l2j-server-game/L2_GameServer/java/com/l2jserver/accountmanager/SQLAccountManager.java

/*
 * 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/>.
 */
package com.l2jserver.accountmanager;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.l2jserver.Base64;
import com.l2jserver.Config;
import com.l2jserver.L2DatabaseFactory;
import com.l2jserver.Server;

import javolution.util.FastList;

/**
 * This class SQL Account Manager
 *
 * @author netimperia
 * @version $Revision: 2.3.2.1.2.3 $ $Date: 2005/08/08 22:47:12 $
 */
public class SQLAccountManager
{
    private static String _uname = "";
    private static String _pass = "";
    private static String _level = "";
    private static String _mode = "";
    
    public static void main(String[] args) throws SQLException, IOException, NoSuchAlgorithmException
    {
        Server.serverMode = Server.MODE_LOGINSERVER;
        Config.load();
        while (true)
		{
			System.out.println("Please choose an option:");
			System.out.println("");
			System.out.println("1 - Create new account or update existing one (change pass and access level).");
			System.out.println("2 - Change access level.");
			System.out.println("3 - Delete existing account.");
			System.out.println("4 - List accounts & access levels.");
			System.out.println("5 - Exit.");
			LineNumberReader _in = new LineNumberReader(new InputStreamReader(System.in));
			while (!(_mode.equals("1") || _mode.equals("2") || _mode.equals("3") || _mode.equals("4") || _mode.equals("5")))
			{
				System.out.print("Your choice: ");
				_mode = _in.readLine();
			}
			
			if (_mode.equals("1") || _mode.equals("2") || _mode.equals("3"))
			{
				if (_mode.equals("1") || _mode.equals("2"))
					while (_uname.length() == 0)
					{
						System.out.print("Username: ");
						_uname = _in.readLine().toLowerCase();
					}
				else if (_mode.equals("3"))
					while (_uname.length() == 0)
					{
						System.out.print("Account name: ");
						_uname = _in.readLine().toLowerCase();
					}
				
				if (_mode.equals("1"))
					while (_pass.length() == 0)
					{
						System.out.print("Password: ");
						_pass = _in.readLine();
					}
				
				if (_mode.equals("1") || _mode.equals("2"))
					while (_level.length() == 0)
					{
						System.out.print("Access level: ");
						_level = _in.readLine();
					}
				
			}
			
			if (_mode.equals("1"))
				// Add or Update
				addOrUpdateAccount(_uname, _pass, _level);
			else if (_mode.equals("2"))
				// Change Level
				changeAccountLevel(_uname, _level);
			else if (_mode.equals("3"))
			{
				// Delete
				System.out.print("Do you really want to delete this account ? Y/N : ");
				String yesno = _in.readLine();
				if (yesno.equalsIgnoreCase("Y"))
					deleteAccount(_uname);
				else
					System.out.println("Deletion cancelled");
				
			}
			else if (_mode.equals("4"))
			{
				// List
				_mode = "";
				System.out.println("");
				System.out.println("Please choose a listing mode:");
				System.out.println("");
				System.out.println("1 - Banned accounts only (accessLevel < 0)");
				System.out.println("2 - GM/privileged accounts (accessLevel > 0)");
				System.out.println("3 - Regular accounts only (accessLevel = 0)");
				System.out.println("4 - List all");
				while (!(_mode.equals("1") || _mode.equals("2") || _mode.equals("3") || _mode.equals("4")))
				{
					System.out.print("Your choice: ");
					_mode = _in.readLine();
				}
				System.out.println("");
				printAccInfo(_mode);
			}
			else if (_mode.equals("5"))
				System.exit(0);
			
			_mode = "";
			System.out.println();
		}
    }
    
    private static void printAccInfo(String m) throws SQLException
    {
        int count = 0;
        Connection con = null;
        con = L2DatabaseFactory.getInstance().getConnection();
        String q = "SELECT login, accessLevel FROM accounts ";
        if (m.equals("1"))
            q=q.concat("WHERE accessLevel<0");
        else if (m.equals("2"))
            q=q.concat("WHERE accessLevel>0");
        else if (m.equals("3"))
            q=q.concat("WHERE accessLevel=0");
        q=q.concat(" ORDER BY login ASC");

        PreparedStatement statement = con.prepareStatement(q);
        ResultSet rset = statement.executeQuery();
        while (rset.next())
        {
            System.out.println(rset.getString("login")	+ " -> " + rset.getInt("accessLevel"));
            count++;
        }
        rset.close();
        statement.close();
        con.close();
        System.out.println("Displayed accounts: " + count + ".");
    }
    
    private static void addOrUpdateAccount(String account,String password, String level) throws IOException, SQLException, NoSuchAlgorithmException
    {
        // Encode Password
        MessageDigest md = MessageDigest.getInstance("SHA");
        byte[] newpass;
        newpass = password.getBytes("UTF-8");
        newpass = md.digest(newpass);
        
        // Add to Base
        Connection con = null;
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement = con.prepareStatement("REPLACE	accounts (login, password, accessLevel) VALUES (?,?,?)");
        statement.setString(1, account);
        statement.setString(2, Base64.encodeBytes(newpass));
        statement.setString(3, level);
        statement.executeUpdate();
        statement.close();
        con.close();
    }
    
