Changeset 401 for trunk

Timestamp:

Mar 24, 2010, 7:47:23 AM (15 years ago)

Author:

george

Message:

• Upraveno: SQL dotaz pro generování exportu přepracován na využití nedávno uvedených tabulek ExportUser a ExportLanguage. Generování seznamu Idpřekaldatelů a dotaz typu User IN (...) nahrazen povelem JOIN ExportUser.
• Upraveno: Funkce pro sestavování sjednocena přehledněji ve formě jedné obecné metody BuildQuery.

File:

• trunk/export/export.php (modified) (7 diffs)

trunk/export/export.php

@@ -24 +24 @@
     $DbResult = $this->Database->query('SELECT * FROM `Export` WHERE `Id`='.$this->Id);
     if($DbResult->num_rows == 0) throw new Exception('Export '.$this->Id.' neexistuje');
-    $this->Export = $DbResult->fetch_assoc();
-    
-    // Filter selected languages
-    $this->WhereLang = '';
-    $DbResult = $this->Database->query('SELECT * FROM `ExportLanguage` WHERE `Export`='.$this->Id.' ORDER BY `Sequence`');
-    echo($this->Database->error);
-    while($Language = $DbResult->fetch_assoc())
-      $this->WhereLang .= ' OR (`Language` = '.$Language['Language'].')'; 
-    if($this->WhereLang == '') $this->WhereLang = 1; 
-      else $this->WhereLang = '('.substr($this->WhereLang, 4).')';
+    $this->Export = $DbResult->fetch_assoc();   
 
     // Filter selected users
-    $this->SelectedUsers = '';  
     $this->UserNames = '';
     $DbResult = $this->Database->query('SELECT `ExportUser`.*, `User`.`Name`, `User`.`ID` FROM `ExportUser` LEFT JOIN `User` ON `User`.`ID`=`ExportUser`.`User` WHERE `ExportUser`.`Export`='.$this->Id.' ORDER BY `ExportUser`.`Sequence`');
     while($UserLine = $DbResult->fetch_assoc())
     {
-      $this->SelectedUsers .= ','.$UserLine['ID'];
-      $this->UserNames .= ','.$UserLine['Name'];
-    }
-    $this->SelectedUsers = substr($this->SelectedUsers, 1);
-    $this->UserNames = substr($this->UserNames, 1);      
+      $this->UserNames .= ', '.$UserLine['Name'];
+    }
+    $this->UserNames = substr($this->UserNames, 2);      
    
-    if($this->SelectedUsers == '')
-    {
-      $this->WhereUsers = 0;
-      $this->OrderByUserList = '';
-    } else
-    {
-      $this->WhereUsers = '(`User` IN ('.$this->SelectedUsers.'))';
-      $this->OrderByUserList = ' ORDER BY FIELD(`User`, '.$this->SelectedUsers.')';
-    }
-    
     $DbResult = $this->Database->query('SELECT * FROM `ClientVersion` WHERE `Id`='.$this->Export['ClientVersion']);
     $this->ClientVersion = $DbResult->fetch_assoc();    
+  }
+  
+  function BuildQuery($Group, $Columns = '`T`.*')
+  {
+    $Query = 'SELECT * FROM (SELECT '.$Columns.', `User`.`Name` AS `UserName` FROM `'.$Group['TablePrefix'].'` AS `T`'.
+    ' JOIN `ExportUser` ON (`ExportUser`.`User`=`T`.`User`) AND (`ExportUser`.`Export`='.$this->Id.') '.
+    ' JOIN `User` ON `User`.`ID`=`T`.`User`'.
+    ' JOIN `ExportLanguage` ON (`ExportLanguage`.`Export`='.$this->Id.')'.
+    ' WHERE (`Complete` = 1) AND (`VersionStart` <= '.$this->ClientVersion['BuildNumber'].') AND (`VersionEnd` >= '.$this->ClientVersion['BuildNumber'].')'.
+    ' ORDER BY `ExportLanguage`.`Sequence`, `ExportUser`.`Sequence`) AS `TT` GROUP BY `TT`.`Entry`';  
+    return($Query);
   }
 
