/// <summary>
/// Update Bug Information
/// </summary>
/// <param name="bug"></param>
/// <returns></returns>
public bool UpdateBug(BugModel bug)
{
bool flag = false;
try
{
String sql = "update tbl_bug set code=@code, status=@status, version=@version where bug_id = @bug_id";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// Add and set value for parameter.
Debug.WriteLine("UpdateBUg Called");
cmd.Parameters.AddWithValue("@code", bug.getCode());
cmd.Parameters.AddWithValue("@status", bug.getStatus());
cmd.Parameters.AddWithValue("@bug_id", bug.getBUGID());
cmd.Parameters.AddWithValue("@version", bug.getVersion());
Debug.WriteLine(bug.getCode() + bug.getStatus() + bug.getBUGID());
int rowCount = cmd.ExecuteNonQuery();
if (rowCount > 0)
{
Debug.WriteLine("Updated");
flag = setHistory(bug);
}
else
{
flag = false;
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
/// <summary>
/// Get History for Audit
/// </summary>
/// <param name="bug"></param>
public bool setHistory(BugModel bug)
{
bool flag = false;
try {
String sql = "INSERT INTO `tbl_history`( `bug_id`,`version`, `updatedate`, `updatedby`, `code`, `status`, `remarks`) " +
"VALUES (@bug_id,@version,@updatedate,@updatedby,@code,@status,@remarks)";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Parameters.AddWithValue("@bug_id", bug.getBUGID());
cmd.Parameters.AddWithValue("@version", bug.getVersion());
cmd.Parameters.AddWithValue("@updatedate", DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"));
cmd.Parameters.AddWithValue("@updatedby", bug.getUsername());
cmd.Parameters.AddWithValue("@code", bug.getCode());
cmd.Parameters.AddWithValue("@status", bug.getStatus());
cmd.Parameters.AddWithValue("@remarks", bug.getRemarks());
Debug.WriteLine("setHistory Called");
cmd.Connection = conn;
cmd.CommandText = sql;
int rowCount = cmd.ExecuteNonQuery();
if (rowCount > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
//check username if assign
public bool CheckUsername(String username)
{
bool flag = false;
try {
String sql = "select * from tbl_user where username=@developerusername";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// Add and set value for parameter.
cmd.Parameters.AddWithValue("@developerusername", username);
MySqlDataReader component = cmd.ExecuteReader();
if (component.Read())
{
int forrole = component.GetOrdinal("role");
String role = component.GetString(forrole);
if (role == "developer")
{
flag = true;
}
else
{
flag = false;
}
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
public bool addProjectComponent(BugModel bug)
{
bool flag = false;
try
{
String sql = "INSERT INTO `tbl_component`( `component_name`, `component_desc`, `project_id`, `developerusername`) VALUES (@component_name,@component_desc,@project_id,@developerusername)";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
Debug.WriteLine("addprojectcomponent Called");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@component_name", bug.getComponentName());
cmd.Parameters.AddWithValue("@component_desc", bug.getcomponentdesc());
cmd.Parameters.AddWithValue("@project_id", bug.getprojectid());
cmd.Parameters.AddWithValue("@developerusername", bug.getUsername());
cmd.CommandText = sql;
int rowCount = cmd.ExecuteNonQuery();
if (rowCount > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
//popolate component list
public System.Collections.ArrayList PopulateComponent(String project_name)
{
int project_id = getProjectID(project_name);
Debug.WriteLine("This IS Debug Message" + project_id + project_name);
try
{
//query to select component id
String sql = "select * from tbl_component where project_id=@project_id";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// Add and set value for parameter.
cmd.Parameters.AddWithValue("@project_id", project_id);
MySqlDataReader component = cmd.ExecuteReader();
while (component.Read())
{
int component_name = component.GetOrdinal("component_name");
componentlist.Add(component.GetString(component_name));
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(componentlist);
}
/// <summary>
/// View All Audit History
/// </summary>
/// <param name="updatedate"></param>
/// <returns></returns>
public static DataTable getAuditHistory(String updatedate)
{
using (DataTable historytable = new DataTable())
{
try
{
String sql = "SELECT * FROM tbl_history where updatedate=@updatedate";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
DateTime enteredDate = DateTime.Parse(updatedate);
MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
cmd.Parameters.AddWithValue("@updatedate", enteredDate);
Debug.WriteLine(updatedate);
MyAdapter.SelectCommand = cmd;
MyAdapter.Fill(historytable);
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(historytable);
}
}
/// <summary>
/// Audit History Creator
/// </summary>
/// <returns></returns>
public static DataTable getHistory(int bugid)
{
using (DataTable historytable = new DataTable())
{
try
{
String sql = "SELECT * FROM tbl_history where bug_id=@bug_id ORDER by updatedate DESC";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
cmd.Parameters.AddWithValue("@bug_id", bugid);
Debug.WriteLine(bugid);
MyAdapter.SelectCommand = cmd;
MyAdapter.Fill(historytable);
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(historytable);
}
}
//populate tester dashboard
public void populatetesterdashboard(String username)
{
try
{
String sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where submittedby=@username";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
cmd.Parameters.AddWithValue("@username", username);
MyAdapter.SelectCommand = cmd;
DataTable dTable = new DataTable();
MyAdapter.Fill(dTable);
TesterDashboard tdash = new TesterDashboard();
tdash.dataTable = dTable;
tdash.Show();
tdash.sessionusername = username;
Debug.WriteLine("Logged in");
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
}
/// <summary>
/// Search Data
/// </summary>
/// <param name="searchfor"></param>
/// <param name="searchtext"></param>
/// <returns></returns>
public static DataTable getSearch(String searchfor, String searchtext)
{
using (DataTable searchtable = new DataTable())
{
try
{
String sql = "select * from tbl_bug where bugid>=1";
switch (searchfor)
{
case "code":
sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where code like @searchtext";
break;
case "method":
sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where method like @searchtext";
break;
case "class":
sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where class like @searchtext";
break;
case "summary":
sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where summary like @searchtext";
break;
case "bugdesc":
sql = "select bug_id as id, summary as Summary,submittedby from tbl_bug where bugdesc like @searchtext";
break;
default:
sql = "select * from tbl_bug where bugid>=1";
break;
}
MySqlConnection conn = DBUtils.GetDBConnection();
Debug.WriteLine(sql);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
// cmd.Parameters.AddWithValue("@searchfor", searchfor);
if (searchtext == "")
{
MySqlDataReader data = cmd.ExecuteReader();
searchtable.Load(data);
}
else
{
cmd.Parameters.Add("@searchtext", MySqlDbType.VarChar, 200).Value = "%" + searchtext + "%";
}
Debug.WriteLine(searchtext);
Debug.WriteLine(searchfor);
MyAdapter.SelectCommand = cmd;
MyAdapter.Fill(searchtable);
Debug.WriteLine("Search query found" + searchtable.Rows[0][2].ToString());
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(searchtable);
}
}
//report bug
public bool ReportBug(BugModel bug)
{
Bitmap img = new Bitmap(bug.getImageLocation());
var image = imageToByte(img);
try
{
//query to insert bug report to the database
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
String sql = "INSERT INTO `tbl_bug`( `component_id`, `summary`, `version`, `severity`, `os`," +
" `class`, `method`, `linenofrom`, `linenoto`, " +
"`vcrurl`, `screenshot`, `bugdesc`, `code`,`submittedby`,`submitteddate`) VALUES (@component_id,@summary,@version,@severity,@os,@class,@method," +
"@linenofrom,@linenoto,@vcrurl,@screenshot,@bugdesc,@code,@submittedby,@submitteddate)";
//adding values to the parameters
cmd.Parameters.AddWithValue("@component_id", bug.getComponentID());
cmd.Parameters.AddWithValue("@summary", bug.getSummary());
cmd.Parameters.AddWithValue("@version", bug.getVersion());
cmd.Parameters.AddWithValue("@severity", bug.getSeverity());
cmd.Parameters.AddWithValue("@os", bug.getOS());
cmd.Parameters.AddWithValue("@class", bug.getClass());
cmd.Parameters.AddWithValue("@method", bug.getMethod());
cmd.Parameters.AddWithValue("@linenofrom", bug.getlinenofrom());
cmd.Parameters.AddWithValue("@linenoto", bug.getlinenoto());
cmd.Parameters.AddWithValue("@vcrurl", bug.getVCURL());
//getting image
var paramUserImage = new MySqlParameter("@screenshot", MySqlDbType.Blob, image.Length);
paramUserImage.Value = image;
cmd.Parameters.Add(paramUserImage);
cmd.Parameters.AddWithValue("@bugdesc", bug.getbugdesc());
cmd.Parameters.AddWithValue("@code", bug.getCode());
cmd.Parameters.AddWithValue("@submittedby", submittedby);
cmd.Parameters.AddWithValue("@submitteddate", DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"));
cmd.Connection = conn;
cmd.CommandText = sql;
int rowCount = cmd.ExecuteNonQuery();
if (rowCount >= 1)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
//User Login
public bool LoginUser(String username, String password)
{
bool flag = false;
try
{
//query to check if username and password exists
String sql = "select * from tbl_user where username=@username and password=@password";
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// Add and set value for parameter.
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
MySqlDataReader login = cmd.ExecuteReader();
if (login.Read())
{
sessionusername = username;
int location = login.GetOrdinal("pplocation");
int role = login.GetOrdinal("role");
//Open Project Manager Dashboard
if (login.GetString(role) == "projectmanager")
{
populatePMDashboard();
flag = true;
}
else if (login.GetString(role) == "developer")
{
flag = true;
populateDeveloperDashboard(username);
}
else if (login.GetString(role) == "tester")
{
flag = true;
populatetesterdashboard(username);
}
}
else
{
// home.setPasswordMessage("Username or password do not match");
// home.Refresh();
Debug.WriteLine("Username and password do not match");
flag = false;
}
}
catch (Exception e)
{
Debug.WriteLine("Error Message: " + e);
Debug.WriteLine(e.StackTrace);
}
return(flag);
}
