public int assignBug()
{
DateTime date = DateTime.Now;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE BugDetails SET priority = @priority, assignee = @assignee, status = 'Assigned', date_modified = @date_modified WHERE bug_id = @bugid";
cmd.Parameters.AddWithValue("@priority", priority);
cmd.Parameters.AddWithValue("@assignee", assignee);
cmd.Parameters.AddWithValue("@date_modified", date);
cmd.Parameters.AddWithValue("@bugid", bugID);
cmd.Connection = conn;
int count = cmd.ExecuteNonQuery();
conn.Close();
if (count != 0)
{
return(0);
}
else
{
return(-1);
}
}
public DataTable countResolvedWeekly()
{
string date = DateTime.Now.StartOfWeek(DayOfWeek.Monday).ToString("yyyy-MM-dd h:mm tt");
string dateEnd = DateTime.Now.EndOfWeek(DayOfWeek.Monday).ToString("yyyy-MM-dd h:mm tt");
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails where date_resolved > @date && date_resolved < @dateEnd";
cmd.Parameters.AddWithValue("@dateEnd", dateEnd + "%");
cmd.Parameters.AddWithValue("@date", date + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable countComments()
{
string Month = DateTime.Now.AddMonths(-1).ToString("MM");
string Year = DateTime.Now.ToString("yyyy");
string currentMonth = Year + "-" + Month;
string date = DateTime.Now.StartOfWeek(DayOfWeek.Monday).ToString("yyyy-MM-dd h:mm tt");
string dateEnd = DateTime.Now.EndOfWeek(DayOfWeek.Monday).ToString("yyyy-MM-dd h:mm tt");
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM CommentDetails where comment_date > @date && comment_date < @dateEnd";
cmd.Parameters.AddWithValue("@currentMonth", currentMonth + "%");
cmd.Parameters.AddWithValue("@dateEnd", dateEnd + "%");
cmd.Parameters.AddWithValue("@date", date + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public int addBug()
{
DateTime date = DateTime.Now;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO BugDetails (bugreporter, title, keywords, category, description, date_reported, status) VALUES(@bugreporter, @title, @keywords, @category, @description, @date_reported, @status)";
cmd.Parameters.AddWithValue("@bugreporter", bugReporter);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@keywords", keywords);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@date_reported", date);
cmd.Parameters.AddWithValue("@category", category);
cmd.Parameters.AddWithValue("@status", "New");
cmd.Connection = conn;
int count = cmd.ExecuteNonQuery();
conn.Close();
if (count != 0)
{
return(0);
}
else
{
return(-1);
}
}
public DataTable countResolvedMonthly()
{
string Month = DateTime.Now.AddMonths(-1).ToString("MM");
string Year = DateTime.Now.ToString("yyyy");
string currentMonth = Year + "-" + Month;
string Month_word = DateTime.Now.AddMonths(-1).ToString("MMMM");
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails where date_resolved LIKE @currentMonth";
cmd.Parameters.AddWithValue("@currentMonth", currentMonth + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public MySqlCommand mostBugsFixed()
{
string Month = DateTime.Now.AddMonths(-1).ToString("MM");
string Year = DateTime.Now.ToString("yyyy");
string currentMonth = Year + "-" + Month;
string Month_word = DateTime.Now.AddMonths(-1).ToString("MMMM");
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT assignee FROM BugDetails WHERE status = 'Fixed' OR status = 'Verified' OR status = 'Closed' GROUP BY assignee ORDER BY COUNT(*) DESC LIMIT 1";
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(cmd);
conn.Close();
}
public MySqlCommand mostBugsReported()
{
string Month = DateTime.Now.AddMonths(-1).ToString("MM");
string Year = DateTime.Now.ToString("yyyy");
string currentMonth = Year + "-" + Month;
string Month_word = DateTime.Now.AddMonths(-1).ToString("MMMM");
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT bugreporter FROM BugDetails where date_reported LIKE @currentMonth GROUP BY bugreporter ORDER BY COUNT(*) DESC LIMIT 1";
cmd.Parameters.AddWithValue("@currentMonth", currentMonth + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(cmd);
conn.Close();
}
public bool validateUser()
{
bool status = false;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM UserDetails WHERE username=@username AND password=@password AND type=@type";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@type", type);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
status = true;
}
conn.Close();
return(status);
}
public int closeBugReport()
{
DateTime date = DateTime.Now;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE BugDetails SET status = 'Closed', date_modified = @date_modified, date_resolved = @date_resolved WHERE bug_id = @bugid";
cmd.Parameters.AddWithValue("@date_modified", date);
cmd.Parameters.AddWithValue("@date_resolved", date);
cmd.Parameters.AddWithValue("@bugid", bugID);
cmd.Connection = conn;
int count = cmd.ExecuteNonQuery();
conn.Close();
if (count != 0)
{
return(0);
}
else
{
return(-1);
}
}
public int reviewFix()
{
DateTime date = DateTime.Now;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE BugDetails SET status = @status WHERE bug_id = @bugid";
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@bugid", bugID);
cmd.Connection = conn;
int count = cmd.ExecuteNonQuery();
conn.Close();
if (count != 0)
{
return(0);
}
else
{
return(-1);
}
}
public BugClass(string id)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE bug_id=@bugid";
cmd.Parameters.AddWithValue("@bugid", id);
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet result = new DataSet();
da.Fill(result, "bugDetails");
conn.Close();
if (result.Tables["bugDetails"].Rows.Count > 0)
{
this.bugReporter = result.Tables["bugDetails"].Rows[0]["bugreporter"].ToString();
this.title = result.Tables["bugDetails"].Rows[0]["title"].ToString();
this.keywords = result.Tables["bugDetails"].Rows[0]["keywords"].ToString();
this.description = result.Tables["bugDetails"].Rows[0]["description"].ToString();
this.category = result.Tables["bugDetails"].Rows[0]["category"].ToString();
this.dateReported = DateTime.Parse(result.Tables["bugDetails"].Rows[0]["date_reported"].ToString());
if (!string.IsNullOrWhiteSpace(result.Tables["bugDetails"].Rows[0]["date_modified"].ToString()))
{
this.dateModified = DateTime.Parse(result.Tables["bugDetails"].Rows[0]["date_modified"].ToString());
}
else
{
this.dateModified = DateTime.Parse(result.Tables["bugDetails"].Rows[0]["date_reported"].ToString());
}
if (!string.IsNullOrWhiteSpace(result.Tables["bugDetails"].Rows[0]["date_resolved"].ToString()))
{
this.dateResolved = DateTime.Parse(result.Tables["bugDetails"].Rows[0]["date_resolved"].ToString());
}
this.assignee = result.Tables["bugDetails"].Rows[0]["assignee"].ToString();
this.priority = result.Tables["bugDetails"].Rows[0]["priority"].ToString();
this.status = result.Tables["bugDetails"].Rows[0]["status"].ToString();
}
}
public DataTable getBugsByAssigneeForTriager(string assignee)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE assignee LIKE '%" + assignee + "%' AND status = 'New' OR status = 'Verified' ORDER BY date_reported DESC";
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getBugsByAssignee(string assignee)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE assignee LIKE '%" + assignee + "%'";
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getBugsForReview()
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE status = 'Fixed'";
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getDevelopers()
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM UserDetails WHERE type = 'D'";
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public int addComment()
{
DateTime date = DateTime.Now;
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO CommentDetails (comment_bug_id,comment_username,comment_date,comment_text) VALUES(@bugid,@comment_username,@comment_date,@comment)";
cmd.Parameters.AddWithValue("@bugid", bugID);
cmd.Parameters.AddWithValue("@comment_username", commentUsername);
cmd.Parameters.AddWithValue("@comment", commentDescription);
cmd.Parameters.AddWithValue("@comment_date", date);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
return(0);
}
public DataTable getAllBugsForDeveloper()
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails where assignee = @username";
cmd.Parameters.AddWithValue("@username", assignee);
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getBugsByAssignee(string assignee, string username)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE bugreporter = @username AND assignee LIKE @assignee";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@assignee", "%" + assignee + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getBugsByKeywordsForDeveloper(string keywords, string username)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE assignee = @username AND keywords LIKE @keywords";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@keywords", "%" + keywords + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
//search for reviewer
public DataTable getBugsByTitleReviewer(string title, string status)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE status = @status AND title LIKE @title";
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@title", "%" + title + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public DataTable getBugsByKeywordsForReviewer(string keywords, string status)
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM BugDetails WHERE bugreporter = @status AND keywords LIKE @keywords";
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@keywords", "%" + keywords + "%");
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public int countCommentsByID()
{
DatabaseClass db = new DatabaseClass();
MySqlConnection conn = db.getConnection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM CommentDetails where comment_bug_id = @bugid";
cmd.Parameters.AddWithValue("@bugid", bugID);
cmd.Connection = conn;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt.Rows.Count);
}