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

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

namespace PoEco.Net.DB
{
    internal class Stash
    {
        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()};;max pool size=200";
            conn = new MySqlConnection();
            conn.ConnectionString = myConnectionString;
            return conn;
        }

        public static List<int> EnumerateTabByUID(int uid, string type)
        {
            List<int> t = new List<int>();
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_tabs WHERE uid = '{uid}' AND type = '{type}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            MySqlDataReader DR = cmd.ExecuteReader();
            while (DR.Read())
            {
                t.Add(DR.GetInt32(1));
            }
            con.Close();
            return t;
        }

        public static int GetClassIDByBase(string ibase)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"SELECT * FROM poeco_bases WHERE name LIKE '%{MySqlHelper.EscapeString(ibase)}%'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            var row = cmd.ExecuteReader();
            if (row.HasRows)
            {
                row.Read();
                int user = row.GetInt32(1);
                con.Close();
                return user;
            }
            else
            {
                con.Close();
                return 0;
            }
        }

        public static string GetClassByID(int iclass)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_classes WHERE cid = '{iclass}'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();
                if (row.HasRows)
                {
                    row.Read();
                    string user = row.GetString(1);
                    con.Close();
                    return user;
                }
                else
                {
                    con.Close();
                    return null;
                }
            }

        }

        public static int GetBaseIDByName(string ibase)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_bases WHERE name LIKE '%{MySql.Data.MySqlClient.MySqlHelper.EscapeString(ibase)}%'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();
                if (row.HasRows) { row.Read(); return row.GetInt32(0); } else { return 0; }
            }
        }

        public static void AddItemTable(int uid, int ti, string uiid, int ilvl, int cid, int bid, string name, string mods, string img, string txt, string b64, double min, double max)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"INSERT INTO poeco_items(uid, tid, uiid, ilvl, cid, bid, name, mods, img, txt, b64, min, max) VALUES ('{uid}','{ti}','{uiid}','{ilvl}','{cid}','{bid}','{name}','{mods}','{img}','{txt}','{b64}','{min}','{max}')";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public static void AddProfitTable(int uid, string uiid, string type, string name, int stack, double value)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"INSERT INTO poeco_profits(uid, uiid, type, name, stack, cvalue) VALUES ('{uid}','{uiid}','{type}','{name}','{stack}','{value}')";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

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

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

        public static bool CheckItemExistsDB(string iid)
        {
            using (var con = MxPoEDB())
            {
                string cmdText = $"SELECT * FROM poeco_items WHERE uiid = '{iid}'";
                MySqlCommand cmd = new MySqlCommand(cmdText, con);
                con.Open();
                var row = cmd.ExecuteReader();
                return row.HasRows;
            }
        }

        public static void DeleteSpecificItem(int userid, string uuid)
        {
            MySqlConnection con = MxPoEDB();
            string cmdText = $"DELETE FROM poeco_items WHERE uid = '{userid}' AND uiid = '{uuid}'";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

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