[SCRIPT] Очистка базы от старых персонажей

Тема в разделе "Веб скрипты (WEB)", создана пользователем root, 1 окт 2014.

  1. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    254
    Симпатии:
    58
    Баллы:
    11
    Удаляем аккаунты, на которые вообще не заходили после регистрации, на которые не заходили более 3-х месяцев.
    Код:
    <?php  $con = mssql_connect(bleh,bleh,bleh); 
    
    
     if ($con) 
     {      
        $db  = mssql_select_db (bleh); 
         
        $dTime = date("Y-m-d H:i:s.000", strtotime('-60 days')); 
         
        $query = " 
                 SELECT 
                    * 
                 FROM 
                    [user_account] 
                 WHERE 
                    (user_account.last_login < '".$dTime."') 
                 "; 
           
        $result = mssql_query($query); 
         
        $j=0; 
         
        while ($row = mssql_fetch_array($result)) 
        { 
          $user[$j]['account'] = $row['account']; 
          $user[$j]['uid'] = $row['uid']; 
           
          // get the ssn 
          $query = " 
                   SELECT 
                      ssn 
                   FROM 
                      [user_info] 
                   WHERE 
                      (user_info.account = '".$row['account']."') 
                   "; 
          $ssn = mssql_query($query);          
          $ssnval = mssql_fetch_assoc($ssn); 
           
          $user[$j]['ssn'] = $ssnval['ssn']; 
           
          // ssn 
          $query = " 
                   DELETE FROM 
                      [ssn] 
                   WHERE 
                      (ssn.ssn='".$ssnval['ssn']."') 
                   "; 
    
    
          mssql_query($query); 
           
          // user_account   
          $query = " 
                    DELETE FROM 
                       [user_account] 
                    WHERE 
                       (user_account.account='".$row['account']."') 
                    "; 
                     
          mssql_query($query); 
           
          // user_account   
          $query = " 
                    DELETE FROM 
                       [user_auth] 
                    WHERE 
                       (user_auth.account='".$row['account']."') 
                    "; 
                     
          mssql_query($query); 
           
           
          // user_info   
          $query = " 
                    DELETE FROM 
                       [user_info] 
                    WHERE 
                       (user_info.account='".$row['account']."') 
                    "; 
                     
          mssql_query($query); 
           
          // user_time   
          $query = " 
                    DELETE FROM 
                       [user_time] 
                    WHERE 
                       (user_time.account='".$row['account']."') 
                    "; 
                     
          mssql_query($query); 
           
          echo "Deleted Account: ".$row['account']."\r\n"; 
              
          $j++;     
        } 
         
        $db  = mssql_select_db (bleh); 
         
        $c=0; 
         
        for ($i=0; $i<count($user); $i++) 
        { 
           echo "Character Maitenance Starting...\r\n"; 
                
           // get list of characters  
           $query = " 
                    SELECT 
                       * 
                    FROM 
                       [user_data] 
                    WHERE 
                       (user_data.account_id=".$user[$i]['uid'].") 
                    "; 
                     
           $result = mssql_query($query); 
            
           while ($row = mssql_fetch_array($result)) 
           { 
              $character[$c] = $row; 
               
              echo "\tStarting Maitenance on character ".$row['char_name']."\r\n"; 
                
              // user_data 
              $query = " 
                       DELETE FROM 
                          [user_data] 
                       WHERE 
                          (user_data.account_id=".$user[$i]['uid'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // time_data 
              $query = " 
                       DELETE FROM 
                          [time_data] 
                       WHERE 
                          (time_data.account_id=".$user[$i]['uid'].") 
                       "; 
    
    
              mssql_query($query); 
               
               
              // user_deleted 
              $query = " 
                       DELETE FROM 
                          [user_deleted] 
                       WHERE 
                          (user_deleted.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_ActiveSkill 
              $query = " 
                       DELETE FROM 
                          [user_ActiveSkill] 
                       WHERE 
                          (user_ActiveSkill.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_blocklist 
              $query = " 
                       DELETE FROM 
                          [user_blocklist] 
                       WHERE 
                          (user_blocklist.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_comment 
              $query = " 
                       DELETE FROM 
                          [user_comment] 
                       WHERE 
                          (user_comment.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_friend 
              $query = " 
                       DELETE FROM 
                          [user_friend] 
                       WHERE 
                          (user_friend.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_history 
              $query = " 
                       DELETE FROM 
                          [user_history] 
                       WHERE 
                          (user_history.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // remove any pets 
              $query = " 
                       SELECT 
                          * 
                       FROM 
                          [user_item] 
                       WHERE 
                          (user_item.char_id=".$row['char_id'].") 
                       AND 
                          (user_item.warehouse < 2) 
                       "; 
                        
              $item_result = mssql_query($query); 
               
              while ($item = mssql_fetch_array($item_result)) 
              { 
                  // user_item 
                  $query = " 
                           DELETE FROM 
                              [pet_data] 
                           WHERE 
                              (pet_data.pet_id=".$item['item_id'].") 
                           "; 
    
    
                  mssql_query($query);               
              } 
               
              // user_item 
              $query = " 
                       DELETE FROM 
                          [user_item] 
                       WHERE 
                          (user_item.char_id=".$row['char_id'].") 
                       AND 
                          (user_item.warehouse < 2) 
                       "; 
    
    
              mssql_query($query); 
               
              // user_punish 
              $query = " 
                       DELETE FROM 
                          [user_punish] 
                       WHERE 
                          (user_punish.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_skill 
              $query = " 
                       DELETE FROM 
                          [user_skill] 
                       WHERE 
                          (user_skill.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_surrender 
              $query = " 
                       DELETE FROM 
                          [user_surrender] 
                       WHERE 
                          (user_surrender.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // user_warehouse 
              $query = " 
                       DELETE FROM 
                          [user_warehouse] 
                       WHERE 
                          (user_warehouse.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // event_items 
              $query = " 
                       DELETE FROM 
                          [event_items] 
                       WHERE 
                          (event_items.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // Quest 
              $query = " 
                       DELETE FROM 
                          [Quest] 
                       WHERE 
                          (Quest.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // shortcut_data 
              $query = " 
                       DELETE FROM 
                          [shortcut_data] 
                       WHERE 
                          (shortcut_data.char_id=".$row['char_id'].") 
                       "; 
    
    
              mssql_query($query); 
               
              // clan & alliance cleanup 
              $query = " 
                       SELECT 
                          * 
                       FROM 
                          [Pledge] 
                       WHERE 
                          (ruler_id=".$row['char_id'].") 
                       "; 
                        
              $pledge_result = mssql_query($query); 
               
              while ($pledge = mssql_fetch_array($pledge_result)) 
              { 
                  // Pledge 
                  $query = " 
                           DELETE FROM 
                              [Pledge] 
                           WHERE 
                              (Pledge.ruler_id=".$row['char_id'].") 
                           "; 
    
    
                  mssql_query($query); 
                   
                  // Pledge_Crest 
                  $query = " 
                           DELETE FROM 
                              [Pledge_Crest] 
                           WHERE 
                              (Pledge_Crest.crest_id=".$pledge['crest_id'].") 
                           "; 
    
    
                  mssql_query($query); 
                   
                  // Pledge_War 
                  $query = " 
                           DELETE FROM 
                              [Pledge_War] 
                           WHERE 
                              (Pledge_War.challenger=".$pledge['pledge_id'].") OR 
                              (Pledge_War.challengee=".$pledge['pledge_id'].") 
                           "; 
    
    
                  mssql_query($query); 
                   
                  // user_item (clan warehouse) 
                  $query = " 
                           DELETE FROM 
                              [user_item] 
                           WHERE 
                              (user_item.char_id=".$pledge['pledge_id'].") 
                           AND 
                              (user_item.warehouse = 2) 
                           "; 
    
    
                  mssql_query($query); 
                   
                  // get alliance info if it exists 
                  $query = " 
                           SELECT 
                              * 
                           FROM 
                              [Alliance] 
                           WHERE 
                              (Alliance.master_pledge_id=".$pledge['pledge_id'].") 
                           "; 
                            
                  $alliance_result = mssql_query($query); 
                  $alliance = mssql_fetch_assoc($alliance_result); 
                   
                  if ($alliance['id']) 
                  { 
                      // Alliance 
                      $query = " 
                               DELETE FROM 
                                  [Alliance] 
                               WHERE 
                                  (Alliance.master_pledge_id=".$pledge['pledge_id'].") 
                               "; 
    
    
                      mssql_query($query); 
                       
                      $query = " 
                               UPDATE 
                                  [Pledge] 
                               SET 
                                  Pledge.alliance_id=0 
                               WHERE 
                                  (Pledge.alliance_id=".$alliance['id'].") 
                               "; 
                                
                       mssql_query($query);               
                  }               
                   
                  // user_data 
                  $query = " 
                           UPDATE 
                              [user_data] 
                           SET 
                              user_data.pledge_id=0 
                           WHERE 
                              (user_data.pledge_id=".$pledge['pledge_id'].") 
                           "; 
    
    
                  mssql_query($query);                             
              } 
               
              echo "\t Character maitenance finished for character ".$row['char_name']."\r\n"; 
               
              $c++; 
           }     
        } 
         
     } 
      
     $logdata = print_r($user, true).print_r($character, true); 
     file_put_contents("lineage_account_maitenance-".time().".log", $logdata);    
    ?>