@@ -91 +80 @@
       if($Group['MangosTable'] != '')
       {
-        $Query = 'SELECT * FROM (SELECT * FROM '.$Group['TablePrefix'].' WHERE (`Complete` = 1) AND `VersionStart` <= '.$this->ClientVersion['BuildNumber'].' AND `VersionEnd` >= '.$this->ClientVersion['BuildNumber'].' AND '.$this->WhereLang.' AND '.$this->WhereUsers.$this->OrderByUserList.') AS `T` GROUP BY `T`.`Entry`';  
-        //echo($Query);
         $Buffer .= "\n\n-- ".$Group['Name']."\n\n";
-        $DbResult2 = $this->Database->query($Query);
+        $DbResult2 = $this->Database->query($this->BuildQuery($Group));
         while($Line = $DbResult2->fetch_array()) 
         {
@@ -134 +121 @@
     $Buffer = $this->ExportToMangosSQL();
   
+    /*
     // Data to aowow
-
     $Database2 = new mysqli($this->Config['Database']['Host'], $this->Config['Database']['User'], $this->Config['Database']['Password'], $this->Config['Database']['Database']);
     $Database2->query('SET NAMES '.$this->Config['Database']['Charset']);
@@ -176 +163 @@
       $Buffer .= "\n\n";  
     }
+    */
     if($this->Export['WithDiacritic'] != 1) $Buffer = utf2ascii($Buffer);
     return($Buffer);
@@ -210 +198 @@
         $Group['MangosTableIndex'] = 'entry';
       }
-      $Query = 'SELECT *,(SELECT `Name` FROM `User` WHERE `User`.`ID`=`T`.`User`) AS `UserName` FROM (SELECT * FROM `'.$Group['TablePrefix'].'` WHERE (`Complete` = 1) AND (`VersionStart` <= '.$this->ClientVersion['BuildNumber'].') AND (`VersionEnd` >= '.$this->ClientVersion['BuildNumber'].') AND '.$this->WhereLang.' AND '.$this->WhereUsers.$this->OrderByUserList.') AS `T` GROUP BY `T`.`entry`'; 
-      //echo($Query);
       $Buffer .= '    <group id="'.$Group['Id'].'" name="'.$Group['TablePrefix'].'">'."\n";
-      $DbResult2 = $this->Database->query($Query);
+      $DbResult2 = $this->Database->query($this->BuildQuery($Group));
       while($Line = $DbResult2->fetch_assoc()) 
       {
@@ -247 +233 @@
       if(file_exists('../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc')) 
       {
-        $i = 1;
-        $columns = '';
+        $I = 1;
+        $Columns = '';
         foreach($TranslationTree[$Group['Id']]['Items'] as $Column) 
         {
-          $columns .= ' `T`.`'.$Column['Column'].'` AS `Text'.$i.'`,';
-          $i = $i + 1;
+          $Columns .= '`T`.`'.$Column['Column'].'` AS `Text'.$I.'`, ';
+          $I = $I + 1;
         }
-        $Query = 'SELECT '.$columns.' `T`.`Entry` AS `Entry` FROM (SELECT * FROM `'.$Group['TablePrefix'].'` WHERE (`Complete` = 1) AND '.$this->WhereLang.' AND '.$this->WhereUsers.' AND (`VersionStart` <= '.$this->ClientVersion['BuildNumber'].') AND (`VersionEnd` >= '.$this->ClientVersion['BuildNumber'].')) AS `T` GROUP BY `T`.`Entry`';
+        $Columns = substr($Columns, 0, -2);
+
+        $Query = $this->BuildQuery($Group, $Columns);
         $Query = str_replace('`', '\`', $Query);
         $Query = '"'.str_replace(' ', '\ ', $Query).'"';
       
-        $params = '-u '.$Config['Database']['User'].' -p '.$Config['Database']['Password'].' -f ../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc -n '.$this->TempDir.'dbc/'.$Group['DBCFileName'].'.dbc -c '.GetDBCColumns($this->ClientVersion['Version'], $Group['DBCFileName']).' -q '.$Query;
+        $Params = '-u '.$Config['Database']['User'].' -p '.$Config['Database']['Password'].' -f ../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc -n '.$this->TempDir.'dbc/'.$Group['DBCFileName'].'.dbc -c '.GetDBCColumns($this->ClientVersion['Version'], $Group['DBCFileName']).' -q '.$Query;
       
-        //echo('./DBCExport '.$params);       
-        exec('./DBCExport '.$params);
+        exec('./DBCExport '.$Params);
        
       } else echo('Zdrojový soubor '.'../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc'.' nenalezen.'."\n");
@@ -295 +282 @@
           $Value['Comment'] = substr(trim($LineParts[1]), 3);
 
-          $Query = 'SELECT * FROM (SELECT * FROM `'.$Group['TablePrefix'].'` WHERE (`Complete` = 1) AND '.$this->WhereLang.' AND '.$this->WhereUsers.' AND (`ShortCut`="'.$Value['ShortCut'].'") AND (`VersionStart` <= '.$this->ClientVersion['BuildNumber'].') AND (`VersionEnd` >= '.$this->ClientVersion['BuildNumber'].')'.$this->OrderByUserList.') AS `T` GROUP BY `T`.`Entry`';
-          //echo($Query);
-          $DbResult2 = $this->Database->query($Query);
+          $DbResult2 = $this->Database->query($this->BuildQuery($Group));
           if($DbResult2->num_rows > 0)
           {