Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using MySql.Data.MySqlClient;
- using System.Collections;
- using System.Linq;
- using System.Text;
- /*
- command =connection.CreateCommand();
- command.CommandText = "INSERT INTO log(userid,message,date) VALUES(1,'Hello there',NOW());";
- MySqlDataReader reader;
- reader = command.ExecuteReader();
- Console.WriteLine("Executed Query: " + reader);
- */
- namespace Lightchat_server
- {
- public class SQL
- {
- private MySqlConnection connection = new MySqlConnection();
- private MySqlDataAdapter data = new MySqlDataAdapter();
- private MySqlCommand command = new MySqlCommand();
- MySqlDataReader reader;
- public void connect(Boolean flag)
- {
- if (flag) { Console.WriteLine("Connecting to MySQL server @ localhost"); }
- //Set connection string
- connection.ConnectionString =
- "server=localhost;"
- + "database=lightmunnas;"
- + "uid=root;"
- + "password=redrun11;";
- //Connect
- connection.Open();
- if (flag) { Console.WriteLine(" >Connected"); }
- }
- public void disconnect()
- {
- try
- {
- connection.Dispose();
- connection.Close();
- }
- catch
- {
- }
- }
- public void userLogin(String userName) // on client connection
- {
- command = connection.CreateCommand();
- command.CommandText = "SELECT * FROM users WHERE (username = '" + userName + "');";
- reader = command.ExecuteReader();
- if (reader.Read() != false)//Is the client name already in the database?
- {
- reader.Close(); //close reader
- command.CommandText = "UPDATE users SET lastlogin=NOW() WHERE username ='" + userName + "';";
- command.ExecuteNonQuery();
- }
- else //add username to the database
- {
- reader.Close(); //close reader
- Console.WriteLine(" >Clients first time connecting, adding to database");
- command.CommandText = "INSERT INTO users(username,lastlogin,sent) VALUES('" + userName + "',NOW(),0);";
- command.ExecuteNonQuery();
- }
- }
- public void createChannel(String name, String password)
- {
- reader.Close();
- command = connection.CreateCommand();
- command.CommandText = "INSERT INTO channels(channelname,channelpassword) VALUES('" + name + "','" + password + "');";
- command.ExecuteNonQuery();
- }
- public int[] getChannelsByUser(String userName)
- {
- command = connection.CreateCommand();
- //Get user id
- command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString();
- reader.Close();
- //get number of channels
- command.CommandText = "SELECT count(*) FROM channeluser WHERE userid='" + id + "';";
- reader = command.ExecuteReader();
- reader.Read();
- int count = (int)Convert.ToInt32(reader.GetValue(0));
- reader.Close();
- //get channels by ID
- command.CommandText = "SELECT channelid FROM channeluser WHERE userid='" + id + "';";
- reader = command.ExecuteReader();
- int[] channels = new int[count];
- int loopCount = 0;
- while (reader.Read())
- {
- channels[loopCount] = (int)reader["channelid"];
- loopCount++;
- }
- reader.Close();
- return (channels);
- }
- public int joinChannel(String userName, String channelName, String channelPassword)
- {
- reader.Close();
- command = connection.CreateCommand();
- //Get user id
- command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString();
- reader.Close();
- //Get channel id
- command.CommandText = "SELECT id FROM channels WHERE channelname='" + channelName + "';";
- reader = command.ExecuteReader();
- //check if id exists
- String channelid = "-1";
- if (reader.Read())
- {
- channelid = reader.GetValue(0).ToString();
- }
- reader.Close();
- //Get channel password
- command.CommandText = "SELECT channelpassword FROM channels WHERE channelname='" + channelName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String channelpass = "¤";
- if (reader.Read())
- {
- channelpass = reader.GetValue(0).ToString();
- }
- reader.Close();
- //check if user is already in the channel
- command.CommandText = "SELECT * FROM channeluser WHERE (channelid='" + channelid + "') AND (userid='" + id + "');";
- reader = command.ExecuteReader();
- if (!reader.Read())
- {
- reader.Close();
- if (channelpass == channelPassword)
- {
- //Get user id
- command.CommandText = "INSERT INTO channeluser(channelid,userid) VALUES(" + channelid + "," + id + ");";
- command.ExecuteNonQuery();
- return (0);
- }
- else
- {
- return (1);
- }
- }
- reader.Close();
- return (2);
- }
- public int leaveChannel(String userName, String channelIndex)
- {
- int result = 0;
- reader.Close();
- command = connection.CreateCommand();
- //Get user id
- command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString();
- reader.Close();
- try
- {
- //leave channel
- command.CommandText = "DELETE FROM channeluser WHERE (channelid='" + channelIndex + "') AND (userid='" + id + "');";
- command.ExecuteNonQuery();
- result = 0;
- }
- catch
- {
- result = 1;
- }
- return result;
- }
- public String[] getChannelList()
- {
- String[] channels;
- //get number of channels
- command.CommandText = "SELECT count(*) FROM channels;";
- reader = command.ExecuteReader();
- reader.Read();
- int count = (int)Convert.ToInt32(reader.GetValue(0));
- reader.Close();
- channels = new String[count];
- int loopCount = 0;
- command.CommandText = "SELECT channelname FROM channels;";
- reader = command.ExecuteReader();
- while (reader.Read())
- {
- channels[loopCount] = (string)reader["channelname"];
- loopCount++;
- }
- return channels;
- }
- public String getUserFromID(String userid)
- {
- command = connection.CreateCommand();
- command.CommandText = "SELECT username FROM users WHERE id='" + userid + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String name = reader.GetValue(0).ToString(); // get name
- reader.Close();
- return name;
- }
- public String getIDFromUser(String username)
- {
- command = connection.CreateCommand();
- command.CommandText = "SELECT id FROM users WHERE username='" + username + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString(); // get name
- reader.Close();
- return id;
- }
- public void newMessage(String userName, String msg)
- {
- command = connection.CreateCommand();
- command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString(); // get id
- reader.Close();
- command.CommandText = "INSERT INTO log(userid,message,date) VALUES(" + id + ",'" + msg + "',NOW());";
- command.ExecuteNonQuery();
- command.CommandText = "UPDATE users SET sent = sent + 1 WHERE id=" + id + ";";
- command.ExecuteNonQuery();
- }
- public string getLog(String input)
- {
- try
- {
- String[] arguments = input.Split('-');
- String users = null;
- bool usersb = false;
- String date = null;
- bool dateb = false;
- foreach (string argument in arguments)
- {
- if (argument.Substring(0, 1) == "u")
- {
- String[] s;
- String noArg = argument.Substring(argument.IndexOf('u') + 1);
- if (argument.Contains(','))
- {
- s = noArg.Split(',');
- users = "users.username='" + s[0].Trim() + "'";
- for (int i = 1; i < s.Length; i++)
- {
- users += " OR users.username='" + s[i].Trim() + "'";
- }
- }
- else
- {
- users = "users.username='" + noArg.Trim() + "'";
- }
- usersb = true;
- }
- else if (argument.Substring(0, 1) == "d")
- {
- String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
- dateb = true;
- if (argument.Contains(','))
- {
- String[] d;
- int argCount = 0;
- d = noArg.Split(',');
- foreach (String dt in d)
- {
- if (dt.Contains('*'))
- {
- String[] dtsplit = dt.Split('*');
- foreach (String dts in dtsplit)
- {
- dts.Trim();
- }
- if (argCount == 0)
- {
- date += "log.date BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
- argCount++;
- }
- else
- {
- date += "OR log.date BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
- }
- }
- else
- {
- if (argCount == 0)
- {
- date += "log.date BETWEEN CAST '" + dt + "00' DATETIME AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
- argCount++;
- }
- else
- {
- date += " OR log.date BETWEEN '" + dt + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
- }
- }
- }
- }
- else
- {
- int argCount = 0;
- String d = noArg;
- if (d.Contains('*'))
- {
- String[] dtsplit = d.Split('*');
- foreach (String dts in dtsplit)
- {
- dts.Trim();
- }
- if (argCount == 0)
- {
- date += "log.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
- argCount++;
- }
- else
- {
- date += "OR log.date BETWEEN '" + dtsplit[0] + "00' AND '20" + dtsplit[1] + "'";
- }
- }
- else
- {
- if (argCount == 0)
- {
- date += "log.date BETWEEN '" + d + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
- argCount++;
- }
- else
- {
- date += " OR log.date BETWEEN '" + d + "'00 AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
- }
- }
- }
- }
- }
- String args = "SELECT users.username, log.message, log.date FROM log INNER JOIN users ON users.id=log.userid WHERE ";
- if (!usersb && dateb)
- {
- args += date;
- }
- else if (usersb && !dateb)
- {
- args += users;
- }
- else
- {
- args += users + " AND " + date;
- }
- args += " ORDER BY log.date ASC;";
- String data = null;
- Console.WriteLine(args);
- Console.WriteLine();
- command = connection.CreateCommand();
- command.CommandText = args;
- reader = command.ExecuteReader();
- while (reader.Read())
- {
- data += "(" + reader["date"].ToString() + ") <" + reader["username"].ToString() + "> " + reader["message"].ToString() + Environment.NewLine;
- }
- reader.Close();
- return (data);
- }
- catch (Exception ex)
- {
- Console.WriteLine(" >/log ERROR: " + ex.Message);
- return (null);
- }
- }
- public string getFileLog(String input)
- {
- try
- {
- String[] arguments = input.Split('-');
- String senders = null;
- bool sendersb = false;
- String receivers = null;
- bool receiversb = false;
- String date = null;
- bool dateb = false;
- String fileinfo = null;
- bool fileinfob = false;
- foreach (string argument in arguments)
- {
- if (argument.Substring(0, 1) == "s")
- {
- String[] s;
- String noArg = argument.Substring(argument.IndexOf('s') + 1);
- if (argument.Contains(','))
- {
- s = noArg.Split(',');
- senders = "users.username='" + s[0].Trim() + "'";
- for (int i = 1; i < s.Length; i++)
- {
- senders += " OR users.username='" + s[i].Trim() + "'";
- }
- }
- else
- {
- senders = "users.username='" + noArg.Trim() + "'";
- }
- sendersb = true;
- }
- else if (argument.Substring(0, 1) == "r")
- {
- String[] r;
- String noArg = argument.Substring(argument.IndexOf('r') + 1);
- if (argument.Contains(','))
- {
- r = noArg.Split(',');
- receivers = "filetransfers.receiverID='" + getIDFromUser(r[0].Trim()) + "'";
- for (int i = 1; i < r.Length; i++)
- {
- receivers += " OR filetransfers.receiverID='" + getIDFromUser(r[i].Trim()) + "'";
- }
- }
- else
- {
- receivers = "filetransfers.receiverID='" + getIDFromUser(noArg.Trim()) + "'";
- }
- receiversb = true;
- }
- else if (argument.Substring(0, 1) == "f")
- {
- String[] f;
- String noArg = argument.Substring(argument.IndexOf('f') + 1);
- if (argument.Contains(','))
- {
- f = noArg.Split(',');
- fileinfo = "filetransfers.filename='" + f[0].Trim() + "'";
- for (int i = 1; i < f.Length; i++)
- {
- fileinfo += " OR filetransfers.filename='" + f[i].Trim() + "'";
- }
- }
- else
- {
- fileinfo = "filetransfers.filename='" + noArg.Trim() + "'";
- }
- fileinfob = true;
- }
- else if (argument.Substring(0, 1) == "d")
- {
- String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
- dateb = true;
- if (argument.Contains(','))
- {
- String[] d;
- int argCount = 0;
- d = noArg.Split(',');
- foreach (String dt in d)
- {
- if (dt.Contains('*'))
- {
- String[] dtsplit = dt.Split('*');
- foreach (String dts in dtsplit)
- {
- dts.Trim();
- }
- if (argCount == 0)
- {
- date += "filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
- argCount++;
- }
- else
- {
- date += "OR filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
- }
- }
- else
- {
- if (argCount == 0)
- {
- date += "filetransfers.time BETWEEN CAST '" + dt + "00' DATETIME AND DATE_ADD(CAST('" + dt + "00' AS datetime), INTERVAL +5 MINUTE)";
- argCount++;
- }
- else
- {
- date += " OR filetransfers.time BETWEEN '" + dt + "00' AND DATE_ADD(CAST('" + dt + "00' AS datetime), INTERVAL +5 MINUTE)";
- }
- }
- }
- }
- else
- {
- }
- }
- }
- String args = "SELECT users.username, filetransfers.filename, filetransfers.receiverID, filetransfers.size, filetransfers.time FROM filetransfers INNER JOIN users ON users.id=filetransfers.userid WHERE ";
- int argcount = 0;
- if (sendersb)
- {
- if (argcount == 0)
- {
- args += senders;
- argcount++;
- } else
- {
- args += " AND " + senders;
- }
- }
- if (dateb)
- {
- if (argcount == 0)
- {
- args += date;
- argcount++;
- } else
- {
- args += " AND " + date;
- }
- }
- if (receiversb)
- {
- if (argcount == 0)
- {
- args += receivers;
- argcount++;
- } else
- {
- args += " AND " + receivers;
- }
- }
- if (fileinfob)
- {
- if (argcount == 0)
- {
- args += fileinfo;
- argcount++;
- } else
- {
- args += " AND " + fileinfo;
- }
- }
- args += " ORDER BY filetransfers.time ASC;";
- String data = null;
- Console.WriteLine(args);
- Console.WriteLine();
- command = connection.CreateCommand();
- command.CommandText = args;
- reader = command.ExecuteReader();
- while (reader.Read())
- {
- data += "(" + reader["time"].ToString() + ") " + reader["username"].ToString() + " sent " + reader["filename"].ToString() + " which is " + reader["size"].ToString() + " bytes to " getUserFromID(reader["receiverID"].ToString()) + "." + Environment.NewLine;
- }
- reader.Close();
- return (data);
- }
- catch (Exception ex)
- {
- Console.WriteLine(" >/log ERROR: " + ex.Message);
- return (null);
- }
- }
- public void fileTransfer(String userName,String toUser, String fileName, int fileSize)
- {
- command = connection.CreateCommand();
- command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
- reader = command.ExecuteReader();
- reader.Read();
- String id = reader.GetValue(0).ToString(); // get id
- reader.Close();
- command = connection.CreateCommand();
- command.CommandText = "INSERT INTO filetransfers(userid,size,filename,time) VALUES('" + id + "','" + fileSize + "','" + fileName + "',NOW());";
- command.ExecuteNonQuery();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement