WT_DB::exec("LOCK TABLE" . " `##individuals` WRITE," . " `##individuals` AS individuals2 READ," . " `##families` WRITE," . " `##families` AS families2 READ," . " `##sources` WRITE," . " `##sources` AS sources2 READ," . " `##media` WRITE," . " `##media` AS media2 READ," . " `##other` WRITE," . " `##other` AS other2 READ," . " `##name` WRITE," . " `##name` AS name2 READ," . " `##placelinks` WRITE," . " `##placelinks` AS placelinks2 READ," . " `##change` WRITE," . " `##change` AS change2 READ," . " `##dates` WRITE," . " `##dates` AS dates2 READ," . " `##default_resn` WRITE," . " `##default_resn` AS default_resn2 READ," . " `##hit_counter` WRITE," . " `##hit_counter` AS hit_counter2 READ," . " `##link` WRITE," . " `##link` AS link2 READ");
try {
WT_DB::prepare("INSERT INTO `##individuals` (i_id, i_file, i_rin, i_sex, i_gedcom)" . " SELECT i_id, ?, i_rin, i_sex, i_gedcom FROM `##individuals` AS individuals2 WHERE i_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##families` (f_id, f_file, f_husb, f_wife, f_gedcom, f_numchil)" . " SELECT f_id, ?, f_husb, f_wife, f_gedcom, f_numchil FROM `##families` AS families2 WHERE f_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##sources` (s_id, s_file, s_name, s_gedcom)" . " SELECT s_id, ?, s_name, s_gedcom FROM `##sources` AS sources2 WHERE s_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##media` (m_id, m_ext, m_type, m_titl, m_filename, m_file, m_gedcom)" . " SELECT m_id, m_ext, m_type, m_titl, m_filename, ?, m_gedcom FROM `##media` AS media2 WHERE m_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##other` (o_id, o_file, o_type, o_gedcom)" . " SELECT o_id, ?, o_type, o_gedcom FROM `##other` AS other2 WHERE o_file = ? AND o_type NOT IN ('HEAD', 'TRLR')")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##name` (n_file, n_id, n_num, n_type, n_sort, n_full, n_surname, n_surn, n_givn, n_soundex_givn_std, n_soundex_surn_std, n_soundex_givn_dm, n_soundex_surn_dm)" . " SELECT ?, n_id, n_num, n_type, n_sort, n_full, n_surname, n_surn, n_givn, n_soundex_givn_std, n_soundex_surn_std, n_soundex_givn_dm, n_soundex_surn_dm FROM `##name` AS name2 WHERE n_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##placelinks` (pl_p_id, pl_gid, pl_file)" . " SELECT pl_p_id, pl_gid, ? FROM `##placelinks` AS placelinks2 WHERE pl_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##dates` (d_day, d_month, d_mon, d_year, d_julianday1, d_julianday2, d_fact, d_gid, d_file, d_type)" . " SELECT d_day, d_month, d_mon, d_year, d_julianday1, d_julianday2, d_fact, d_gid, ?, d_type FROM `##dates` AS dates2 WHERE d_file = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##default_resn` (gedcom_id, xref, tag_type, resn, comment, updated)" . " SELECT ?, xref, tag_type, resn, comment, updated FROM `##default_resn` AS default_resn2 WHERE gedcom_id = ?")->execute(array($ged2_id, WT_GED_ID));
WT_DB::prepare("INSERT INTO `##link` (l_file, l_from, l_type, l_to)" . " SELECT ?, l_from, l_type, l_to FROM `##link` AS link2 WHERE l_file = ?")->execute(array($ged2_id, WT_GED_ID));
// This table may contain old (deleted) references, which could clash. IGNORE these.
WT_DB::prepare("INSERT IGNORE INTO `##change` (change_time, status, gedcom_id, xref, old_gedcom, new_gedcom, user_id)" . " SELECT change_time, status, ?, xref, old_gedcom, new_gedcom, user_id FROM `##change` AS change2 WHERE gedcom_id = ?")->execute(array($ged2_id, WT_GED_ID));
// This table may contain old (deleted) references, which could clash. IGNORE these.
WT_DB::prepare("INSERT IGNORE INTO `##hit_counter` (gedcom_id, page_name, page_parameter, page_count)" . " SELECT ?, page_name, page_parameter, page_count FROM `##hit_counter` AS hit_counter2 WHERE gedcom_id = ? AND page_name <> 'index.php'")->execute(array($ged2_id, WT_GED_ID));
echo '<p>', WT_I18N::translate('The family trees were merged successfully.'), '</p>';
} catch (Exception $ex) {
WT_DB::exec("ROLLBACK");
echo '<p>', WT_I18N::translate('Oops! An unexpected database error occurred.'), '</p>';
echo '<pre>', $ex, '</pre>';
}
WT_DB::exec("UNLOCK TABLES");
WT_DB::exec("COMMIT");
}
}
echo '<form method="POST" action="', WT_SCRIPT_NAME, '">';
echo '<input type="hidden" name="go" value="1">';
echo '<p>', WT_I18N::translate('Copy all the records from %1$s into %2$s.', WT_Filter::escapeHtml($WT_TREE->tree_title), select_edit_control('ged2_id', WT_Tree::getIdList(), null, $ged2_id)), '</p>';
echo '<input type="submit" value="', WT_I18N::translate('continue'), '">';
echo '</form>';
private function adminPlaceCheck()
{
require_once WT_ROOT . 'includes/functions/functions_edit.php';
$action = WT_Filter::get('action', '', 'go');
$gedcom_id = WT_Filter::get('gedcom_id', null, WT_GED_ID);
$country = WT_Filter::get('country', '.+', 'XYZ');
$state = WT_Filter::get('state', '.+', 'XYZ');
$matching = WT_Filter::getBool('matching');
$controller = new WT_Controller_Page();
$controller->restrictAccess(Auth::isAdmin())->setPageTitle(WT_I18N::translate('Google Maps™'))->pageHeader();
echo '
<table id="gm_config">
<tr>
<th>
<a href="module.php?mod=googlemap&mod_action=admin_config">', WT_I18N::translate('Google Maps™ preferences'), '</a>
</th>
<th>
<a href="module.php?mod=googlemap&mod_action=admin_places">
', WT_I18N::translate('Geographic data'), '
</a>
</th>
<th>
<a class="current" href="module.php?mod=googlemap&mod_action=admin_placecheck">
', WT_I18N::translate('Place check'), '
</a>
</th>
</tr>
</table>';
//Start of User Defined options
echo '
<form method="get" name="placecheck" action="module.php">
<input type="hidden" name="mod" value="', $this->getName(), '">
<input type="hidden" name="mod_action" value="admin_placecheck">
<div class="gm_check">
<label>', WT_I18N::translate('Family tree'), '</label>';
echo select_edit_control('gedcom_id', WT_Tree::getIdList(), null, $gedcom_id, ' onchange="this.form.submit();"');
echo '<label>', WT_I18N::translate('Country'), '</label>
<select name="country" onchange="this.form.submit();">
<option value="XYZ" selected="selected">', WT_I18N::translate('<select>'), '</option>
<option value="XYZ">', WT_I18N::translate('All'), '</option>';
$rows = WT_DB::prepare("SELECT pl_id, pl_place FROM `##placelocation` WHERE pl_level=0 ORDER BY pl_place")->fetchAssoc();
foreach ($rows as $id => $place) {
echo '<option value="', WT_Filter::escapeHtml($place), '"';
if ($place == $country) {
echo ' selected="selected"';
$par_id = $id;
}
echo '>', WT_Filter::escapeHtml($place), '</option>';
}
echo '</select>';
if ($country != 'XYZ') {
echo '<label>', WT_I18N::translate('Subdivision'), '</label>
<select name="state" onchange="this.form.submit();">
<option value="XYZ" selected="selected">', WT_I18N::translate('<select>'), '</option>
<option value="XYZ">', WT_I18N::translate('All'), '</option>';
$places = WT_DB::prepare("SELECT pl_place FROM `##placelocation` WHERE pl_parent_id=? ORDER BY pl_place")->execute(array($par_id))->fetchOneColumn();
foreach ($places as $place) {
echo '<option value="', WT_Filter::escapeHtml($place), '"', $place == $state ? ' selected="selected"' : '', '>', WT_Filter::escapeHtml($place), '</option>';
}
echo '</select>';
}
echo '<label>', WT_I18N::translate('Include fully matched places: '), '</label>';
echo '<input type="checkbox" name="matching" value="1" onchange="this.form.submit();"';
if ($matching) {
echo ' checked="checked"';
}
echo '>';
echo '</div>';
// close div gm_check
echo '<input type="hidden" name="action" value="go">';
echo '</form>';
//close form placecheck
echo '<hr>';
switch ($action) {
case 'go':
//Identify gedcom file
$trees = WT_Tree::getAll();
echo '<div id="gm_check_title">', $trees[$gedcom_id]->tree_title_html, '</div>';
//Select all '2 PLAC ' tags in the file and create array
$place_list = array();
$ged_data = WT_DB::prepare("SELECT i_gedcom FROM `##individuals` WHERE i_gedcom LIKE ? AND i_file=?")->execute(array("%\n2 PLAC %", $gedcom_id))->fetchOneColumn();
foreach ($ged_data as $ged_datum) {
preg_match_all('/\\n2 PLAC (.+)/', $ged_datum, $matches);
foreach ($matches[1] as $match) {
$place_list[$match] = true;
}
}
$ged_data = WT_DB::prepare("SELECT f_gedcom FROM `##families` WHERE f_gedcom LIKE ? AND f_file=?")->execute(array("%\n2 PLAC %", $gedcom_id))->fetchOneColumn();
foreach ($ged_data as $ged_datum) {
preg_match_all('/\\n2 PLAC (.+)/', $ged_datum, $matches);
foreach ($matches[1] as $match) {
$place_list[$match] = true;
}
}
// Unique list of places
$place_list = array_keys($place_list);
// Apply_filter
if ($country == 'XYZ') {
$filter = '.*$';
} else {
$filter = preg_quote($country) . '$';
if ($state != 'XYZ') {
$filter = preg_quote($state) . ', ' . $filter;
}
}
$place_list = preg_grep('/' . $filter . '/', $place_list);
//sort the array, limit to unique values, and count them
usort($place_list, array('WT_I18N', 'strcasecmp'));
$i = count($place_list);
//calculate maximum no. of levels to display
$x = 0;
$max = 0;
while ($x < $i) {
$levels = explode(",", $place_list[$x]);
$parts = count($levels);
if ($parts > $max) {
$max = $parts;
}
$x++;
}
$x = 0;
//scripts for edit, add and refresh
?>
<script>
function edit_place_location(placeid) {
window.open('module.php?mod=googlemap&mod_action=places_edit&action=update&placeid='+placeid, '_blank', gmap_window_specs);
return false;
}
function add_place_location(placeid) {
window.open('module.php?mod=googlemap&mod_action=places_edit&action=add&placeid='+placeid, '_blank', gmap_window_specs);
return false;
}
</script>
<?php
//start to produce the display table
$cols = 0;
$span = $max * 3 + 3;
echo '<div class="gm_check_details">';
echo '<table class="gm_check_details"><tr>';
echo '<th rowspan="3">', WT_I18N::translate('Place'), '</th>';
echo '<th colspan="', $span, '">', WT_I18N::translate('Geographic data'), '</th></tr>';
echo '<tr>';
while ($cols < $max) {
if ($cols == 0) {
echo '<th colspan="3">', WT_I18N::translate('Country'), '</th>';
} else {
echo '<th colspan="3">', WT_I18N::translate('Level'), ' ', $cols + 1, '</th>';
}
$cols++;
}
echo '</tr><tr>';
$cols = 0;
while ($cols < $max) {
echo '<th>', WT_Gedcom_Tag::getLabel('PLAC'), '</th><th>', WT_I18N::translate('Latitude'), '</th><th>', WT_I18N::translate('Longitude'), '</th>';
$cols++;
}
echo '</tr>';
$countrows = 0;
$matched = array();
while ($x < $i) {
$placestr = "";
$levels = explode(",", $place_list[$x]);
$parts = count($levels);
$levels = array_reverse($levels);
$placestr .= "<a href=\"placelist.php?action=show";
foreach ($levels as $pindex => $ppart) {
$ppart = urlencode(trim($ppart));
$placestr .= "&parent[{$pindex}]=" . $ppart . "";
}
$placestr .= "\">" . $place_list[$x] . "</a>";
$gedplace = "<tr><td>" . $placestr . "</td>";
$z = 0;
$id = 0;
$level = 0;
$matched[$x] = 0;
// used to exclude places where the gedcom place is matched at all levels
$mapstr_edit = "<a href=\"#\" onclick=\"edit_place_location('";
$mapstr_add = "<a href=\"#\" onclick=\"add_place_location('";
$mapstr3 = "";
$mapstr4 = "";
$mapstr5 = "')\" title='";
$mapstr6 = "' >";
$mapstr7 = "')\">";
$mapstr8 = "</a>";
$plac = array();
$lati = array();
$long = array();
while ($z < $parts) {
if ($levels[$z] == ' ' || $levels[$z] == '') {
$levels[$z] = "unknown";
}
// GoogleMap module uses "unknown" while GEDCOM uses , ,
$levels[$z] = rtrim(ltrim($levels[$z]));
$placelist = $this->createPossiblePlaceNames($levels[$z], $z + 1);
// add the necessary prefix/postfix values to the place name
foreach ($placelist as $key => $placename) {
$row = WT_DB::prepare("SELECT pl_id, pl_place, pl_long, pl_lati, pl_zoom FROM `##placelocation` WHERE pl_level=? AND pl_parent_id=? AND pl_place LIKE ? ORDER BY pl_place")->execute(array($z, $id, $placename))->fetchOneRow(PDO::FETCH_ASSOC);
if (!empty($row['pl_id'])) {
$row['pl_placerequested'] = $levels[$z];
// keep the actual place name that was requested so we can display that instead of what is in the db
break;
}
}
if ($row['pl_id'] != '') {
$id = $row['pl_id'];
}
if ($row['pl_place'] != '') {
$placestr2 = $mapstr_edit . $id . "&level=" . $level . $mapstr3 . $mapstr5 . WT_I18N::translate('Zoom=') . $row['pl_zoom'] . $mapstr6 . $row['pl_placerequested'] . $mapstr8;
if ($row['pl_place'] == 'unknown') {
$matched[$x]++;
}
} else {
if ($levels[$z] == "unknown") {
$placestr2 = $mapstr_add . $id . "&level=" . $level . $mapstr3 . $mapstr7 . "<strong>" . rtrim(ltrim(WT_I18N::translate('unknown'))) . "</strong>" . $mapstr8;
$matched[$x]++;
} else {
$placestr2 = $mapstr_add . $id . "&place_name=" . urlencode($levels[$z]) . "&level=" . $level . $mapstr3 . $mapstr7 . '<span class="error">' . rtrim(ltrim($levels[$z])) . '</span>' . $mapstr8;
$matched[$x]++;
}
}
$plac[$z] = "<td>" . $placestr2 . "</td>\n";
if ($row['pl_lati'] == '0') {
$lati[$z] = "<td class='error'><strong>" . $row['pl_lati'] . "</strong></td>";
} elseif ($row['pl_lati'] != '') {
$lati[$z] = "<td>" . $row['pl_lati'] . "</td>";
} else {
$lati[$z] = "<td class='error center'><strong>X</strong></td>";
$matched[$x]++;
}
if ($row['pl_long'] == '0') {
$long[$z] = "<td class='error'><strong>" . $row['pl_long'] . "</strong></td>";
} elseif ($row['pl_long'] != '') {
$long[$z] = "<td>" . $row['pl_long'] . "</td>";
} else {
$long[$z] = "<td class='error center'><strong>X</strong></td>";
$matched[$x]++;
}
$level++;
$mapstr3 = $mapstr3 . "&parent[" . $z . "]=" . WT_Filter::escapeJs($row['pl_placerequested']);
$mapstr4 = $mapstr4 . "&parent[" . $z . "]=" . WT_Filter::escapeJs($levels[$z]);
$z++;
}
if ($matching) {
$matched[$x] = 1;
}
if ($matched[$x] != 0) {
echo $gedplace;
$z = 0;
while ($z < $max) {
if ($z < $parts) {
echo $plac[$z];
echo $lati[$z];
echo $long[$z];
} else {
echo '<td> </td><td> </td><td> </td>';
}
$z++;
}
echo '</tr>';
$countrows++;
}
$x++;
}
// echo final row of table
echo '<tr><td colspan="2" class="accepted">', WT_I18N::translate('Total places: %s', WT_I18N::number($countrows)), '</td></tr></table></div>';
break;
default:
// Do not run until user selects a gedcom/place/etc.
// Instead, show some useful help info.
echo '<div class="gm_check_top accepted">', WT_I18N::translate('This will list all the places from the selected GEDCOM file. By default this will NOT INCLUDE places that are fully matched between the GEDCOM file and the GoogleMap tables'), '</div>';
break;
}
}
private function edit()
{
require_once WT_ROOT . 'includes/functions/functions_edit.php';
if (WT_Filter::postBool('save') && WT_Filter::checkCsrf()) {
$block_id = WT_Filter::postInteger('block_id');
if ($block_id) {
WT_DB::prepare("UPDATE `##block` SET gedcom_id=NULLIF(?, '0'), block_order=? WHERE block_id=?")->execute(array(WT_Filter::postInteger('gedcom_id'), WT_Filter::postInteger('block_order'), $block_id));
} else {
WT_DB::prepare("INSERT INTO `##block` (gedcom_id, module_name, block_order) VALUES (NULLIF(?, '0'), ?, ?)")->execute(array(WT_Filter::postInteger('gedcom_id'), $this->getName(), WT_Filter::postInteger('block_order')));
$block_id = WT_DB::getInstance()->lastInsertId();
}
set_block_setting($block_id, 'header', WT_Filter::post('header'));
set_block_setting($block_id, 'faqbody', WT_Filter::post('faqbody'));
$languages = array();
foreach (WT_I18N::installed_languages() as $code => $name) {
if (WT_Filter::postBool('lang_' . $code)) {
$languages[] = $code;
}
}
set_block_setting($block_id, 'languages', implode(',', $languages));
$this->config();
} else {
$block_id = WT_Filter::getInteger('block_id');
$controller = new WT_Controller_Page();
if ($block_id) {
$controller->setPageTitle(WT_I18N::translate('Edit FAQ item'));
$header = get_block_setting($block_id, 'header');
$faqbody = get_block_setting($block_id, 'faqbody');
$block_order = WT_DB::prepare("SELECT block_order FROM `##block` WHERE block_id=?")->execute(array($block_id))->fetchOne();
$gedcom_id = WT_DB::prepare("SELECT gedcom_id FROM `##block` WHERE block_id=?")->execute(array($block_id))->fetchOne();
} else {
$controller->setPageTitle(WT_I18N::translate('Add an FAQ item'));
$header = '';
$faqbody = '';
$block_order = WT_DB::prepare("SELECT IFNULL(MAX(block_order)+1, 0) FROM `##block` WHERE module_name=?")->execute(array($this->getName()))->fetchOne();
$gedcom_id = WT_GED_ID;
}
$controller->pageHeader();
if (array_key_exists('ckeditor', WT_Module::getActiveModules())) {
ckeditor_WT_Module::enableEditor($controller);
}
// "Help for this page" link
echo '<div id="page_help">', help_link('add_faq_item', $this->getName()), '</div>';
echo '<form name="faq" method="post" action="module.php?mod=', $this->getName(), '&mod_action=admin_edit">';
echo WT_Filter::getCsrf();
echo '<input type="hidden" name="save" value="1">';
echo '<input type="hidden" name="block_id" value="', $block_id, '">';
echo '<table id="faq_module">';
echo '<tr><th>';
echo WT_I18N::translate('Question');
echo '</th></tr><tr><td><input type="text" name="header" size="90" tabindex="1" value="' . WT_Filter::escapeHtml($header) . '"></td></tr>';
echo '<tr><th>';
echo WT_I18N::translate('Answer');
echo '</th></tr><tr><td>';
echo '<textarea name="faqbody" class="html-edit" rows="10" cols="90" tabindex="2">', WT_Filter::escapeHtml($faqbody), '</textarea>';
echo '</td></tr>';
echo '</table><table id="faq_module2">';
echo '<tr>';
echo '<th>', WT_I18N::translate('Show this block for which languages?'), '</th>';
echo '<th>', WT_I18N::translate('FAQ position'), help_link('add_faq_order', $this->getName()), '</th>';
echo '<th>', WT_I18N::translate('FAQ visibility'), help_link('add_faq_visibility', $this->getName()), '</th>';
echo '</tr><tr>';
echo '<td>';
$languages = get_block_setting($block_id, 'languages');
echo edit_language_checkboxes('lang_', $languages);
echo '</td><td>';
echo '<input type="text" name="block_order" size="3" tabindex="3" value="', $block_order, '"></td>';
echo '</td><td>';
echo select_edit_control('gedcom_id', WT_Tree::getIdList(), WT_I18N::translate('All'), $gedcom_id, 'tabindex="4"');
echo '</td></tr>';
echo '</table>';
echo '<p><input type="submit" value="', WT_I18N::translate('save'), '" tabindex="5">';
echo '</form>';
exit;
}
}
