public static Subheader Login(ref UserInformation userInfo, ref ServerUserInformation serverUser)
{
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand("SELECT username, password, user_id, user_role FROM Users " +
"WHERE username ='******' AND password = '******';", con);
MySqlDataReader rdr = cmd.ExecuteReader();
if (!rdr.Read())
{
return(Subheader.INVALIDUSR);
}
serverUser.username = (string)rdr["username"];
serverUser.password = (string)rdr["password"];
serverUser.user_id = (string)rdr["user_id"];
serverUser.role = (AccountType)Enum.Parse(typeof(AccountType), (string)rdr["user_role"]);
userInfo.user_id = serverUser.user_id;
Console.WriteLine($"{serverUser.username}, {serverUser.password}, {serverUser.user_id}, {Enum.GetName(typeof(AccountType), serverUser.role)}");
return(Subheader.NULL);
}
catch (MySqlException e)
{
Console.WriteLine(e.Message);
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ChangePassword(ref UserInformation userInfo, ref ServerUserInformation serverUser)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE Users SET password = '******' WHERE user_id = '" + userInfo.user_id + "' AND username = '******';", con);
}
catch (Exception) { return(Subheader.DBCON_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBCHG_ERROR);
}
serverUser.username = userInfo.username;
serverUser.password = userInfo.newpassword;
userInfo.password = userInfo.newpassword;
userInfo.newpassword = "";
serverUser.user_id = userInfo.user_id;
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader CreateProject(ServerUserInformation serverUser, ProjectInformation projInfo)
{
MySqlCommand cmd;
try
{
string query = "INSERT INTO Projects (project_id, project_name) VALUES ('" + projInfo.project_id + "','" + projInfo.project_name + "'); ";
foreach (ProjectUser user in projInfo)
{
query += "INSERT INTO Project_Users (project_users_id,project_id,user_id) VALUES ('" + projInfo.project_user_id + "','" + projInfo.project_id + "','" + user.user_id + "'); ";
}
cmd = new MySqlCommand(query, con);
}
catch { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader UpdateFriendRequest(ServerUserInformation serverUser, FriendInformation friendInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE Friends SET friend_status = '" + Enum.GetName(typeof(FriendStatus), friendInfo.friend_status) +
"' WHERE friend_id = '" + friendInfo.friend_id + "';", con);
}
catch { return(Subheader.DBCON_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBREG_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader RemoveProject(ServerUserInformation serverUser, ProjectInformation projInfo)
{
if (!ProjectAuth(serverUser, projInfo, ProjectRole.Owner))
{
return(Subheader.UNAUTH_ERROR);
}
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("DELETE FROM Files WHERE project_id = '" + projInfo.project_id + "'; " +
"DELETE FROM Project_Users WHERE project_id = '" + projInfo.project_id + "'; "
+ "DELETE FROM Projects WHERE project_id = '" + projInfo.project_id + " ';", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader AddProjectUser(ServerUserInformation serverUser, UserInformation userInfo, ProjectInformation projInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO Project_Users (project_users_id,project_id,user_id) VALUES ('" + projInfo + "','" + projInfo.project_id + "','" + userInfo.user_id + "'); ", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader UpdateFile(ServerUserInformation serverUser, ProjectFileInformation fileInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE Files SET file_data = @file_data, file_name = '" + fileInfo.file_name + "' WHERE file_id = '" + fileInfo.file_id + "';", con);
cmd.Parameters.Add("@file_data", MySqlDbType.Binary, fileInfo.file_data.Length).Value = fileInfo.file_data;
}
catch { return(Subheader.DBCON_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBREG_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader CreateFile(ServerUserInformation serverUser, ProjectInformation projInfo, ProjectFileInformation fileInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO Users (file_id, file_data, file_name, project_id) " +
"VALUES ('" + projInfo.project_id + "',@file_data,'" + fileInfo.file_name +
"', '" + fileInfo.project_id + "');", con);
cmd.Parameters.Add("@file_data", MySqlDbType.Binary, fileInfo.file_data.Length).Value = fileInfo.file_data;
}
catch { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBREG_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ViewFiles(ServerUserInformation serverUser, UserInformation userInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("SELECT file_id, file_name FROM Files WHERE project_id = " +
"(SELECT project_id FROM Project_Users WHERE user_id = '" + userInfo.user_id + "' )", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader AddFriend(ServerUserInformation serverUser, UserInformation userInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO Friends (friend_id, requested_user_id, received_user_id) " +
"VALUES ('" + Guid.NewGuid() + "','" + serverUser.user_id + "','" + userInfo.user_id + "'); ", con);
}
catch { return(Subheader.FRIEND_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ViewFriends(ServerUserInformation serverUser)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("SELECT friend_id FROM Friends WHERE requested_user_id ='" +
serverUser.user_id + "' OR received_user_id = '" + serverUser.user_id + "';", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ViewProjectFiles(ServerUserInformation serverUser, ProjectInformation projInfo)
{
MySqlCommand cmd;
MySqlDataReader rdr;
try
{
cmd = new MySqlCommand("SELECT file_id, file_name FROM Files WHERE project_id = '" + projInfo.project_id + "';", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
rdr = cmd.ExecuteReader();
if (rdr.FieldCount <= 0)
{
return(Subheader.DBQRY_ERROR);
}
projectList.Clear();
while (rdr.Read())
{
fileList.Add(new ProjectFileInformation(rdr["file_name"] + "", rdr["file_id"] + ""));
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader RemoveFriend(ServerUserInformation serverUser, FriendInformation friendInfo)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("DELETE FROM Friends WHERE friend_id = '" + friendInfo.friend_id + "';", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBQRY_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ChangeUserType(ServerUserInformation serverUser, UserInformation userInfo, AccountType type)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("UPDATE Users SET user_role = '" + Enum.GetName(typeof(AccountType), type) + "' WHERE user_id = '" + userInfo.user_id + "';", con);
}
catch (Exception) { return(Subheader.DBCON_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBCHG_ERROR);
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
private void UpdateClientInfo(ServerUserInformation usrInfo)
{
usrInfo.lastRequest = DateTime.Now;
int i = clients.FindIndex(usr => usr.socket == usrInfo.socket);
if (i > 0)
{
clients[i] = usrInfo;
}
}
public WMIServerUserInformation(int handle, ServerUserInformation userInfo)
{
try
{
this.userInfo = userInfo;
this.handle = handle;
Instrumentation.Publish(this);
}
catch (Exception e)
{
GXLogging.Error(log, "WMI Error", e);
}
}
private static bool ProjectAuth(ServerUserInformation serverUser, ProjectInformation projInfo, ProjectRole?role)
{
foreach (ProjectUser user in projInfo)
{
if (user.user_id == serverUser.user_id)
{
if (role == null || user.role == role)
{
return(true);
}
}
}
return(false);
}
public void BeginExecute(ICursor cursor, IGxConnection con)
{
if (con != null)
{
con.LastObject = this.name;
con.LastSQLStatementEnded = false;
ServerUserInformation sui = (ServerUserInformation)GxConnectionManager.Instance.GetUserInformation(con.DataStore.Handle, con.DataStore.Id);
if (sui != null)
{
sui.LastSQLStatement = cursor.SQLStatement;
sui.LastSQLStatementTime = DateTime.Now.ToString();
}
}
}
private void Application(Socket socket, Subheader sh, PacketReader pr)
{
ServerUserInformation sentUser = FindUser(pr.ReadObject <SessionInformation>());
switch (sh)
{
case Subheader.ADDUSR:
if (DatabaseQuery.AddFriend(sentUser, pr.ReadObject <UserInformation>()) != Subheader.NULL)
{
SendError(socket, sh, null);
}
break;
case Subheader.REMOVEUSR:
case Subheader.DEC_REQ:
if (DatabaseQuery.RemoveFriend(sentUser, pr.ReadObject <FriendInformation>()) != Subheader.NULL)
{
SendError(socket, sh, null);
}
break;
case Subheader.BLOCKUSR:
case Subheader.ACC_REQ:
if (DatabaseQuery.UpdateFriendRequest(sentUser, pr.ReadObject <FriendInformation>()) != Subheader.NULL)
{
SendError(socket, sh, null);
}
break;
case Subheader.VIEWFRIENDS:
if (DatabaseQuery.ViewFriends(sentUser) != Subheader.NULL)
{
SendError(socket, sh, null);
}
break;
case Subheader.PING:
SendHeaders(socket, Header.Application, sh);
break;
default: // Invalid Subheader
Dispose(socket);
Console.WriteLine("Application disposal: " + (int)sh);
//SendError(socket, Subheader.INVALIDREQ, null);
break;
}
}
private void Message(Socket socket, Subheader sh, PacketReader pr)
{
//Header h = Header.Message;
SessionInformation session = pr.ReadObject <SessionInformation>(); // Reads Session Information from User Identity
ServerUserInformation sUsers = FindUser(session); //
if (!UserExist(session))
{
SendError(socket, Subheader.SESSIONLOST, null);
return;
}
var users = UsernameList.Deserialize(ref pr);
switch (sh)
{
case Subheader.TEXTMSG: // (Header,Subheader,SessionInformation,UsernameList,String)
var text = pr.ReadString();
Console.WriteLine(text);
//Redirect(users, (user) => {RedirectPacket(h, sh, socket, sUsers.username, user, text); });
break;
/*
* case Subheader.IMGMSG: // (Header,Subheader,SessionInformation,UsernameList,Image)
* var img = pr.ReadImage();
* Redirect(users, (user) => {
* RedirectPacket(h, sh, socket, sUsers.username, user, img); });
* break;
*
* case Subheader.FILEMSG: // (Header,Subheader,SessionInformation,UsernameList,File)
* var file = pr.ReadObject<File>();
* Redirect(users, (user) => {
* RedirectPacket(h, sh, socket, sUsers.username, user, file);
* });
* break;
*/
default: // Invalid Subheader
//Console.Write((int)sh);
Dispose(socket);
Console.WriteLine("Message disposal");
SendError(socket, sh, (pw) => { pw.Write("server - Error with sending test message"); });
break;
}
}
private void Admin(Socket socket, Subheader sh, PacketReader pr)
{
ServerUserInformation sentUser = FindUser(pr.ReadObject <SessionInformation>());
switch (sh)
{
case Subheader.GETNUMCLIENTS:
Console.WriteLine(clients.Count + " clients");
break;
case Subheader.GETUPTIME:
Console.WriteLine(serverTimer.ElapsedMilliseconds + " uptime");
break;
default: // Invalid Subheader
Dispose(socket);
Console.WriteLine("Admin disposal");
//SendError(socket, Subheader.INVALIDREQ, null);
break;
}
}
public static Subheader Register(ref UserInformation userInfo, ref ServerUserInformation serverUser)
{
string username = userInfo.username;
string password = userInfo.password;
string guid = Guid.NewGuid().ToString();
AccountType role = AccountType.BASIC;
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("INSERT INTO Users (user_id, username, password, user_role) VALUES ('" + guid + "','" + username + "','" + password + "', '" + Enum.GetName(typeof(AccountType), role) + "');", con);
}
catch (Exception) { return(Subheader.DBCON_ERROR); }
try
{
if (cmd.ExecuteNonQuery() <= 0)
{
return(Subheader.DBREG_ERROR);
}
serverUser.username = username;
serverUser.password = password;
serverUser.user_id = guid;
serverUser.role = (AccountType)role;
userInfo.user_id = guid;
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static Subheader ViewProjects(ServerUserInformation serverUser, UserInformation userInfo)
{
MySqlCommand cmd;
MySqlDataReader rdr;
try
{
cmd = new MySqlCommand("SELECT project_id, project_name FROM Projects WHERE project_type = '" + Enum.GetName(typeof(ProjectType), ProjectType.PUBLIC)
+ "' project_id = (SELECT project_id FROM Project_Users WHERE Project_Users.user_id = '" + userInfo.user_id + "')", con);
}
catch (Exception) { return(Subheader.PROJ_ERROR); }
try
{
rdr = cmd.ExecuteReader();
if (rdr.FieldCount <= 0)
{
return(Subheader.DBQRY_ERROR);
}
projectList.Clear();
while (rdr.Read())
{
projectList.Add(new ProjectInformation(rdr["project_name"] + "", rdr["project_id"] + "", rdr["project_user_id"] + "", rdr["project_type"] + ""));
}
return(Subheader.NULL);
}
catch (MySqlException e)
{
if (e.Number == 2627)
{
return(Subheader.DBEXST_ERROR);
}
return(Subheader.DBQRY_ERROR);
}
}
public static AccountType UserAccountType(ServerUserInformation serverUser)
{
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("SELECT user_role FROM Users WHERE user_id = '" + serverUser.user_id + "';", con);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
return((AccountType)Enum.Parse(typeof(AccountType), (string)rdr["user_role"]));
}
else
{
return(AccountType.NULL);
}
}
catch
{
return((AccountType)1);
}
}
private void Account(Socket socket, Subheader sh, PacketReader pr)
{
ServerUserInformation usrInfo = new ServerUserInformation();
UserInformation info;
switch (sh)
{
case Subheader.LOGIN:
info = pr.ReadObject <UserInformation>();
sh = DatabaseQuery.Login(ref info, ref usrInfo);
UpdateClientInfo(usrInfo);
if (sh != Subheader.NULL)
{
SendError(socket, sh, null);
}
else
{
SendUserInfo(socket, info);
}
break;
case Subheader.REGISTER:
info = pr.ReadObject <UserInformation>();
sh = DatabaseQuery.Register(ref info, ref usrInfo);
UpdateClientInfo(usrInfo);
if (sh != Subheader.NULL)
{
SendError(socket, sh, null);
}
else
{
SendUserInfo(socket, info);
}
break;
case Subheader.USERNAME:
sh = DatabaseQuery.UserExists(pr.ReadString());
if (sh != Subheader.NULL)
{
SendError(socket, sh, null);
}
else
{
SendHeaders(socket, Header.Account, Subheader.USERNAME);
}
break;
case Subheader.CHANGEPASS:
info = pr.ReadObject <UserInformation>();
sh = DatabaseQuery.ChangePassword(ref info, ref usrInfo);
UpdateClientInfo(usrInfo);
if (sh != Subheader.NULL)
{
SendError(socket, sh, null);
}
else
{
SendUserInfo(socket, info);
}
break;
default: // Invalid Subheader
SendError(socket, Subheader.INVALIDREQ, null);
break;
}
}
