TranslationString.php

Go to the documentation of this file.

<?php
/***************************************************************************
 *   Copyright (C) 2005-2020 by Ferenc Veres                                                       *
 *   lion@netngine.hu                                                                                                      *
 *                                                                         *
 *   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 3 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, see <http://www.gnu.org/licenses/>. *
 ***************************************************************************/
include_once('TranslationWordList.php');

class TranslationString
{
        private $StringID;      
        private $ProjectID;     
        private $Original;      
        private $LocaleID;      
        private $Translation;   
        private $SentenceID;    
        private $LastSeen;      
        private $RowOrder;      
        private $Matches;       
        private $Words;         
        public static $SentenceLengthCache; 
        public static $WordSentencesCache; 
        function __construct($stringID = 0, $projectID = 0, $original = "", $localeID = 1, $translation = "", $sentenceID = 0, $lastSeen = 0, $rowOrder = 0)
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                // Load string from database?
                if($stringID != 0 && $original == "")
                {
                        $rs = $DB->Execute("SELECT ID, ProjectID, Original, LocaleID, Translation, SentenceID, LastSeen, RowOrder FROM ".$Tables["Strings"]." WHERE ID=?", array($stringID));
                        
                        // Found the string?
                        if($row = $rs->FetchRow())
                        {
                                // Yes! Load details.
                                $this->StringID = $row["ID"];
                                $this->ProjectID = $row["ProjectID"];
                                $this->Original = $row["Original"];
                                $this->LocaleID = $row["LocaleID"];
                                $this->Translation = $row["Translation"];
                                $this->SentenceID = ($row["SentenceID"] != null ? $row["SentenceID"] : 0);
                                $this->LastSeen = $row["LastSeen"];
                                $this->RowOrder = $row["RowOrder"];
                        }
                        else
                        {
                                throw new Exception("TranslationString: Requested not existing string from databse. ID: $stringID");
                        }
                }

