PoE-PoEco.Net/PoEco.Net/DB/User.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PoEco.Net.DB
{
    internal class User
    {
        public static MySqlConnection MxPoEDB()
        {
            MySqlConnection conn;
            string myConnectionString;

            myConnectionString = $"server={JSON.Settings.GetdbHost()};port={JSON.Settings.GetdbPort()};uid={JSON.Settings.GetdbUser()};pwd={JSON.Settings.GetdbPass()};database={JSON.Settings.GetdbName()};";
            conn = new MySqlConnection();
            conn.ConnectionString = myConnectionString;
            return conn;
        }

        public static bool CheckUserExists(string user, string discriminator)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}|{discriminator}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { con.Close(); return true; } else { con.Close(); return false; }            
        }

        public static int GetUserID(string user)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();
                if (row.HasRows) { row.Read(); return Convert.ToInt32(row["uid"]); } else { return 0; }
            }
        }

        public static string[] GetUserNameByID(int uid)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_user WHERE uid = '{uid}'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();              
                if (row.HasRows) 
                {
                    row.Read();
                    string[] user = row["account"].ToString().Split('|');
                    con.Close(); 
                    return user; 
                } else 
                { 
                    con.Close(); 
                    return null; 
                }
            }
        }

        public static string GetUserTokenByID(int uid)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_user WHERE uid = '{uid}'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();
                if (row.HasRows)
                {
                    row.Read();
                    string user = row["token"].ToString();
                    con.Close();
                    return user;
                }
                else
                {
                    con.Close();
                    return null;
                }
            }
        }

        public static int GetUserTabIndex(string user)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); con.Close(); return Convert.ToInt32(row["rareindex"]); } else { con.Close(); return 0; }
        }

        public static int GetUserProfitTabIndex(int uid)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_tabs WHERE type = 'profit'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); return Convert.ToInt32(row["tid"]); } else { return 0; }
        }

        public static int GetUserCurrencyIndex(string user, string column)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); return Convert.ToInt32(row[column]); } else { return 0; }
        }

        public static int GetUserItemCount(string user)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); return Convert.ToInt32(row["itemcount"]); } else { return 0; }
        }

        public static string GetUserStashHash(string user)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); return row["stashb64"].ToString(); } else { return ""; }
        }

        public static string GetUserToken(string user)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_user WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows) { row.Read(); return row["token"].ToString(); } else { return ""; }
        }

        public static int GetTabValueByTID(int uid, int tid)
        {
            int tv = 0;
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_items WHERE uid = '{uid}' AND tid = '{tid}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            MySqlDataReader DR = cmd.ExecuteReader();
            while (DR.Read())
            {
                tv = tv + DR.GetInt32(12);
            }
            return tv;
        }

        public static int CountTabByUID(int uid)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT COUNT(*) FROM poeco_tabs WHERE uid = '{uid}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            int count = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return count;
        }

        public static void SetUserItemCount(int uid, int count, int index)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"UPDATE poeco_tabs SET count = '{count}' WHERE uid = '{uid}' AND tid = '{index}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void SetUserStashHash(string user, string b64)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"UPDATE poeco_user SET stashb64 = '{b64}' WHERE account = '{user}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void SetUserTabValue(int uid, int ti, double value)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"UPDATE poeco_tabs SET cvalue = '{value}' WHERE uid = '{uid}' AND tid = '{ti}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public static void DeleteProfits(int uid)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"DELETE FROM poeco_profits WHERE uid = '{uid}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}