/// <summary>
/// Gets the user based on the given id.
/// Returns null when the user is not found.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static User GetById(int id)
{
User user = null;
Database db = new Database();
try
{
db.CreateCommand("SELECT * FROM users WHERE id = :id");
db.AddParameter("id", id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
{
dr.Read();
int userId = dr.GetValueByColumn<int>("id");
string username = dr.GetValueByColumn<string>("Username");
long uploadLimit = dr.GetValueByColumn<long>("uploadlimit");
bool isEmployee = dr.GetValueByColumn<int>("isemployee") == 1 ? true : false;
user = new User(id, username, uploadLimit, isEmployee);
}
}
finally
{
db.CloseConnection();
}
return user;
}
/// <summary>
/// Gets all categories from the database.
/// </summary>
/// <returns></returns>
public static List<Category> GetAll()
{
List<Category> categories = new List<Category>();
Database db = new Database();
try
{
db.CreateCommand("SELECT * FROM files_categories");
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
int id = dr.GetValueByColumn<int>("Id");
string title = dr.GetValueByColumn<string>("Title");
int parentId = dr.GetValueByColumn<int>("files_categories_id");
categories.Add(new Category(id, title, parentId));
}
}
finally
{
db.CloseConnection();
}
return categories;
}
/// <summary>
/// Gets the total rating the user has received on all of it's files that are active.
/// </summary>
/// <returns></returns>
public int GetTotalRating()
{
int totalRating = 0;
Database db = new Database();
try
{
db.CreateCommand("SELECT positive FROM files_likes " +
"LEFT JOIN files ON files.id = files_likes.files_id" +
" WHERE files.users_id = :id AND files.active = 1");
db.AddParameter(":id", Id);
db.OpenConnection();
db.ExecuteCommand();
while (db.DataReader.Read())
{
if (db.DataReader.GetInt32(0) > 0)
{
totalRating++;
}
else
{
totalRating--;
}
}
}
finally
{
db.CloseConnection();
}
return totalRating;
}
/// <summary>
/// Gets the amount of bytes currently uploaded by the user.
/// </summary>
/// <returns></returns>
public long GetFilesizeCurrentlyUploaded()
{
long size = 0;
Database db = new Database();
try
{
db.CreateCommand("SELECT SUM(filesize) as fullsize FROM files WHERE users_id = :id");
db.AddParameter(":id", Id);
db.OpenConnection();
db.ExecuteCommand();
while (db.DataReader.Read())
{
size = db.DataReader.GetValueByColumn<long>("fullsize");
}
}
finally
{
db.CloseConnection();
}
return size;
}
/// <summary>
/// Gets the amount of reports the user has received on all of it's files.
/// </summary>
/// <returns></returns>
public int GetAmountOfReports()
{
int totalRating = 0;
Database db = new Database();
try
{
db.CreateCommand("SELECT COUNT(files_reports.id) AS totalreports FROM files_reports " +
"LEFT JOIN files ON files.id = files_reports.files_id" +
" WHERE files.users_id = :id");
db.AddParameter(":id", Id);
db.OpenConnection();
db.ExecuteCommand();
while (db.DataReader.Read())
{
totalRating = db.DataReader.GetInt32(0);
}
}
finally
{
db.CloseConnection();
}
return totalRating;
}
/// <summary>
/// Gets the amount of files uploaded by the user.
/// </summary>
/// <returns></returns>
public int GetAmountOfFiles()
{
int amountOfFiles = 0;
Database db = new Database();
try
{
db.CreateCommand("SELECT COUNT(id) AS count FROM files WHERE users_id = :id");
db.AddParameter(":id", Id);
db.OpenConnection();
db.ExecuteCommand();
while (db.DataReader.Read())
{
amountOfFiles = db.DataReader.GetValueByColumn<int>("count");
}
}
finally
{
db.CloseConnection();
}
return amountOfFiles;
}
/// <summary>
/// Correctly inserts the file into the database.
/// </summary>
/// <param name="filename"></param>
/// <param name="title"></param>
/// <param name="size"></param>
/// <param name="description"></param>
/// <param name="user"></param>
/// <param name="category"></param>
/// <param name="privacy"></param>
/// <returns></returns>
public static bool Insert(string filename, string title, long size, string description, User user, Category category, Privacy privacy)
{
Database db = new Database();
try
{
db.CreateCommand(
"INSERT INTO files(filename, title, filesize, description, users_id, files_categories_id, private, active) VALUES(:filename, :title, :filesize, :description, :users_id, :files_categories_id, :private, 1)");
db.AddParameter("filename", filename);
db.AddParameter("title", title);
db.AddParameter("filesize", size);
db.AddParameter("description", description);
db.AddParameter("users_id", user.Id);
if (category != null)
{
db.AddParameter("files_categories_id", category.Id);
}
else
{
db.AddParameter("files_categories_id", DBNull.Value);
}
if (privacy == Privacy.@private)
{
db.AddParameter("private", 1);
}
else
{
db.AddParameter("private", DBNull.Value);
}
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
return true;
}
/// <summary>
/// Refreshes the download ListView with the most up-to-date search parameters and data.
/// Searches for current set category and filters.
/// </summary>
private void ReloadDownloadListView()
{
FormLoading.Show();
buttonDownloadRefresh.Enabled = false;
buttonDownloadSearch.Enabled = false;
listViewDownload.Items.Clear();
Database db = new Database();
try
{
// Selection query for files which are active and not private or belong too the current logged in user.
string command = "SELECT id FROM files WHERE active = 1 AND (private = 0 OR private IS NULL OR users_id = :usersId)";
// If the filter is set add the appriopiate WHERECLAUSE.
if (comboBoxDownloadFilterFilesize.SelectedItem != null)
{
ComboBoxItem item = (ComboBoxItem) comboBoxDownloadFilterFilesize.SelectedItem;
if (item.Value != null)
{
FilterFilesize filter = (FilterFilesize) item.Value;
if (filter.LowerLimit > 0 || filter.UpperLimit > 0)
{
command += " AND (";
if (filter.LowerLimit > 0)
{
command += "filesize >= " + Filesize.ConvertMegaByteToByte(filter.LowerLimit);
if (filter.UpperLimit > 0)
{
command += " AND ";
}
}
if (filter.UpperLimit > 0)
{
command += "filesize <= " + Filesize.ConvertMegaByteToByte(filter.UpperLimit);
}
command += ")";
}
}
}
// If there is no category selected.
if (currentCategory == null)
{
// If the search field is filled.
if (currentSearchDownload.Length > 0)
{
command += " AND LOWER(title) LIKE '%' || :title || '%'";
db.CreateCommand(command);
db.AddParameter("title", currentSearchDownload);
}
else
{
db.CreateCommand(command);
}
}
else
{
// Add the category to the WHERECLAUSE.
command += " AND files_categories_id = :id";
// If the search field is filled.
if (currentSearchDownload.Length > 0)
{
command += " AND LOWER(title) LIKE '%' || :title || '%'";
db.CreateCommand(command);
db.AddParameter("title", currentSearchDownload);
}
else
{
db.CreateCommand(command);
}
db.AddParameter("id", currentCategory.Id);
}
db.AddParameter("usersId", user.Id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
RemoteFile file = RemoteFile.GetFileById(dr.GetInt32(0));
listViewDownload.Items.Add(new ListViewItemRemoteFile(file));
}
buttonDownloadRefresh.Enabled = true;
RefreshInterfaceDownload();
RefreshDownloadRatingButtons();
}
catch (Exception ex)
{
MessageBox.Show((ex.Message));
}
finally
{
db.CloseConnection();
buttonDownloadRefresh.Enabled = true;
FormLoading.CloseForm();
buttonDownloadSearch.Enabled = true;
}
}
/// <summary>
/// Loads all comments into the list.
/// </summary>
private void LoadComments()
{
Database db = new Database();
try
{
comments = new List<RemoteFileComment>();
db.CreateCommand("SELECT * FROM files_comments WHERE files_id = :id");
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
User user = ftp_db_poc.User.GetById(dr.GetValueByColumn<int>("users_id"));
DateTime dateTime = dr.GetValueByColumn<DateTime>("created");
string text = dr.GetValueByColumn<string>("text");
RemoteFileComment comment = new RemoteFileComment(dr.GetValueByColumn<int>("id"), text, user, dateTime);
comments.Add(comment);
}
}
finally
{
db.CloseConnection();
}
}
/// <summary>
/// Reports the file. If the file has 3 or more reports the file will be deactivated and hidden to all users.
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public bool Report(User user)
{
Database db = new Database();
int count = 0;
try
{
db.CreateCommand("SELECT * FROM files_reports WHERE files_id = :id");
db.AddParameter("files_id", Id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
count++;
int userId = dr.GetValueByColumn<int>("users_id");
if (userId == user.Id)
{
throw new Exception("U heeft dit bestand al gerapporteerd.");
}
}
}
finally
{
db.CloseConnection();
}
try
{
db.CreateCommand("INSERT INTO files_reports(files_id, users_id) VALUES(:filesId, :usersId)");
db.AddParameter("filesId", Id);
db.AddParameter("usersId", user.Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
if (count >= 2)
{
try
{
db.CreateCommand("UPDATE files SET active = 0 WHERE id = :filesId");
db.AddParameter("filesId", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
}
return true;
}
/// <summary>
/// Removes the comment from the database and from the list.
/// </summary>
/// <param name="comment"></param>
public void RemoveComment(RemoteFileComment comment)
{
Database db = new Database();
try
{
db.CreateCommand("DELETE files_comments WHERE id = :id");
db.AddParameter("id", comment.Id);
db.OpenConnection();
db.ExecuteCommand();
comments.Remove(comment);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
db.CloseConnection();
}
}
/// <summary>
/// Rates the file for the given user. Positive(true) for a like, Positive(false) for a dislike.
/// </summary>
/// <param name="user"></param>
/// <param name="positive"></param>
public void Rate(User user, bool positive)
{
Database db = new Database();
int currentRating = CurrentRating(user);
try
{
if (currentRating == 0)
{
db.CreateCommand(
"INSERT INTO files_likes(positive, files_id, users_id) VALUES(:positive, :filesId, :usersId)");
}
else
{
if (currentRating == 1 && !positive)
{
int index = likes.FindIndex(x => x.Id == user.Id);
if (index >= 0)
{
likes.RemoveAt(index);
}
}
else if (currentRating == -1 && positive)
{
int index = dislikes.FindIndex(x => x.Id == user.Id);
if (index >= 0)
{
dislikes.RemoveAt(index);
}
}
db.CreateCommand(
"UPDATE files_likes SET positive = :positive WHERE files_id = :filesId AND users_id = :usersId");
}
db.AddParameter("positive", positive ? "1" : "-1");
db.AddParameter("filesId", Id);
db.AddParameter("usersId", user.Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
if (positive)
{
likes.Add(user);
}
else
{
dislikes.Add(user);
}
}
/// <summary>
/// Removes the file with all relations from the database and FTP server.
/// </summary>
public void Delete()
{
Database db = new Database();
// Remove file.
try
{
db.CreateCommand("DELETE FROM files WHERE id = :id");
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
// Remove likes.
try
{
db.CreateCommand("DELETE FROM files_likes WHERE files_id = :id");
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
// Remove comments.
try
{
db.CreateCommand("DELETE FROM files_comments WHERE files_id = :id");
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
// Remove reports.
try
{
db.CreateCommand("DELETE FROM files_reports WHERE files_id = :id");
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://127.0.0.1/" + Filename);
request.Credentials = new NetworkCredential("root", "root");
request.Method = WebRequestMethods.Ftp.DeleteFile;
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
}
public void Comment(User user, String comment)
{
Database db = new Database();
try
{
db.CreateCommand(
"INSERT INTO files_comments(files_id, users_id, text) VALUES(:filesId, :usersId, :text)");
db.AddParameter("filesId", Id);
db.AddParameter("usersId", user.Id);
db.AddParameter("text", comment);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
LoadRating();
}
/// <summary>
/// Loads the current rating of the file.
/// </summary>
private void LoadRating()
{
Database db = new Database();
try
{
db.CreateCommand(
"SELECT files_likes.positive, users.* FROM files_likes LEFT JOIN users ON users.id = files_likes.users_id WHERE files_likes.files_id = :files_id");
db.AddParameter("files_id", Id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
User user = new User(dr.GetInt32(1), dr.GetString(2));
// If the rating is positive it is a like else it is a dislike.
if (dr.GetValueByColumn<int>("positive") == 1)
{
likes.Add(user);
}
else
{
dislikes.Add(user);
}
}
}
finally
{
db.CloseConnection();
}
ratingsLoaded = true;
}
private static void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
string filename = new FileInfo(localFile.Filename).Name;
Database db = new Database();
try
{
bool exists = true;
// Check if the filename already exists.
// Continue until it is unique.
while (exists)
{
db.CreateCommand("SELECT * FROM files WHERE filename = :filename");
db.AddParameter("filename", filename);
db.OpenConnection();
db.ExecuteCommand();
if (!db.DataReader.HasRows)
{
exists = false;
}
else
{
Random rand = new Random();
filename = rand.Next(9).ToString() + "-" + filename;
}
}
FtpWebRequest request =
(FtpWebRequest)
WebRequest.Create("ftp://" + Properties.Settings.Default.ftp_server + "/" + filename);
request.UseBinary = true;
request.UsePassive = true;
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(Properties.Settings.Default.ftp_user,
Properties.Settings.Default.ftp_pass);
long filesize = new FileInfo(localFile.Filename).Length;
// Chunksize 1MB.
int chunksize = 1024*1024;
int numRetries = 0;
int maxRetries = 10;
long sentBytes = 0;
byte[] buffer = new byte[chunksize];
using (Stream ftpStream = request.GetRequestStream())
{
using (FileStream fileStream = System.IO.File.OpenRead(localFile.Filename))
{
int bytesRead = fileStream.Read(buffer, 0, chunksize);
while (bytesRead > 0)
{
try
{
if (backgroundWorker.CancellationPending)
{
e.Cancel = true;
return;
}
ftpStream.Write(buffer, 0, bytesRead);
sentBytes += bytesRead;
backgroundWorker.ReportProgress((int) (((decimal) sentBytes/(decimal) filesize)*100),
sentBytes.ToString() + " VERSTUURD, " + (filesize - sentBytes).ToString() +
" TE GAAN");
}
catch (Exception)
{
if (numRetries++ < maxRetries)
{
// Reverts to the last succesfully written/read position.
fileStream.Position -= bytesRead;
}
else
{
throw new Exception("Uploaden van bestand mislukt.");
}
}
bytesRead = fileStream.Read(buffer, 0, chunksize);
}
}
}
// Insert the file to the database when the uploading is finished.
FileInfo fi = new FileInfo(localFile.Filename);
RemoteFile.Insert(filename, localFile.Title, fi.Length, localFile.Description, user, localFile.Category,
localFile.Privacy);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
e.Cancel = true;
}
finally
{
db.CloseConnection();
}
}
//
// Methods
//
/// <summary>
/// Attempts to log the user in. Shows the errors in the correct label on the User Interface.
/// </summary>
private void Login()
{
labelResult.Text = "";
string username = textBoxUsername.Text.Trim();
string password = textBoxPassword.Text.Trim();
if (username.Length > 0 && password.Length > 0)
{
Database db = new Database();
try
{
FormLoading.Show();
db.CreateCommand("SELECT id FROM users WHERE username = :username AND password = :password");
db.AddParameter("username", username);
db.AddParameter("password", password);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
{
dr.Read();
User user = User.GetById(dr.GetValueByColumn<int>("id"));
this.formMain = new FormMain(user);
this.formMain.Show();
this.formMain.FormClosing += formMain_FormClosing;
this.formMain.logoutToolStripMenuItem.Click += afmeldenToolStripMenuItem_Click;
this.Hide();
}
else
{
throw new Exception("Uw aanmeldgegevens zijn onjuist.");
}
}
catch (Exception ex)
{
labelResult.Text = ex.Message;
}
finally
{
db.CloseConnection();
FormLoading.CloseForm();
}
}
}
/// <summary>
/// Gets an category based on the given Id. Returns NULL when the category is not found.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Category GetById(int id)
{
Category category = null;
Database db = new Database();
try
{
db.CreateCommand("SELECT * FROM files_categories WHERE Id = :Id");
db.AddParameter("Id", id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
string title = dr.GetValueByColumn<string>("Title");
int parentId = dr.GetValueByColumn<int>("files_categories_id");
category = new Category(id, title, parentId);
}
}
finally
{
db.CloseConnection();
}
return category;
}
/// <summary>
/// Changes the upload limit of the user. Immediatly updates the user in the database.
/// </summary>
/// <param name="bytes"></param>
public void ChangeUploadLimit(long bytes)
{
uploadLimit = bytes;
if (uploadLimit < 0)
{
throw new Exception("Upload limiet moet positief zijn.");
}
Database db = new Database();
try
{
db.CreateCommand("UPDATE users SET uploadlimit = :uploadlimit WHERE id = :id");
db.AddParameter("uploadlimit", uploadLimit);
db.AddParameter("id", Id);
db.OpenConnection();
db.ExecuteCommand();
}
finally
{
db.CloseConnection();
}
}
/// <summary>
/// Refreshes all the data in the moderator ListView, with the most up-to-date data and search parameters.
/// </summary>
private void ReloadModeratorListView()
{
buttonModeratorRefresh.Enabled = false;
buttonModeratorSearch.Enabled = false;
FormLoading.Show();
listViewModerator.Items.Clear();
Database db = new Database();
try
{
if (currentSearchModerator.Length > 0)
{
db.CreateCommand("SELECT id FROM users WHERE LOWER(username) LIKE '%' || :username || '%'");
db.AddParameter("username", currentSearchModerator);
}
else
{
db.CreateCommand("SELECT id FROM users");
}
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
User user = User.GetById(dr.GetValueByColumn<int>("id"));
listViewModerator.Items.Add(new ListViewItemModerator(user));
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
MessageBox.Show((ex.Message));
}
finally
{
db.CloseConnection();
buttonModeratorRefresh.Enabled = true;
buttonModeratorSearch.Enabled = true;
}
FormLoading.CloseForm();
RefreshInterfaceModerator();
}
/// <summary>
/// Gets the correct File from the database.
/// Returns NULL when the file is not found.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static RemoteFile GetFileById(int id)
{
RemoteFile file = null;
Database db = new Database();
try
{
db.CreateCommand("SELECT * FROM files WHERE id = :id");
db.AddParameter("id", id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
{
dr.Read();
file = new RemoteFile(dr.GetValueByColumn<int>("id"), dr.GetValueByColumn<string>("filename"),
dr.GetValueByColumn<string>("title"), dr.GetValueByColumn<long>("filesize"),
dr.GetValueByColumn<int>("files_categories_id"),
dr.GetValueByColumn<int>("active") > 0 ? true : false, dr.GetValueByColumn<int>("users_id"),
dr.GetValueByColumn<int>("private") > 0
? ftp_db_poc.Privacy.@private
: ftp_db_poc.Privacy.@public);
file.Description = dr.GetValueByColumn<string>("description");
}
}
finally
{
db.CloseConnection();
}
return file;
}
