<?php
/**
* i-net Download Plugin
*
* @license GPL 2 (http://www.gnu.org/licenses/gpl.html)
* @author i-net software <tools@inetsoftware.de>
* @author Gerry Weissbach <gweissbach@inetsoftware.de>
*/
/*
* delete from tblTranslation Where Value like '%\?\?\?%' and Lang not in ('en', 'de', 'es');
* DROP TABLE IF EXISTS tblTranslation_old;
* create table tblTranslation2 like tblTranslation;
* INSERT INTO tblTranslation2 (KeyID, Lang, Date, Value, User) SELECT KeyID, Lang, MAX(Date), Value, User FROM tblTranslation GROUP BY Value, Lang, KeyID, User;
* RENAME TABLE tblTranslation TO tblTranslation_old;
* RENAME TABLE tblTranslation2 TO tblTranslation;
*/
// must be run within Dokuwiki
if (!defined('DOKU_INC')) die();
if (!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN', DOKU_INC.'lib/plugins/');
class helper_plugin_translator extends DokuWiki_Plugin { // DokuWiki_Helper_Plugin
var $database = null;
var $checkedOK = null;
var $pageWordLenIdx = null;
var $revertableUpload = array();
var $dataBaseStruct = array (
"tblCategory" => array( 'statement' => "CREATE TABLE `tblCategory` (
`CategoryID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Name` VARCHAR( 255 ) NOT NULL ,
`FileName` VARCHAR( 255 ) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
'fields' => array('CategoryID', 'Name', 'FileName')
),
"tblMasterKey" => array( 'statement' => "CREATE TABLE `tblMasterKey` (
`KeyID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ResourceKey` VARCHAR( 255 ) NOT NULL ,
`Value` TEXT NOT NULL ,
`MD5KeyVal` VARCHAR( 255 ) NOT NULL ,
UNIQUE ( `Md5KeyVal` )
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
'fields' => array('KeyID', 'ResourceKey', 'Value', 'MD5KeyVal')
),
"tblMaster" => array( 'statement' => "CREATE TABLE `tblMaster` (
`KeyID` INT( 11 ) NOT NULL,
`CategoryID` INT( 11 ) NOT NULL,
`Version` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `KeyID`, `CategoryID`, `Version` )
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
'fields' => array('KeyID', 'CategoryID', 'Version')
),
"tblTranslation" => array( 'statement' => "CREATE TABLE `tblTranslation` (
`KeyID` VARCHAR( 255 ) NOT NULL ,
`Lang` VARCHAR( 10 ) NOT NULL ,
`Date` DATETIME NOT NULL ,
`Value` TEXT NOT NULL ,
`User` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `KeyID` , `Lang` , `Date` )
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
'fields' => array('KeyID', 'Lang', 'Value', 'User', 'Date')
),
"tblUserRights" => array( 'statement' => "CREATE TABLE `tblUserRights` (
`User` VARCHAR( 255 ) NOT NULL ,
`Lang` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `User` )
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
'fields' => array('User', 'Lang')
),
);
function getInfo(){
return array_merge(confToHash(dirname(__FILE__).'/info.txt'), array(
'name' => 'Translator - Helper',
));
}
function getMethods() {
$result = array();
return $result;
}
function checkDatabase() {
if ( $this->init_database() === false ) {
print "Cannot init.";
return false;
}
if ( empty($this->database) ) {
print $this->lang['nodatabase'];
return false;
}
if ( !is_null($this->checkedOK) ) { return $this->checkedOK; }
$this->checkedOK = $this->currentVersionChecked();
if ( $this->checkedOK ) { return $this->checkedOK; }
foreach ($this->dataBaseStruct as $tableName => $sqlStatement ) {
$this->database->query("SHOW TABLES LIKE \"$tableName\";");
//$this->database->execute($tableName);
$allGood = false;
if ( $this->database->num_rows() != 1 ) {
msg("Creating table '$tableName'", 0);
$this->database->prepare($sqlStatement['statement']);
$this->database->execute();
$allGood = true;
} else {
// Check Log
$table = $this->database->databaseConnection->escape_string($tableName);
$this->database->prepare("SHOW COLUMNS FROM `$table`");
$this->database->execute();
if ( $this->database->num_rows() >= count($sqlStatement['fields']) ) {
$allGood = true;
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
if ( !in_array($data['Field'], $sqlStatement['fields']) ) {
msg("Field missing in '$tableName': '{$data['Field']}'", -1);
$allGood = false;
break;
}
}
}
}
}
$this->checkedOK = $allGood;
if ( $allGood ) {
$this->setCurrentVersionChecked();
}
return $allGood;
}
function init_database() {
if ( empty($this->database) ) {
if ( !$this->database =& plugin_load('helper', 'databaseconnector', true) ) { return false;}
$this->dbType = $dbType;
$this->database->setType($this->getConf('DBType'));
if ( $this->database->connect($this->getConf('DBName'), $this->getConf('DBUserName'), $this->getConf('DBUserPassword'), $this->getConf('DBHostName')) ) {
return true;
}
return false;
}
}
function close_database() {
if ( !empty($this->database) ) {
$this->database->close();
$this->database = null;
}
}
function _hashKey($key, $value) {
return md5($key . $value);
}
/*
* Create Master Key / Value Entries
*/
function _createMasterTableEntry($translation, $categoryID, $version, &$finalStatus) {
foreach ( $translation as $key => $value) {
$KeyID = null;
$HASHKEY = $this->_hashKey($key, $value);
// Check if Key => Value is in tblMasterKeys
$this->database->prepare("SELECT KeyID FROM tblMasterKey WHERE MD5KeyVal=?;");
$this->database->execute($HASHKEY);
if ( $this->database->num_rows() == 0 ) {
// Key is not in DB ... is there a Key with another value for the current Category? Order DESC by Version
$this->database->prepare("SELECT tblMasterKey.KeyID FROM tblMaster LEFT JOIN tblMasterKey ON tblMaster.KeyID=tblMasterKey.KeyID WHERE ResourceKey=? AND CategoryID=? ORDER BY Version DESC;");
$this->database->execute($key, $categoryID);
if ( $this->database->num_rows() > 0 ) {
$data = array(); $this->database->bind_assoc($data); $this->database->fetch();
$KeyID = $data['KeyID'];
// Update Database
$this->database->prepare("UPDATE tblMasterKey SET Value=?, Md5KeyVal=? WHERE KeyID=?;");
$this->database->execute($value, $HASHKEY, $KeyID);
} else {
// The Key is not in the Database, so add it
$this->database->prepare("INSERT INTO tblMasterKey (ResourceKey, Value, Md5KeyVal) VALUES(?, ?, ?);");
$this->database->execute($key, $value, $HASHKEY);
$KeyID = $this->database->insert_id();
}
} else {
// Fetch inserted Key ID
$data = array(); $this->database->bind_assoc($data); $this->database->fetch();
$KeyID = $data['KeyID'];
// If exists with same Version Return
$this->database->prepare("SELECT KeyID FROM tblMaster WHERE KeyID=? AND CategoryID=? AND Version=?;");
$this->database->execute($KeyID, $categoryID, $version);
if ( $this->database->num_rows() > 0 ) {
$finalStatus['KeysMatchingExisting']++;
continue;
}
}
// Add KeyID to tblMaster
$this->database->prepare("INSERT INTO tblMaster (KeyID, CategoryID, Version) VALUES(?, ?, ?);");
$this->database->execute($KeyID, $categoryID, $version);
continue;
}
}
/*
* get Category ID via Name
*/
function _getCategoryFromName($categoryName) {
$CATEGORIES = $this->_getCategories($categoryName);
if ( empty($CATEGORIES[$categoryName]) ) {
msg($this->_messageReplacer('CategoryNotFound', $categoryName), -1);
return false;
}
return array( $CATEGORIES[$categoryName]['CategoryID'], str_replace('[LANG]', '(.*?)', $CATEGORIES[$categoryName]['FileName']));
}
/*
* get Categories
*/
function _getCategories($categoryName=null, $isID=false) {
$additional = ''; $execute = array(); $return = array();
if ( !empty($categoryName) ) {
if ( $isID ) {
$additional .= "WHERE CategoryID=?";
} else {
$additional .= "WHERE Name=?";
}
$execute[] = $categoryName;
}
$this->database->prepare("SELECT Name, CategoryID, FileName FROM tblCategory $additional;");
$this->database->execute($execute);
if ( $this->database->num_rows() == 0 ) {
return $return;
}
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$return[$data['Name']] = array( 'CategoryID' => $data['CategoryID'],
'FileName' => $data['FileName'],
);
}
return $return;
}
/*
* Get Available Languages for a Category
*/
function _getAvailableLanguagesVersions($categoryID) {
if ( !$categoryID ) {
return;
}
$SQL = "Select DISTINCT Version, Lang FROM (SELECT Version, keyID FROM tblMaster WHERE CategoryID=? GROUP BY Version) as t1 INNER JOIN tblTranslation ON t1.KeyID = tblTranslation.KeyID";
$SQL = "SELECT Version, Lang FROM tblTranslation INNER JOIN tblMaster ON tblMaster.KeyID=tblTranslation.KeyID WHERE CategoryID=? GROUP BY Version, Lang;";
// Approximativ - vermutlich zu viel, aber besser als gar nix!
$SQL = "SELECT Lang, Version FROM (SELECT DISTINCT Lang FROM tblTranslation) as t1 CROSS JOIN (SELECT DISTINCT Version FROM tblMaster WHERE CategoryID=?) as t2;";
$this->database->prepare($SQL);
$this->database->execute($categoryID);
$languages = array(); $data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$languages[$data['Version']][] = $data['Lang'];
}
foreach ( array_keys($languages) as $key ) {
array_unshift($languages[$key], $this->getConf('default_language'));
}
return $languages;
}
/*
* Get Available Languages for a Category
*/
function _getAvailableLanguages($categoryID, $version=null) {
$EXECUTE = array($categoryID);
if ( !empty($version) ) {
$ADDITIONAL .= " AND Version=?";
$EXECUTE[] = $version;
}
$languages = array($this->getConf('default_language'));
$this->database->prepare("SELECT Lang FROM tblTranslation INNER JOIN tblMaster ON tblMaster.KeyID=tblTranslation.KeyID WHERE CategoryID=? $ADDITIONAL GROUP BY Lang;");
$this->database->execute($EXECUTE);
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$languages[] = $data['Lang'];
}
return $languages;
}
/*
* Get Available Versions for a Category
*/
function _getAvailableVersions($categoryID=null) {
$EXECUTE = array();
$ADDITIONAL = "";
if ( !empty( $categoryID) ) {
$EXECUTE[] = $categoryID;
$ADDITIONAL .= " WHERE CategoryID=? ";
}
$versions = array();
$this->database->prepare("SELECT Version FROM tblMaster $ADDITIONAL GROUP BY Version;");
$this->database->execute($EXECUTE);
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$versions[] = trim($data['Version']);
}
$versions = array_unique($versions);
usort($versions, array($this, "_sortVersion"));
return $versions;
}
function _sortVersion($a, $b) {
if ($a === $b ) {
return 0;
} else if ( is_numeric($a) && is_numeric($b) ) {
return $a < $b ? -1 : 1;
} else if ( !is_numeric($a) && !is_numeric($b) ) {
return strcmp($a, $b);
} else if ( !is_numeric($a)) {
return 1;
} else {
return -1;
}
}
/*
* Get Key from Master via Name
*/
function _getMasterKeyIDFromName($KeyName, $CategoryID, $isID=false, $getID=true) {
$this->setUTF8Collation();
$WHATTO = 'tblMasterKey.ResourceKey';
if ( $isID ) {
$WHATTO = 'tblMasterKey.KeyID';
}
// Get latest MasterKeyID from Category
$this->database->prepare("SELECT DISTINCT tblMasterKey.KeyID, Value FROM tblMasterKey INNER JOIN tblMaster ON tblMasterKey.KeyID=tblMaster.KeyID WHERE tblMaster.CategoryID=? AND $WHATTO=? ORDER BY Version DESC;");
$this->database->execute($CategoryID, $KeyName);
if ( $this->database->num_rows() > 0 ) {
$data = array(); $this->database->bind_assoc($data); $this->database->fetch();
return $getID ? $data['KeyID'] : $data['Value'];
} else {
return false;
}
}
/**
* Check for all Keys if they do have a master - otherwise, kick them
* @param $translation
* @param $finalStatus
* @param $CategoryID
*/
function _checkForMasterKeyIDs(&$translation, &$finalStatus, $CategoryID) {
$this->setUTF8Collation();
$keysIN = implode("\",\"", array_keys($translation));
// $keysIN = str_replace("(", "\(", $keysIN);
// $keysIN = str_replace(")", "\)", $keysIN);
// Get latest MasterKeyID from Category
$SQL = "SELECT DISTINCT tblMasterKey.KeyID, tblMasterKey.ResourceKey FROM tblMasterKey INNER JOIN tblMaster ON tblMasterKey.KeyID=tblMaster.KeyID WHERE tblMaster.CategoryID=? AND tblMasterKey.ResourceKey in ( \"$keysIN\" ) ORDER BY Version DESC;";
$this->database->prepare($SQL);
$this->database->execute($CategoryID);
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$translation[$data['ResourceKey']] = array( 'ID' => $data['KeyID'], 'Value' => $translation[$data['ResourceKey']] );
$finalStatus['CountOfKeys']++;
}
}
/*
* Check if a key exists - additionally check for Lang and Value
*/
function _keyExists($KeyID, $Lang=null, $Value=null, $Date=null) {
$additional = "";
$execute = array( $KeyID );
if ( !empty($Lang) ) {
$additional .= " AND Lang=?";
$execute[] = $Lang;
}
if ( !empty($Date) ) {
$additional .= " AND Date=?";
$execute[] = $Date;
}
$SQL = "SELECT KeyID, Value FROM tblTranslation WHERE KeyID=? $additional";
// Checks for the latest Entry
if ( !empty($Value) ) {
$SQL = "SELECT tblTrans.KeyID FROM ($SQL ORDER BY Date DESC LIMIT 1) as tblTrans WHERE BINARY tblTrans.Value=?";
$execute[] = $Value;
}
$this->database->prepare("$SQL;");
$this->database->execute($execute);
if ( $this->database->num_rows() == 0 ) {
return false;
}
return true;
}
/*
* Insert a new Translation Set of Translations
*/
function _insertTranslation($KeyID, $Value, $Lang, $Date, $User=null) {
global $INFO;
$revertable = false;
if ( empty($Value) ) return $revertable;
// Bottleneck 2
if ( $this->_keyExists($KeyID, $Lang) ) {
if ( $this->_keyExists($KeyID, $Lang, $Value) || $this->_keyExists($KeyID, $Lang, null, $Date) ) {
return -1;
}
$revertable = true;
}
// Insert Translation
if ( empty($User) ) $User = $_SERVER['REMOTE_USER'];
$this->database->prepare("INSERT INTO tblTranslation (KeyID, Lang, Value, User, Date) VALUES(?, ?, ?, ?, ?);");
$this->database->execute($KeyID, $Lang, $Value, $User, $Date);
return $revertable;
}
function _insertTranslationArray($translation, &$finalStatus, $Lang, $Date) {
$CHECK = '';
$translationINV = array();
foreach( $translation as $key => $value ) {
if ( !is_array($value) ) {
$finalStatus['KeysNotInMaster'][] = $key;
unset($translation[$key]);
continue;
}
$CHECK .= $value['ID'] . ",";
$translationINV[$value['ID']] = array(
'key' => $key,
'value' => $value['Value']
);
}
$CHECK = substr($CHECK, 0, -1);
$SQL = "SELECT DISTINCT * FROM (SELECT KeyID, Value, Date FROM tblTranslation WHERE KeyID in ( $CHECK ) AND Lang=? ORDER BY Date) as t1 GROUP BY KeyID;";
$this->database->prepare($SQL);
$this->database->execute($Lang);
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
if ( $translationINV[$data['KeyID']]['value'] == $data['Value'] || $data['Date'] == $Date ) {
$finalStatus['KeysMatchingExisting'][] = $translationINV[$data['KeyID']]['key'];
unset( $translation[$translationINV[$data['KeyID']]['key']] );
unset( $translationINV[$data['KeyID']] );
continue;
}
$finalStatus['CountOfToBeReverted']++;
}
unset($SQL);
if ( empty($translation) ) {
return;
}
$INSERT = '';
$INSERTDATA = array();
foreach( $translation as $key => $value ) {
$INSERTDATA[] = $value['ID'];
$INSERTDATA[] = $Lang;
$INSERTDATA[] = $value['value'];
$INSERTDATA[] = $_SERVER['REMOTE_USER'];
$INSERTDATA[] = $Date;
$INSERT .= '(?,?,?,?,?),';
}
$INSERT = substr($INSERT, 0, -1);
$this->database->prepare("INSERT INTO tblTranslation (KeyID, Lang, Value, User, Date) VALUES $INSERT;");
$this->database->execute($INSERTDATA);
}
function _getTranslation($CategoryID, $Version, $Language) {
$this->setUTF8Collation();
if ( $Language == $this->getConf('default_language') ) {
$this->database->prepare("SELECT ResourceKey, Value FROM tblMasterKey INNER JOIN tblMaster ON tblMasterKey.KeyID=tblMaster.KeyID WHERE tblMaster.Version=? AND tblMaster.CategoryID=? ORDER BY tblMasterKey.ResourceKey;");
$this->database->execute($Version, $CategoryID);
} else {
$this->database->prepare("SELECT tblMasterKey.ResourceKey, tblTrans.Value FROM (tblMasterKey INNER JOIN tblMaster ON tblMasterKey.KeyID=tblMaster.KeyID AND tblMaster.Version=? AND tblMaster.CategoryID=?) INNER JOIN (SELECT * FROM (SELECT * FROM tblTranslation WHERE tblTranslation.Lang=? ORDER BY tblTranslation.Date DESC) as tblTemp1 GROUP BY KeyID) as tblTrans ON tblMasterKey.KeyID = tblTrans.KeyID ORDER BY ResourceKey, Value;");
$this->database->execute($Version, $CategoryID, $Language);
}
$return = array();
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$return[$data['ResourceKey']] = $data['Value'];
}
return $return;
}
/*
* Revert Entries by dateTime, lang and user
*/
function _revertEntries($dateTime, $lang, $user=null) {
global $INFO;
if ( empty($user) ) $user = $_SERVER['REMOTE_USER'];
$this->database->prepare("DELETE FROM tblTranslation WHERE Date=? AND Lang=? AND User=?;");
$this->database->execute($dateTime, $lang, $user);
return $this->database->num_rows();
}
/* ******************************************************************************************************************************
* Export Manager
***************************************************************************************************************************** */
function _exportManager($caller) {
$form = $caller->_startFieldSet($this->getLang('ExportManager'), 'translator_export');
list($categories, $changeCategory, $changeVersion) = $this->_getChangerJS();
$versions = array();
$languages = array();
if ( is_array($categories[$_REQUEST['Category']]['Versions']) ) {
$versions = array_keys($categories[$_REQUEST['Category']]['Versions']);
}
if ( is_array($categories[$_REQUEST['Category']]['Versions'][$_REQUEST['Version']]) ) {
$languages = array_values($categories[$_REQUEST['Category']]['Versions'][$_REQUEST['Version']]);
}
$form->addElement(form_makeListboxField('Category', array_keys($categories), $_REQUEST['Category'], $this->getLang('Category') . ':', null, null, array('onchange' => $changeCategory)));
$form->addElement(form_makeTag('br'));
$form->addElement(form_makeListboxField('Version', $versions, $_REQUEST['Version'], $this->getLang('Version') . ':', null, null, array('onchange' => $changeVersion)));
$form->addElement(form_makeTag('br'));
$form->addElement(form_makeListboxField('Language', $languages, $_REQUEST['Language'], $this->getLang('Language') . ':'));
$form->addElement(form_makeTag('br'));
$form->addElement(form_makeField('submit', 'export_language', $this->getLang('Export'), ''));
$caller->_finishFieldset($form);
}
/**
* Set headers and send the file to the client
*/
function _sendLanguageFile($data, $fileName){
global $conf;
// send headers
header("Content-Type: text");
// nocache
header('Cache-Control: must-revalidate, no-transform, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Disposition: attachment; filename="'.$fileName.'";');
// send file contents
if ( !empty($data) ) {
foreach( $data as $line ) {
print "$line\r\n"; flush();
}
}
}
function capture_form_output($form) {
if ( !$form ) { return ''; }
ob_start();
$form->printForm();
$output = ob_get_contents();
ob_end_clean();
return trim($output);
}
function _getTranslationMatrix($category, $Version, $Language, $additionalLanguage=null, $start="0", $amount="50", $display=null, $filter=null) {
$this->setUTF8Collation();
if ( empty($display) ) {
$display = $this->getLang('notTranslated');
}
$matrix = array();
list($categoryID) = $this->_getCategoryFromName($category);
if ( $additionalLanguage == $this->getConf('default_language') ) $additionalLanguage = null;
$ADDITIONAL = "";
$ADDITIONALKEYS = "";
$EXECUTE = array($categoryID, $Version, $Language, $Language);
// add SQL for Additional Language
if ( !empty($additionalLanguage) ) {
$ADDITIONAL .= "LEFT JOIN ( tblTranslation as AdditionalTranslation INNER JOIN
(SELECT KeyID, MAX(Date) AS Date FROM tblTranslation WHERE Lang=? GROUP BY KeyID) as tblOldestAddTrans ON
tblOldestAddTrans.KeyID=AdditionalTranslation.KeyID AND tblOldestAddTrans.Date=AdditionalTranslation.Date AND AdditionalTranslation.Lang=?) ON
tblMaster.KeyID = AdditionalTranslation.KeyID ";
$ADDITIONALKEYS .= ", AdditionalTranslation.Value as AdditionalValue";
array_push($EXECUTE, $additionalLanguage);
array_push($EXECUTE, $additionalLanguage);
}
// Modify Display settings
switch($display) {
case $this->getLang('notTranslated') : $ADDITIONAL .= "WHERE tblTranslation.Value IS NULL";
break;
case $this->getLang('translated') : $ADDITIONAL .= "WHERE tblTranslation.Value IS NOT NULL";
break;
}
// Add Filter
if ( !empty($filter) ) {
if ( !strstr($ADDITIONAL, 'WHERE')) {
$ADDITIONAL .= "WHERE";
} else {
$ADDITIONAL .= " AND";
}
if( substr($filter, 0, 1) != "%" ) $filter = "%" . $filter;
if( substr($filter, -1) != "%" ) $filter .= "%";
$ADDITIONAL .= ' (ResourceKey LIKE ? OR tblMasterKey.Value LIKE ? OR tblTranslation.Value LIKE ?';
if ( !empty($additionalLanguage) ) {
$ADDITIONAL .= ' OR AdditionalTranslation.Value LIKE ? ';
array_push($EXECUTE, $filter);
}
$ADDITIONAL .= ')';
array_push($EXECUTE, $filter);
array_push($EXECUTE, $filter);
array_push($EXECUTE, $filter);
}
if ( empty($amount) ) $amount = 50;
// Limits
// Build SQL
$SQL = "SELECT tblMasterKey.KeyID, ResourceKey, tblMasterKey.Value as MasterValue,
tblTranslation.Value as TranslationValue, tblTranslation.User $ADDITIONALKEYS
FROM (
( tblMasterKey INNER JOIN tblMaster ON
tblMaster.KeyID = tblMasterKey.KeyID AND CategoryID = ? AND Version = ? )
LEFT JOIN (
tblTranslation INNER JOIN
(SELECT KeyID, MAX(Date) AS Date FROM tblTranslation WHERE Lang=? GROUP BY KeyID) as tblOldestTrans ON
tblOldestTrans.KeyID=tblTranslation.KeyID AND tblOldestTrans.Date=tblTranslation.Date
) ON tblMaster.KeyID = tblTranslation.KeyID AND Lang=?
) $ADDITIONAL ORDER BY tblMaster.KeyID LIMIT ? OFFSET ?;";
$this->database->prepare("$SQL");
$this->database->execute(array_merge($EXECUTE, array('1000000000000', '0')));
$max = $this->database->num_rows();
array_push($EXECUTE, intval($amount));
array_push($EXECUTE, intval($start));
// Fetch Data
$this->database->execute($EXECUTE);
if ( $this->database->num_rows() == 0 ) {
return false;
}
$data = array(); $this->database->bind_assoc($data);
while ( $this->database->fetch() ) {
$matrix[$data['KeyID']] = array (
'KeyID' => $data['KeyID'],
'ResourceKey' => $data['ResourceKey'],
'MasterValue' => str_replace('\n', "\r\n", $this->_stringDecode($data['MasterValue'])),
'TranslationValue' => str_replace('\n', "\r\n", $this->_stringDecode($data['TranslationValue'])),
'AdditionalValue' => str_replace('\n', "\r\n", $this->_stringDecode($data['AdditionalValue'])),
'User' => $data['User'],
);
}
return array($matrix, $max);
}
/*
* get the users languages from the database and return false if none are there
*/
function _getLanguages() {
global $INFO;
// print_r($INFO);
// print $_SERVER['REMOTE_USER'];
if ( empty($INFO['userinfo']) ) {
return false;
}
$this->database->prepare("SELECT Lang FROM tblUserRights WHERE User=?");
$this->database->execute($_SERVER['REMOTE_USER']);
if ( $this->database->num_rows() == 0) {
return false;
}
$data = array(); $this->database->bind_assoc($data); $this->database->fetch();
return trim($data['Lang']);
}
function _handleRequest($fn) {
if (is_array($fn)) {
$cmd = key($fn);
$param = is_array($fn[$cmd]) ? key($fn[$cmd]) : null;
} else {
$cmd = $fn;
$param = null;
}
switch($cmd){
case "revertuploaded" : $this->_revertUploadedEntries($_REQUEST['revert']); break;
case "translate" : $this->_updateTranslation($_REQUEST['Language'], $_REQUEST['translation']); break;
case "setpage" : $_REQUEST['start'] = $param; $_REQUEST['amount'] = $_REQUEST['newAmount']; $_REQUEST['AdditionalLanguage'] = $_REQUEST['newAdditionalLanguage']; break;
case "setup" : $_REQUEST['Language'] = $param; $_REQUEST['Display'] = $param ? key($fn[$cmd][$param]) : null; break;
}
}
function _stringEncode($var) {
// STRINGS ARE EXPECTED TO BE IN ASCII OR UTF-8 FORMAT
$ascii = '';
//$var = str_replace("\n", '\n', $var);
$strlen_var = strlen($var);
/*
* Iterate over every character in the string,
* escaping with a slash or encoding to UTF-8 where necessary
*/
for ($c = 0; $c < $strlen_var; ++$c) {
$ord_var_c = ord($var{$c});
switch ($ord_var_c) {
case 0x08: $ascii .= '\b'; break;
case 0x09: $ascii .= '\t'; break;
case 0x0A: $ascii .= '\n'; break;
case 0x0C: $ascii .= '\f'; break;
case 0x0D: $ascii .= '\r'; break;
case 0x3D: $ascii .= '\='; break;
case 0x3A: $ascii .= '\:'; break;
case (($ord_var_c >= 0x20) && ($ord_var_c <= 0x7F)):
// characters U-00000000 - U-0000007F (same as ASCII)
$ascii .= $var{$c};
break;
case (($ord_var_c & 0xE0) == 0xC0):
// characters U-00000080 - U-000007FF, mask 110XXXXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c, ord($var{$c+1}));
$c+=1;
//$utf16 = mb_convert_encoding($char, 'UTF-16', 'UTF-8');
$utf16 = utf8_to_utf16be($char);
$ascii .= sprintf('\u%04s', strtoupper(bin2hex($utf16)));
break;
case (($ord_var_c & 0xF0) == 0xE0):
// characters U-00000800 - U-0000FFFF, mask 1110XXXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c,
ord($var{$c+1}),
ord($var{$c+2}));
$c+=2;
//$utf16 = mb_convert_encoding($char, 'UTF-16', 'UTF-8');
$utf16 = utf8_to_utf16be($char);
$ascii .= sprintf('\u%04s', strtoupper(bin2hex($utf16)));
break;
case (($ord_var_c & 0xF8) == 0xF0):
// characters U-00010000 - U-001FFFFF, mask 11110XXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c,
ord($var{$c+1}),
ord($var{$c+2}),
ord($var{$c+3}));
$c+=3;
//$utf16 = mb_convert_encoding($char, 'UTF-16', 'UTF-8');
$utf16 = utf8_to_utf16be($char);
$ascii .= sprintf('\u%04s', strtoupper(bin2hex($utf16)));
break;
case (($ord_var_c & 0xFC) == 0xF8):
// characters U-00200000 - U-03FFFFFF, mask 111110XX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c,
ord($var{$c+1}),
ord($var{$c+2}),
ord($var{$c+3}),
ord($var{$c+4}));
$c+=4;
//$utf16 = mb_convert_encoding($char, 'UTF-16', 'UTF-8');
$utf16 = utf8_to_utf16be($char);
$ascii .= sprintf('\u%04s', strtoupper(bin2hex($utf16)));
break;
case (($ord_var_c & 0xFE) == 0xFC):
// characters U-04000000 - U-7FFFFFFF, mask 1111110X
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c,
ord($var{$c+1}),
ord($var{$c+2}),
ord($var{$c+3}),
ord($var{$c+4}),
ord($var{$c+5}));
$c+=5;
//$utf16 = mb_convert_encoding($char, 'UTF-16', 'UTF-8');
$utf16 = utf8_to_utf16be($char);
$ascii .= sprintf('\u%04s', strtoupper(bin2hex($utf16)));
break;
}
}
return $ascii;
}
function _stringDecode($chrs) {
// STRINGS RETURNED IN UTF-8 FORMAT
$utf8 = '';
$chrs = addcslashes(trim($chrs), "\n\t\r"); // Ensure to have the Line Breaks intact
$strlen_chrs = strlen($chrs);
for ($c = 0; $c < $strlen_chrs; ++$c) {
$substr_chrs_c_2 = substr($chrs, $c, 2);
$ord_chrs_c = ord($chrs{$c});
switch ($substr_chrs_c_2) {
case '\b': $utf8 .= chr(0x08); $c+=1; break;
case '\t': $utf8 .= chr(0x09); $c+=1; break;
case '\n': $utf8 .= chr(0x0A); $c+=1; break;
case '\f': $utf8 .= chr(0x0C); $c+=1; break;
case '\r': $utf8 .= chr(0x0D); $c+=1; break;
case '\:': $utf8 .= chr(0x3A); $c+=1; break;
case '\=': $utf8 .= chr(0x3D); $c+=1; break;
default:
if (preg_match('/\\\u[0-9A-F]{4}/i', substr($chrs, $c, 6))) {
// single, escaped unicode character
$utf16 = chr(hexdec(substr($chrs, ($c+2), 2))) . chr(hexdec(substr($chrs, ($c+4), 2)));
//$utf8 .= mb_convert_encoding($utf16, 'UTF-8', 'UTF-16'); // This is correct for rusians
$utf8 .= utf16be_to_utf8($utf16); // this as Well;
//$utf8 .= utf8_decode(utf16be_to_utf8($utf16));
$c+=5;
} elseif(($ord_chrs_c >= 0x20) && ($ord_chrs_c <= 0x7F)) {
$utf8 .= $chrs{$c};
} elseif(($ord_chrs_c & 0xE0) == 0xC0) {
// characters U-00000080 - U-000007FF, mask 110XXXXX
//see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$utf8 .= substr($chrs, $c, 2); $c += 1;
} elseif(($ord_chrs_c & 0xF0) == 0xE0) {
// characters U-00000800 - U-0000FFFF, mask 1110XXXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$utf8 .= substr($chrs, $c, 3); $c += 2;
} elseif(($ord_chrs_c & 0xF8) == 0xF0) {
// characters U-00010000 - U-001FFFFF, mask 11110XXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$utf8 .= substr($chrs, $c, 4); $c += 3;
} elseif(($ord_chrs_c & 0xFC) == 0xF8) {
// characters U-00200000 - U-03FFFFFF, mask 111110XX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$utf8 .= substr($chrs, $c, 5); $c += 4;
} elseif(($ord_chrs_c & 0xFE) == 0xFC) {
// characters U-04000000 - U-7FFFFFFF, mask 1111110X
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$utf8 .= substr($chrs, $c, 6); $c += 5;
} else {
// Falls doch noch andere UTF8 Zeichen enthalten sind, mŸssen die extra behandelt werden.
$utf8 .= chr($ord_chrs_c);
}
break;
}
}
// print " '$utf8' ";
return $utf8;
}
function _revertManager($parent, $revertableUpload) {
// If there is nothing revertable then end here;
if ( count($revertableUpload) == 0 ) {
return;
}
$form = $parent->_startFieldSet($this->getLang('RevertManager'), 'translator_revert');
if ( count($revertableUpload) > 1 ) {
foreach ( $revertableUpload as $revertable ) {
$form->addElement(form_makeCheckboxField("revert[{$revertable['current_date_time']}][{$revertable['lang']}]", 1, $revertable['name'] . " ({$revertable['lang']})", null));
$form->addElement(form_makeTag('br'));
}
} else {
$form->addElement($this->getLang('RevertManagerHowTo'));
$revertable = array_shift($revertableUpload);
$form->addHidden("revert[{$revertable['current_date_time']}][{$revertable['lang']}]", 1);
}
$form->addElement(form_makeField('submit', 'fn[revertuploaded]', $this->getLang('Revert'), ''));
return $parent->_finishFieldset($form);
}
function _updateTranslation($Language, $Translation) {
global $INFO;
$date = date('Y-m-d H:i:s');
$this->revertableUpload = array();
if ( !in_array($Language, explode('|', $this->_getLanguages()) ) ) return;
$finalStatus = array(
'KeysNotInMaster' => array(),
'KeysMatchingExisting' => array(),
'CountOfKeys' => 0,
'CountOfToBeReverted' => 0,
);
foreach( $Translation as $key => $value ) {
$finalStatus['CountOfKeys']++;
if ( empty($value) ) {
$finalStatus['RemovedUserKeys'] += $this->_tryRevertEntry($Language, $_SERVER['REMOTE_USER'], $key);
continue;
}
// 18.05.2010
// Entfernt, damit Russische †bersetzungen wieder funktionieren
// $value = utf8_decode($value);
// 20.05.2010
// Russisch darf nicht ... aber Detusch z.B. muss ... welche Ausnahmen gibt es noch?
//if ( !in_array( $Language, array("ru", "zh_CN", "zh_TW", "ja", "ko") ) ) {
// $value = utf8_decode($value);
//}
// 06.12.2010 - €nderung der Datenbank. Es werden nur noch die codierten Werte gespeichert.
$value = $this->_stringEncode($value);
$status = $this->_insertTranslation($key, $value, $Language, $date);
if ( $status === -1 ) {
$finalStatus['KeysMatchingExisting'][]=$key;
} else if ( $status === true ) {
$finalStatus['CountOfToBeReverted']++;
}
}
if ( $finalStatus['CountOfToBeReverted'] > 0 ) {
$this->revertableUpload[$date] = array(
'lang' => $Language,
'name' => $_SERVER['REMOTE_USER'],
'current_date_time' => $date,
);
}
$this->_statusResult($finalStatus, $Language);
return;
}
/*
* Revert Uploaded Entries
*/
function _revertUploadedEntries($revertItems) {
if ( !is_array($revertItems) ) return;
foreach ( array_keys($revertItems) as $revertDateTime ) {
foreach ( array_keys($revertItems[$revertDateTime]) as $lang ) {
$items = $this->_revertEntries($revertDateTime, $lang);
msg($this->_messageReplacer('RevertedItems', $items));
}
}
}
function _tryRevertEntry($language, $user, $key=null) {
if ( $language == $this->getConf('defaultLanguage') ) return false;
$ADDITIONAL = "";
$EXECUTE = array($user, $language);
if ( !empty( $key )) {
$SQL = "SELECT tblTranslation.* FROM ((SELECT KeyID, MAX(Date) AS Date FROM tblTranslation WHERE Lang=? GROUP BY KeyID) as tblOldestAddTrans INNER JOIN tblTranslation ON tblOldestAddTrans.KeyID=tblTranslation.KeyID AND tblOldestAddTrans.Date=tblTranslation.Date) WHERE tblTranslation.KeyID=? AND tblTranslation.User=?;";
$this->database->prepare($SQL);
$this->database->execute($language, $key, $user);
$data = array(); $this->database->bind_assoc($data); $this->database->fetch();
$ADDITIONAL .= " AND KeyID=? AND Date=?";
$EXECUTE[] = $data['KeyID'];
$EXECUTE[] = $data['Date'];
}
$this->database->prepare("DELETE FROM tblTranslation WHERE User=? AND Lang=? $ADDITIONAL");
$this->database->execute($EXECUTE);
return $this->database->num_rows();
}
function _statusResult($finalStatus, $Lang, $FileName="User created") {
if ( !is_array($finalStatus['KeysNotInMaster']) ) $finalStatus['KeysNotInMaster'] = array($finalStatus['KeysNotInMaster']);
if ( !is_array($finalStatus['KeysMatchingExisting']) ) $finalStatus['KeysMatchingExisting'] = array($finalStatus['KeysMatchingExisting']);
if ( count($finalStatus['KeysNotInMaster']) > 0 ) {
msg($this->_messageReplacer('KeysNotInMaster', array(count($finalStatus['KeysNotInMaster']), count($finalStatus['KeysNotInMaster'])+$finalStatus['CountOfKeys'], $Lang, $FileName, implode(', ', $finalStatus['KeysNotInMaster']))), -1);
}
if ( count($finalStatus['KeysMatchingExisting']) > 0 ) {
msg($this->_messageReplacer('KeysMatchingExisting', array(count($finalStatus['KeysMatchingExisting']), $finalStatus['CountOfKeys'], $Lang, $FileName, implode(', ', $finalStatus['KeysMatchingExisting']))), 1);
}
if ( $finalStatus['RemovedUserKeys'] > 0 ) {
msg($this->_messageReplacer('RemovedUserKeys', array($finalStatus['RemovedUserKeys'], $finalStatus['CountOfKeys'])));
}
if ( $finalStatus['CountOfToBeReverted'] > 0 ) {
msg($this->getLang('RevertValues'));
return false;
}
if ( $finalStatus['KeysMatchingExisting'] == $finalStatus['CountOfKeys']
|| ( $finalStatus['KeysMatchingExisting'] == 0 && $finalStatus['CountOfToBeReverted'] == 0 && $finalStatus['KeysNotInMaster'] == 0 ) ) {
$_REQUEST['start'] += $_REQUEST['amount'];
}
return true;
}
function _getAmountOfMasterValues($categoryID=null, $version=null) {
$EXECUTE = array();
$ADDITIONAL = '';
if ( !empty($categoryID) ) {
$ADDITIONAL .= "CategoryID=?";
$EXECUTE[] = $categoryID;
}
if ( !empty($categoryID) ) {
if ( !empty($ADDITIONAL) ) {
$ADDITIONAL .= " AND ";
}
$ADDITIONAL .= "Version=?";
$EXECUTE[] = $version;
}
if ( !empty($ADDITIONAL) ) {
$ADDITIONAL = " WHERE " . $ADDITIONAL;
}
$this->database->prepare("SELECT COUNT(tblMaster.KeyID) as amount FROM tblMasterKey INNER JOIN tblMaster ON tblMasterKey.KeyID=tblMaster.KeyID $ADDITIONAL;");
$this->database->execute($EXECUTE);
$data=array(); $this->database->bind_assoc($data); $this->database->fetch();
return intval($data['amount']);
}
function _getAmountOfLanguageValues($categoryID, $version, $lang) {
//$SQL = "SELECT COUNT(DISTINCT tblMaster.KeyID) as amount FROM tblMaster INNER JOIN tblTranslation ON tblMaster.KeyID=tblTranslation.KeyID WHERE Lang=? AND categoryID=? and Version=?;";
$SQL = "SELECT COUNT(DISTINCT tblMaster.KeyID) as amount FROM tblMaster INNER JOIN tblTranslation ON tblMaster.KeyID=tblTranslation.KeyID AND Lang=? WHERE categoryID=? and Version=?;";
//$SQL = "SELECT COUNT(tblMaster.KeyID) as amount FROM tblMaster INNER JOIN (SELECT KeyID FROM tblTranslation WHERE Lang=? GROUP BY KeyID) as tblTrans ON tblTrans.KeyID=tblMaster.KeyID WHERE categoryID=? and Version=?;";
$this->database->prepare($SQL);
$this->database->execute($lang, $categoryID, $version);
$data=array(); $this->database->bind_assoc($data); $this->database->fetch();
return intval($data['amount']);
}
function _downloadLink($category, $version, $language, $name, $NoEncoded=false) {
global $ID;
$options = array('export_language' => '1', 'Category' => $category, 'Version' => $version, 'Language' => $language);
if ( $NoEncoded ) $options['NoEncode'] = 'true';
return tpl_link(wl($ID, $options),$name,null,true);
}
function _messageReplacer($LangKey, $replacements) {
if ( !is_array($replacements))
$replacements = array($replacements);
$language = $this->getLang($LangKey);
for($i=0; $i<count($replacements); $i++) {
$language = str_replace('{' . $i . '}', $replacements[$i], $language);
}
return $language;
}
function _getChangerJS($hasLanguageNode=true) {
$categories = $this->_getCategories();
$JSCategories = "var categories = {";
foreach ( $categories as $name => $category ) {
// Bottlenec 1
$languages = $this->_getAvailableLanguagesVersions($category['CategoryID']);
$versions = array_keys($languages);
rsort($versions, SORT_NUMERIC);
if ( empty($versions) ) { continue; }
$JSCategories .= "'$name': {";
// Bottlenec 2
foreach( $versions as $version ) {
$categories[$name]['Versions'][$version] = $hasLanguageNode ? array_unique(array_merge(array('export all'), $languages[$version])) : array();
$JSCategories .= "'$version':['" . implode("','", $categories[$name]['Versions'][$version]) . "']" . ( $version != end($versions) ? ',' : '' );
}
$JSCategories .= "}" . ( $name != end(array_keys($categories)) ? ',' : '' );
}
$JSCategories .= "};";
if ( empty( $_REQUEST['Category']) ) $_REQUEST['Category'] = array_shift(array_keys($categories));
if ( empty( $_REQUEST['Version']) && !empty($categories[$_REQUEST['Category']]['Versions']) ) $_REQUEST['Version'] = array_shift(array_keys($categories[$_REQUEST['Category']]['Versions']));
$nodes = "var node = this.parentNode.parentNode.getElementsByTagName('select');";
$removeNodes = "while(node[i].hasChildNodes()) { node[i].removeChild(node[i].firstChild) }";
$createVersionNode = "for ( var vers in categories[cat] ) { var opt = document.createElement('option'); opt.appendChild(document.createTextNode(vers)); node[i].appendChild(opt) } ";
$createLanguageNode = "for ( var lang in categories[cat][version] ) { var opt = document.createElement('option'); opt.appendChild(document.createTextNode(categories[cat][version][lang])); node[i].appendChild(opt) } ";
$nodeValues = "var cat = null; var version = null; for( var i=0; i<node.length; i++ ) { if (node[i].name == 'Category') { cat = node[i].value; } if (node[i].name == 'Version') { version = node[i].value; } }";
$versionNode = "for( var i=0; i<node.length; i++ ) { if (node[i].name != 'Version') { continue; } $removeNodes $createVersionNode break; }";
$languageNode = "for( var i=0; i<node.length; i++ ) { if (node[i].name != 'Language') { continue; } $removeNodes $createLanguageNode break; }";
$changeCategory = $JSCategories . " for ( var cat in categories) { if (this.value != cat ) { continue;} $nodes $versionNode" . ( $hasLanguageNode ? "var version = node[i].value; $languageNode " : '' ) . "break; } ";
$changeVersion = $JSCategories . " $nodes $nodeValues $languageNode";
return array($categories, $changeCategory, $changeVersion);
}
function setUTF8Collation() {
$this->database->databaseConnection->query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
$this->database->databaseConnection->query("SET CHARACTER SET 'utf8'");
}
/**
* Allow the plugin to prevent DokuWiki creating a second instance of itself
*
* @return bool true if the plugin can not be instantiated more than once
*/
function isSingleton() {
return true;
}
/**
* PrŸfen, ob die aktuelle Version schonmal hinsichtlich der Datenbankanbindung geprŸft wurde
*/
function currentVersionChecked() {
if ( !file_exists( dirname(__FILE__).'/.translationcheck' ) ) {
return false;
}
$checkDate = implode('', file( dirname(__FILE__).'/.translationcheck' ) );
$hash = confToHash(dirname(__FILE__).'/info.txt');
return $hash['date'] == trim($checkDate);
}
/**
* Set the current Version to be checked
*/
function setCurrentVersionChecked() {
$file = dirname(__FILE__).'/.translationcheck';
$fh = fopen($file, 'w');
if ( !$fh ) {
msg('Could not write version check file.');
return false;
}
$hash = confToHash(dirname(__FILE__).'/info.txt');
fwrite( $fh, $hash['date']);
fclose($fh);
return true;
}
}
//Setup VIM: ex: et ts=4 enc=utf-8 :