                // Initialize a new string from constructor call
                // ($stringID may be 0, ->Save can identify the same $original in the same project or
                // create a new string automaticly!)
                if($original != "")
                {
                        $this->StringID = $stringID;
                        $this->ProjectID = $projectID;
                        $this->Original = $original;
                        $this->LocaleID = $localeID;
                        $this->Translation = $translation;
                        $this->SentenceID = ($sentenceID != null ? $sentenceID : 0);
                        $this->LastSeen = $lastSeen;
                        $this->RowOrder = $rowOrder;
                }
        }

        /* Static cache initialization */
        
        public static function InitSentenceLengthCache()
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                // Load all already stored sentences' length for word counting
                $rs = $DB->Execute("SELECT SentenceID, Sum(Occurences) AS Count FROM ".$Tables["SentenceIndex"]." GROUP BY SentenceID");
        
                while($row = $rs->FetchRow())
                {
                        TranslationString::$SentenceLengthCache[$row["SentenceID"]] = $row["Count"];
                }
        }
        
        public static function InitWordSentencesCache()
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                // Load all already stored sentences
                $rs = $DB->Execute("SELECT SentenceID, WordID FROM ".$Tables["SentenceIndex"]);
        
                while($row = $rs->FetchRow())
                {
                        TranslationString::$WordSentencesCache[$row["WordID"]][] = $row["SentenceID"];
                }
        }
        
        /* Property access */

        public function GetStringID()
        {
                return $this->StringID;
        }

        public function GetProjectID()
        {
                return $this->ProjectID;
        }
        
        public function GetOriginal()
        {
                return $this->Original;
        }

        public function GetLocaleID()
        {
                return $this->LocaleID;
        }

        public function GetTranslation()
        {
                return $this->Translation;
        }

        public function GetSentenceID()
        {
                return $this->SentenceID;
        }

        public function GetLastSeen()
        {
                return $this->LastSeen;
        }

        public function SetTranslation($translation)
        {
                $this->Translation = $translation;
        }

        public function SetRowOrder($index)
        {
                $this->RowOrder= $index;
        }

        /* Methods to STORE strings */

        function Save()
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();

                // Check paramters
                if($this->Original == "")
                {
                        throw new Exception("TranslationString->Save() called without Original string.");
                }

                // Find and/or Store our words in the wordindex (always)
                $this->Words = new TranslationWordList($this->Original);

                // Check if we have this Original string already
                $rs = $DB->Execute("SELECT ID, SentenceID, ProjectID FROM ".$Tables["Strings"]." WHERE Original = ? AND LocaleID = ?", array($this->Original, $this->LocaleID));

                // Loop on all same Original strings (with same locale)
                while($row = $rs->FetchRow())
                {
                        if($this->SentenceID == 0)
                        {
                                // Will get the same for all nonzero (indexed), hopefully.
                                $this->SentenceID = ($row["SentenceID"] != null ? $row["SentenceID"] : 0);
                        }

                        // If ProjectID matches too, won't create new string, just update it!
                        if($row["ProjectID"] == $this->ProjectID)
                        {
                                $this->StringID = $row["ID"];
                        }
                }

                // Do a sentence index: if there was no Original yet and we have any words.
                if($this->SentenceID == 0 && $this->Words->SumCount() != 0)
                {
                        // Find in sentence index, store the 100% match's Sentence ID
                        $this->SentenceID = $this->FindSentenceIndex(false);

                        // No 100% match? Store in sentence index!
                        if($this->SentenceID == 0)
                        {       
                                $this->SentenceID = $this->StoreSentenceIndex();

                                // Store length in the cache
                                TranslationString::$SentenceLengthCache[$this->SentenceID] = $this->Words->SumCount();
                        }
                }
                
                // Store string. Update or insert

                if($this->StringID == 0)
                {
                        // INSERT: No same Original in same Project, insert as new

                        $DB->Execute("INSERT INTO ".$Tables["Strings"]." (ProjectID, Original, SentenceID, LocaleID, Translation, LastSeen, RowOrder) VALUES (?,?,?,?,?, now(), ?)",
                                array(
                                        $this->ProjectID,
                                        $this->Original,
                                        ($this->SentenceID != 0 ? $this->SentenceID : null),
                                        $this->LocaleID,
                                        $this->Translation,
                                        $this->RowOrder));

                        if(!$DB->Insert_ID())
                        {
                                throw new Exception("DB Error: Insert_ID not supported");
                        }

                        // Get the new ID
                        $this->StringID = $DB->Insert_ID();
                }
                else
                {
                        // UPDATE: Same Original exists in the same Project, no dupes! Overwrite.
                        $DB->Execute("UPDATE ".$Tables["Strings"]." SET Translation = ?, SentenceID = ?, LastSeen = now(), RowOrder = ? WHERE ID = ?",
                                array(
                                        $this->Translation,
                                        ($this->SentenceID != 0 ? $this->SentenceID : null),
                                        $this->RowOrder,
                                        $this->StringID));
                }


                // Return the found or new string ID. May be 0 for empty sentences.
                return $this->StringID;
        }

        public function DestroyData()
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                if($this->StringID == 0)
                {
                        throw new Exception("String DestroyData called on a not loaded string.");
                }
                $DB->Execute("DELETE FROM Strings WHERE ID = ?", array($this->StringID));
                $this->StringID = 0;
        }

        /**** Methods to find strings ****/

        function FindSimilar($FindText = "", $localeID = 0, $FilterPercent = 60)
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                unset($translations);
                unset($percents);

                if($FindText != "")
                {
                        $this->Original = $FindText;
                }
                if($localeID != 0)
                {
                        $this->LocaleID = $localeID;
                }

                $this->Words = new TranslationWordList($this->Original);

                // No words at all? Return empty stuff.
                if($this->Words->SumCount() == 0)
                {
                        return;
                }

                // Try finding it, allowing similar
                $this->FindSentenceIndex(true);

                // No matches at all?
                if(empty($this->Matches))
                {
                        return;
                }

                // Order the array to descending and filter unwanted percentage results
                $res = $this->Matches;

                // Drop unwanted: Low percent
                foreach($res as $id => $percent)
                {
                        if($percent >= $FilterPercent)
                        {
                                $filtered[$id] = $percent;
                        }
                }

                // No matches above the percent limit?
                if(empty($filtered))
                {
                        return;
                }
                
                // Sort by percent
                arsort($filtered);

                // Remember percentage
                foreach($filtered as $id => $percent)
                {
                        $translations[$id]["percent"] = $percent;
                }

                // Get ALL (found above) strings in one pass
                $idSQL = join(array_keys($filtered), ",");
                
                $rs = $DB->Execute("SELECT ID, ProjectID, Original, Translation, LocaleID, SentenceID, TIMESTAMP(LastSeen) as LastSeen, RowOrder FROM ".$Tables["Strings"]." WHERE SentenceID IN ($idSQL) AND LocaleID = ".$this->LocaleID);

                // Loop on all strings and store in array
                while($row = $rs->FetchRow())
                {
                        $translations[$row["SentenceID"]]["strings"][] = new TranslationString(
                                $row["ID"], 
                                $row["ProjectID"], 
                                $row["Original"], 
                                $row["LocaleID"], 
                                $row["Translation"], 
                                $row["SentenceID"],
                                $row["LastSeen"],
                                $row["RowOrder"]);
                }

                // Drop the ones which were in wrong locale so no result now
                // We need this so late, becase we do the percentage order above
                foreach($translations as $id => $tr)
                {
                        if(empty($translations[$id]["strings"]))
                        {
                                unset($translations[$id]);
                        }
                }

                // Return the array containing the good matches (contains TranslationString objects)
                return $translations;
        }

        function FindText($findText = "", $localeID = 0, $findOriginal, $start = 0, $limit = 100)
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();

                unset($translations);

                // Find this object's text?
                if($findText == "")
                {
                        if($findOriginal)
                        {
                                $findText = $this->Original;
                        }
                        else
                        {
                                $findText = $this->Translation;
                        }
                }

                // Cannot search empty, return.
                if($findText == "")
                {
                        return;
                }

                // Use this object's locale ID?
                if($localeID == 0)
                {
                        $localeID = $this->LocaleID;
                }

                // Which column to look in?
                if($findOriginal)
                {
                        $column = "Original";
                }
                else
                {
                        $column = "Translation";
                }

                // Select all similar strings with LIKE %%
                $rs = $DB->Execute("SELECT ID, ProjectID, Original, Translation, LocaleID, SentenceID, TIMESTAMP(LastSeen) as LastSeen, RowOrder FROM ".$Tables["Strings"]." WHERE $column LIKE '%".fixstr($findText)."%'  AND LocaleID = ? LIMIT ?,?", array($localeID, $start, $limit));

                // Loop on all strings and store in array
                while($row = $rs->FetchRow())
                {
                        $translations[$row["SentenceID"]]["strings"][] = new TranslationString(
                                $row["ID"], 
                                $row["ProjectID"], 
                                $row["Original"], 
                                $row["LocaleID"], 
                                $row["Translation"], 
                                $row["SentenceID"],
                                $row["LastSeen"],
                                $row["RowOrder"]);
                }
                $rs->Close();

                if(empty($translations))
                {
                        return;
                }
                
                return $translations;
        }

        /* Private methods */
        
        private function FindSentenceIndex($findSimilarToo)
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                unset($this->Matches);
                
                // Create a word ID string for SQL
                $idSQL = "";
                foreach($this->Words as $w)
                {
                        $idSQL .= ($idSQL != "") ? "," : "";
                        $idSQL .= $w->GetID();
                }

                // Get all sentences with any of our words
                $rs = $DB->Execute("SELECT SentenceID, WordID, Occurences FROM ".$Tables["SentenceIndex"]." WHERE WordID IN ($idSQL) ORDER BY SentenceID");

                // Loop on the possible sentences
                $sID = 0;
                $exactMatch = 0;
                do
                {
                        $row = $rs->FetchRow();

                        // Another sentence coming or end of query?
                        if($row == false || $row["SentenceID"] != $sID)
                        {
                                // If finished with a sentence id, process it
                                if($sID != 0)
                                {
                                        // Set real length, becuase the SQL does not fetch all words, just the common ones.
                                        $oldWords->SetFakeLength(TranslationString::$SentenceLengthCache[$sID]);
                                        $percent = $this->Words->CompareByID($oldWords);

                                        // Store match percent and exact match ID if any
                                        if($findSimilarToo)
                                        {
                                                $this->Matches[$sID] = $percent;
                                        }
                                        if($percent == 100)
                                        {
                                                // Found exact match
                                                $exactMatch = $sID;

                                                // Don't need similar entries, force quit searching
                                                if(!$findSimilarToo)
                                                {
                                                        break;
                                                }
                                        }
                                }
                                
                                // Start new sentence (if not query end)
                                if($row != false)
                                {
                                        $oldWords = new TranslationWordList();
                                        $sID = $row["SentenceID"];
                                }
                        }

                        // Store current word, either first in this $oldWords or not,
                        // unless the query was over
                        if($row)
                        {
                                $oldWords->AddID($row["WordID"], $row["Occurences"]);
                        }
                        
                }
                while($row);

                return $exactMatch;
        }

        private function StoreSentenceIndex()
        {
                $DB =& GetDbConn();
                $Tables =& GetTables();
                
                // Get new max Sentence ID
                $newID = $DB->GetOne("SELECT Max(SentenceID)+1 FROM ".$Tables["SentenceIndex"]);

                if(!$newID)
                {
                        $newID = 1;
                }

                // Insert all wordIDs and occurence counts for the new sentence ID
                foreach($this->Words as $w)
                {
                        $DB->Execute("INSERT INTO ".$Tables["SentenceIndex"]." VALUES (?,?,?)", array($newID, $w->GetID(), $w->GetCount()));

                        // Update static cache too, add this sentence to all words.
                        //TranslationString::$WordSentencesCache[$w->GetID()][] = $newID;
                }

                return $newID;
        }

}
?>