    private static void changeAccountLevel(String account, String level) throws SQLException
    {
        Connection con = null;
        con = L2DatabaseFactory.getInstance().getConnection();
        
        // Check Account Exist
        PreparedStatement statement = con.prepareStatement("SELECT COUNT(*) FROM accounts WHERE login=?;");
        statement.setString(1, account);
        ResultSet rset = statement.executeQuery();
        if(rset.next()==false) {
            System.out.println("False");
            
        } else if(rset.getInt(1)>0) {
            
            // Exist
            
            // Update
            statement = con.prepareStatement("UPDATE accounts SET accessLevel=? WHERE login=?;");
            statement.setEscapeProcessing(true);
            statement.setString(1, level);
            statement.setString(2, account);
            statement.executeUpdate();
            
            System.out.println("Account " + account + " has been updated.");
        } else {
            // Not Exist
            System.out.println("Account " + account + " does not exist.");
        }
        rset.close();
        statement.close();
        con.close();
    }
    
    private static void deleteAccount(String account) throws SQLException
    {
        Connection con = null;
        con = L2DatabaseFactory.getInstance().getConnection();
        
        // Check Account Exist
        PreparedStatement statement = con.prepareStatement("SELECT COUNT(*) FROM accounts WHERE login=?;");
        statement.setString(1, account);
        ResultSet rset = statement.executeQuery();
        if(rset.next()==false)
        {
            System.out.println("False");
            rset.close();
        }
        else if(rset.getInt(1)>0) 
        {
            rset.close();
            // Account exist
            
            // Get Accounts ID
            ResultSet rcln;
            statement = con.prepareStatement("SELECT charId, char_name, clanid FROM characters WHERE account_name=?;");
            statement.setEscapeProcessing(true);
            statement.setString(1, account);
            rset = statement.executeQuery();
            
            FastList<String> objIds = new FastList<String>();
            FastList<String> charNames = new FastList<String>();
            FastList<String> clanIds = new FastList<String>();
            
            while (rset.next())
            {
                objIds.add(rset.getString("charId"));
                charNames.add(rset.getString("char_name"));
                clanIds.add(rset.getString("clanid"));
            }
            rset.close();
            
            for (int index = 0; index < objIds.size(); index++)
            {
                System.out.println("Deleting character " + charNames.get(index) + ".");
                
                // Check If clan leader Remove Clan and remove all from it
                statement.close();
                statement = con.prepareStatement("SELECT COUNT(*) FROM clan_data WHERE leader_id=?;");
                statement.setString(1, clanIds.get(index));
                rcln = statement.executeQuery();
                rcln.next();
                if(rcln.getInt(1)>0) 
                {
                    rcln.close();
                    // Clan Leader
                    
                    // Get Clan Name
                    statement.close();
                    statement = con.prepareStatement("SELECT clan_name FROM clan_data WHERE leader_id=?;");
                    statement.setString(1, clanIds.get(index));
                    rcln = statement.executeQuery();
                    rcln.next();
                    
                    String clanName = rcln.getString("clan_name");
                    
                    System.out.println("Deleting clan " + rcln.getString("clan_name") + ".");
                    
                    // Delete Clan Wars
                    statement.close();
                    statement = con.prepareStatement("DELETE FROM clan_wars WHERE clan1=? OR clan2=?;");
                    statement.setEscapeProcessing(true);
                    statement.setString(1, clanName);
                    statement.setString(2, clanName);
                    statement.executeUpdate();
                    
                    rcln.close();
                    
                    // Remove All From clan
                    statement.close();
                    statement = con.prepareStatement("UPDATE characters SET clanid=0 WHERE clanid=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    // Free Clan Halls
                    statement.close();
                    statement = con.prepareStatement("UPDATE clanhall SET ownerId=0, paidUntil=0, paid=0 WHERE ownerId=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    // Delete Clan
                    statement.close();
                    statement = con.prepareStatement("DELETE FROM clan_data WHERE clan_id=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    //Clan privileges
                    statement.close();
                    statement = con.prepareStatement("DELETE FROM clan_privs WHERE clan_id=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    //Clan subpledges
                    statement.close();
                    statement = con.prepareStatement("DELETE FROM clan_subpledges WHERE clan_id=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    //Clan skills
                    statement.close();
                    statement = con.prepareStatement("DELETE FROM clan_skills WHERE clan_id=?;");
                    statement.setString(1, clanIds.get(index));
                    statement.executeUpdate();
                    
                    
                }
                else
                    rcln.close();
                
                // skills
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_skills WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // skills save
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_skills_save WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // subclasses
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_subclasses WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // shortcuts
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_shortcuts WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // items
                statement.close();
                statement = con.prepareStatement("DELETE FROM items WHERE owner_id=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // recipebook
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_recipebook WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // quests
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_quests WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // macroses
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_macroses WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // friends
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_friends WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // merchant_lease
                statement.close();
                statement = con.prepareStatement("DELETE FROM merchant_lease WHERE player_id=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // boxaccess
                statement.close();
                statement = con.prepareStatement("DELETE FROM boxaccess WHERE charname=?;");
                statement.setString(1, charNames.get(index));
                statement.executeUpdate();
                
                // hennas
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_hennas WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // recommends
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_recommends WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // ui categories
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_ui_categories WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // ui keys
                statement.close();
                statement = con.prepareStatement("DELETE FROM character_ui_keys WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // characters
                statement.close();
                statement = con.prepareStatement("DELETE FROM characters WHERE charId=?;");
                statement.setString(1, objIds.get(index));
                statement.executeUpdate();
                
                // TODO: delete pets, olympiad/noble/hero stuff
            }
            
            // Delete Account
            statement.close();
            statement = con.prepareStatement("DELETE FROM accounts WHERE login=?;");
            statement.setEscapeProcessing(true);
            statement.setString(1, account);
            statement.executeUpdate();
            
            System.out.println("Account " + account + " has been deleted.");
        } else {
            // Not Exist
            System.out.println("Account " + account + " does not exist.");
        }
        
        // Close Connection
        statement.close();
        con.close();
    }
    
}