public static function getSummary() { $dbConnection = Config::getConnection(); $q = 'SELECT TranslationId, TranslationName, BrowserMatch, ImagePath, ' . 'RfcLanguage, UNIX_TIMESTAMP(lastChangeStatic), UNIX_TIMESTAMP(lastChangeDynamic) ' . 'FROM Page_Translations WHERE Active = 1 OR TranslationId = 1'; $ret = array(); foreach (DataProvider::fetchAll($q) as $r) { $ret[$r['TranslationId']] = array('TranslationId' => $r['TranslationId'], 'TranslationName' => $r['TranslationName'], 'BrowserMatch' => $r['BrowserMatch'], 'ImagePath' => $r['ImagePath'], 'RfcLanguage' => $r['RfcLanguage'], 'lastChangeStatic' => $r['UNIX_TIMESTAMP(lastChangeStatic)'], 'lastChangeDynamic' => $r['UNIX_TIMESTAMP(lastChangeDynamic)']); } return $ret; }
public static function getChanged($tId) { $ret = array(); if ($tId !== 1) { $q = "SELECT Req, Trans FROM Page_StaticTranslation WHERE TranslationId = 1"; foreach (DataProvider::fetchAll($q) as $r) { $req = $r['Req']; $q = "SELECT Trans FROM Page_StaticTranslation WHERE " . "Req = '{$req}' AND TranslationId = {$tId} AND Time < (" . "SELECT Time FROM Page_StaticTranslation " . "WHERE Req = '{$req}' AND TranslationId = 1)"; foreach (DataProvider::fetchAll($q) as $x) { array_push($ret, array('Description' => TranslationProvider::getDescription($req), 'Original' => $r['Trans'], 'Translation' => array('TranslationId' => $tId, 'Translation' => $x['Trans'], 'Payload' => $req, 'TranslationProvider' => 'StaticTranslationProvider'))); } } } return $ret; }
/** @param $sId String studyId like the _$sId suffixes in the db. @param $t [ LanguageIx => String , IxElicitation => String , IxMorphologicalInstance => String , AlternativePhoneticRealisationIx => String , AlternativeLexemIx => String] @return [ lang => FilePathPart , word => SoundFileWordIdentifierText , pron => AlternativePhoneticRealisationIx || '' , lex => AlternativeLexemIx || ''] Gathers information necessary to look for a sound file. */ public static function soundPathParts($sId, $t) { $lIx = $t['LanguageIx']; $wId = $t['IxElicitation'] . $t['IxMorphologicalInstance']; if (!isset($lIx) || !isset($wId)) { return array(); } $base = Config::$soundPath; $lq = "SELECT FilePathPart FROM Languages_{$sId} WHERE LanguageIx = {$lIx}"; $wq = "SELECT SoundFileWordIdentifierText FROM Words_{$sId} " . "WHERE CONCAT(IxElicitation, IxMorphologicalInstance) = '{$wId}'"; $getFirst = function ($q) { $set = DataProvider::fetchAll($q); if (count($set) === 0) { Config::error("Problem with query: '{$q}'", true); return ''; } return current(current($set)); }; return array('lang' => $getFirst($lq), 'word' => $getFirst($wq), 'pron' => $t['AlternativePhoneticRealisationIx'] > 1 ? $t['AlternativePhoneticRealisationIx'] : '', 'lex' => $t['AlternativeLexemIx'] > 1 ? $t['AlternativeLexemIx'] : ''); }
/** @param $tId TranslationId @return $ret [[ Description => [Req => String, Description => String] , Original => String , Translation => [TranslationId => $translationId , Translation => String, Payload => String, TranslationProvider => String] ]] Returns entries where the translation has been saved earlier than the english translation. These are the cases where it makes sense to review the translation. Naturally for ($tId === 1) $ret will be empty. */ public static function getChanged($tId) { //Sanitizing $tId: $tId = is_numeric($tId) ? $tId : 1; //$ret to return: $ret = array(); //Changed only occur in $tId !== 1: if ($tId !== 1) { $q = 'SELECT Category, Field, Trans ' . 'FROM Page_DynamicTranslation WHERE TranslationId = 1'; foreach (DataProvider::fetchAll($q) as $r) { $c = $r['Category']; $f = $r['Field']; $q = "SELECT Trans FROM Page_DynamicTranslation " . "WHERE Category = '{$c}' AND Field = '{$f}' AND TranslationId = {$tId} " . "AND Time < (SELECT Time FROM Page_DynamicTranslation " . "WHERE Category = '{$c}' AND Field = '{$f}' AND TranslationId = 1)"; foreach (DataProvider::fetchAll($q) as $x) { $desc = Translation::categoryToDescription($c); array_push($ret, array('Description' => $desc, 'Original' => $r['Trans'], 'Translation' => array('TranslationId' => $tId, 'Translation' => $x['Trans'], 'Payload' => $f, 'TranslationProvider' => $c))); } } } //Done: return $ret; }
/** @return [TableName => [json => JsonRow, reason => String]] Checks all notValues entries of Integrity::$constraints. TableName is expected to be a complete name, not a prefix. */ public static function checkNotValues() { $ret = array(); foreach (self::$constraints as $tPrefix => $tDesc) { if (array_key_exists('notValues', $tDesc)) { $or = array(); foreach ($tDesc['notValues'] as $k => $v) { array_push($or, "{$k} = {$v}"); } $or = implode(' OR ', $or); foreach (self::getTableNames($tPrefix, $tDesc) as $table) { $q = "SELECT * FROM {$table} WHERE {$or}"; $rs = DataProvider::fetchAll($q); if (count($rs) > 0) { $jsons = array(); foreach ($rs as $r) { array_push($jsons, array('json' => Config::toJSON($r), 'reason' => 'Forbidden combination of key/value occured.')); } $ret[$table] = $jsons; } } } } return $ret; }
/** @param $tId TranslationId the Translation to search @param $searchText String the Text to search @param $searchStrategy {'both','translation','original'} @return $ret [obj] || Exception obj will be arrays resembling JSON objects following this syntax: { Description: {Req: '', Description: ''} , Original: '' , Translation: {TranslationId: 5, Translation: '', Payload: '', TranslationProvider: ''} } Searches for the given $searchText and returns array to allow translation for found entries. $searchStrategy specifies if the originals, the translations or both should be searched. */ public function search($tId, $searchText, $searchStrategy = 'both') { //Sanitizing $tId: $tId = is_numeric($tId) ? $tId : 1; //Sanitizing $searchText: $searchText = Config::getConnection()->escape_string($searchText); //Sanitizing $searchStrategy: if (preg_match('/^(both|translation|original)$/', $searchStrategy) === 0) { return new Exception("Invalid \$searchStrategy: '{$searchStrategy}'"); } //Table to use: $tableName = $this->getTable(); //Study to use: $study = $this->getStudy(); // String || null //Column specific code: return $this->withColumn(function ($column) use($tId, $searchText, $searchStrategy, $tableName, $study) { $category = $column['category']; //Description to use for entries: $description = TranslationTableProjection::fetchDescription($column); //Payload -> $entry to prevent duplicates $payloadMap = array(); //Searching in originals: if ($searchStrategy === 'both' || $searchStrategy === 'original') { $columnName = $column['columnName']; $fieldSelect = $column['fieldSelect']; $q = "SELECT {$columnName} AS columnName, {$fieldSelect} AS fieldSelect " . "FROM {$tableName} " . "WHERE {$columnName} LIKE '%{$searchText}%'"; $originals = DataProvider::fetchAll($q); foreach ($originals as $original) { $fieldSelect = $original['fieldSelect']; if ($study !== null) { $fieldSelect = "{$study}-{$fieldSelect}"; } //Stub for $entry: $entry = array('Description' => $description, 'Original' => $original['columnName'], 'Translation' => array('TranslationId' => $tId, 'Translation' => '', 'Payload' => $fieldSelect, 'TranslationProvider' => $category)); if ($study !== null) { $entry['Study'] = $study; } //Trying to add existing translation: $entry = $this->addTranslation($entry); //Putting $entry into map: $payloadMap[$fieldSelect] = $entry; } } //Searching in translations: if ($searchStrategy === 'both' || $searchStrategy === 'translation') { //Setting $columnName and $fieldSelect: $columnName = $column['columnName']; $fieldSelect = $column['fieldSelect']; //Need to fetch all originals to find matching translations: $q = "SELECT {$columnName} AS columnName, {$fieldSelect} AS fieldSelect " . "FROM {$tableName} "; $originals = DataProvider::fetchAll($q); foreach ($originals as $original) { $fieldSelect = $original['fieldSelect']; if ($study !== null) { $fieldSelect = "{$study}-{$fieldSelect}"; } //Preventing possible duplicates: if (array_key_exists($fieldSelect, $payloadMap)) { continue; } //Checking for translation: $q = "SELECT Trans FROM Page_DynamicTranslation " . "WHERE TranslationId = {$tId} " . "AND Category = '{$category}' " . "AND Field = '{$fieldSelect}' " . "AND Trans LIKE '%{$searchText}%' " . "LIMIT 1"; foreach (DataProvider::fetchAll($q) as $r) { //foreach works as if $entry = array('Description' => $description, 'Original' => $original['columnName'], 'Translation' => array('TranslationId' => $tId, 'Translation' => $r['Trans'], 'Payload' => $fieldSelect, 'TranslationProvider' => $category)); if ($study !== null) { $entry['Study'] = $study; } $payloadMap[$fieldSelect] = $entry; } } } //Done: return array_values($payloadMap); }); }
} $stmt->close(); } else { ?> <table class="table table-bordered"> <thead> <tr> <th>Ix Elicitation:</th> <th>Name:</th> <th>Description:</th> <th>Action:</th> </tr> </thead> <tbody><?php $q = 'SELECT IxElicitation, name, description FROM Meanings'; $meanings = DataProvider::fetchAll($q); foreach ($meanings as $meaning) { $IxElicitation = $meaning['IxElicitation']; $name = $meaning['name']; $description = $meaning['description']; echo '<tr>' . "<td>{$IxElicitation}</td>" . "<td>{$name}</td>" . "<td>{$description}</td>" . "<td><a href='index.php?action=meanings&IxElicitation={$IxElicitation}' class='btn'>Edit</a></td>" . '</tr>'; } ?> <tr><form action="index.php?action=meanings" method="post"> <td><input name="IxElicitation" value="" placeholder="New name" type="text" required></td> <td><input name="name" value="" placeholder="New name" type="text" required></td> <td><textarea name="description" value="" placeholder="New description" type="text" required></textarea></td> <td><button type="submit" class="btn">Save</button></td> <input name="example" value="New Example" type="hidden"> <input name="justification" value="New Justification" type="hidden"> </form></tr>