Changeset 401 for trunk/export


Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/export/export.php

    r395 r401  
    2424    $DbResult = $this->Database->query('SELECT * FROM `Export` WHERE `Id`='.$this->Id);
    2525    if($DbResult->num_rows == 0) throw new Exception('Export '.$this->Id.' neexistuje');
    26     $this->Export = $DbResult->fetch_assoc();
    27    
    28     // Filter selected languages
    29     $this->WhereLang = '';
    30     $DbResult = $this->Database->query('SELECT * FROM `ExportLanguage` WHERE `Export`='.$this->Id.' ORDER BY `Sequence`');
    31     echo($this->Database->error);
    32     while($Language = $DbResult->fetch_assoc())
    33       $this->WhereLang .= ' OR (`Language` = '.$Language['Language'].')';
    34     if($this->WhereLang == '') $this->WhereLang = 1;
    35       else $this->WhereLang = '('.substr($this->WhereLang, 4).')';
     26    $this->Export = $DbResult->fetch_assoc();   
    3627
    3728    // Filter selected users
    38     $this->SelectedUsers = ''; 
    3929    $this->UserNames = '';
    4030    $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`');
    4131    while($UserLine = $DbResult->fetch_assoc())
    4232    {
    43       $this->SelectedUsers .= ','.$UserLine['ID'];
    44       $this->UserNames .= ','.$UserLine['Name'];
    45     }
    46     $this->SelectedUsers = substr($this->SelectedUsers, 1);
    47     $this->UserNames = substr($this->UserNames, 1);     
     33      $this->UserNames .= ', '.$UserLine['Name'];
     34    }
     35    $this->UserNames = substr($this->UserNames, 2);     
    4836   
    49     if($this->SelectedUsers == '')
    50     {
    51       $this->WhereUsers = 0;
    52       $this->OrderByUserList = '';
    53     } else
    54     {
    55       $this->WhereUsers = '(`User` IN ('.$this->SelectedUsers.'))';
    56       $this->OrderByUserList = ' ORDER BY FIELD(`User`, '.$this->SelectedUsers.')';
    57     }
    58    
    5937    $DbResult = $this->Database->query('SELECT * FROM `ClientVersion` WHERE `Id`='.$this->Export['ClientVersion']);
    6038    $this->ClientVersion = $DbResult->fetch_assoc();   
     39  }
     40 
     41  function BuildQuery($Group, $Columns = '`T`.*')
     42  {
     43    $Query = 'SELECT * FROM (SELECT '.$Columns.', `User`.`Name` AS `UserName` FROM `'.$Group['TablePrefix'].'` AS `T`'.
     44    ' JOIN `ExportUser` ON (`ExportUser`.`User`=`T`.`User`) AND (`ExportUser`.`Export`='.$this->Id.') '.
     45    ' JOIN `User` ON `User`.`ID`=`T`.`User`'.
     46    ' JOIN `ExportLanguage` ON (`ExportLanguage`.`Export`='.$this->Id.')'.
     47    ' WHERE (`Complete` = 1) AND (`VersionStart` <= '.$this->ClientVersion['BuildNumber'].') AND (`VersionEnd` >= '.$this->ClientVersion['BuildNumber'].')'.
     48    ' ORDER BY `ExportLanguage`.`Sequence`, `ExportUser`.`Sequence`) AS `TT` GROUP BY `TT`.`Entry`'; 
     49    return($Query);
    6150  }
    6251
    … …  
    9180      if($Group['MangosTable'] != '')
    9281      {
    93         $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`'; 
    94         //echo($Query);
    9582        $Buffer .= "\n\n-- ".$Group['Name']."\n\n";
    96         $DbResult2 = $this->Database->query($Query);
     83        $DbResult2 = $this->Database->query($this->BuildQuery($Group));
    9784        while($Line = $DbResult2->fetch_array())
    9885        {
    … …  
    134121    $Buffer = $this->ExportToMangosSQL();
    135122 
     123    /*
    136124    // Data to aowow
    137 
    138125    $Database2 = new mysqli($this->Config['Database']['Host'], $this->Config['Database']['User'], $this->Config['Database']['Password'], $this->Config['Database']['Database']);
    139126    $Database2->query('SET NAMES '.$this->Config['Database']['Charset']);
    … …  
    176163      $Buffer .= "\n\n"; 
    177164    }
     165    */
    178166    if($this->Export['WithDiacritic'] != 1) $Buffer = utf2ascii($Buffer);
    179167    return($Buffer);
    … …  
    210198        $Group['MangosTableIndex'] = 'entry';
    211199      }
    212       $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`';
    213       //echo($Query);
    214200      $Buffer .= '    <group id="'.$Group['Id'].'" name="'.$Group['TablePrefix'].'">'."\n";
    215       $DbResult2 = $this->Database->query($Query);
     201      $DbResult2 = $this->Database->query($this->BuildQuery($Group));
    216202      while($Line = $DbResult2->fetch_assoc())
    217203      {
    … …  
    247233      if(file_exists('../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc'))
    248234      {
    249         $i = 1;
    250         $columns = '';
     235        $I = 1;
     236        $Columns = '';
    251237        foreach($TranslationTree[$Group['Id']]['Items'] as $Column)
    252238        {
    253           $columns .= ' `T`.`'.$Column['Column'].'` AS `Text'.$i.'`,';
    254           $i = $i + 1;
     239          $Columns .= '`T`.`'.$Column['Column'].'` AS `Text'.$I.'`, ';
     240          $I = $I + 1;
    255241        }
    256         $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`';
     242        $Columns = substr($Columns, 0, -2);
     243
     244        $Query = $this->BuildQuery($Group, $Columns);
    257245        $Query = str_replace('`', '\`', $Query);
    258246        $Query = '"'.str_replace(' ', '\ ', $Query).'"';
    259247     
    260         $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;
     248        $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;
    261249     
    262         //echo('./DBCExport '.$params);       
    263         exec('./DBCExport '.$params);
     250        exec('./DBCExport '.$Params);
    264251       
    265252      } else echo('Zdrojový soubor '.'../source/'.$this->ClientVersion['Version'].'/dbc/'.$Group['DBCFileName'].'.dbc'.' nenalezen.'."\n");
    … …  
    295282          $Value['Comment'] = substr(trim($LineParts[1]), 3);
    296283
    297           $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`';
    298           //echo($Query);
    299           $DbResult2 = $this->Database->query($Query);
     284          $DbResult2 = $this->Database->query($this->BuildQuery($Group));
    300285          if($DbResult2->num_rows > 0)
    301286          {
Note: See TracChangeset for help on using the changeset viewer.