public static object Update(Feedback originalFeedback, Feedback feedback)
{
const string update = "UPDATE Feedback " +
"SET DateClosed = @DateClosed, "
+ "Description = @Description " +
"WHERE Description = @originalDescription";
var connection = new OleDbConnection(BallgameDatabase.GetConnectionString());
var command = new OleDbCommand(update, connection);
if (Convert.ToDateTime(feedback.DateClosed) == Convert.ToDateTime("01/01/0001 12:00:00 AM"))
{
command.Parameters.AddWithValue("DateClosed", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("DateClosed", feedback.DateClosed);
}
command.Parameters.AddWithValue("Description", feedback.Description);
command.Parameters.AddWithValue("originalDescription", originalFeedback.Description);
connection.Open();
var updateCount = command.ExecuteNonQuery();
connection.Close();
return(updateCount);
}
public static IEnumerable GetCustomerFeedback(int customerId)
{
var feedbackList = new List <Feedback>();
var connection = new OleDbConnection(BallgameDatabase.GetConnectionString());
const string selection = "SELECT SupportID, SoftwareID, DateOpened, DateClosed, Title, Description " +
"FROM Feedback " +
"WHERE CustomerID = @CustomerID " +
"ORDER BY SupportID";
var command = new OleDbCommand(selection, connection);
command.Parameters.AddWithValue("CustomerID", customerId);
connection.Open();
var dataReader = command.ExecuteReader();
while (dataReader != null && dataReader.Read())
{
var currFeedback = new Feedback
{
SupportId = dataReader["SupportID"].ToString(),
SoftwareId = dataReader["SoftwareID"].ToString(),
DateOpened = dataReader["DateOpened"].ToString(),
DateClosed = dataReader["DateClosed"].ToString(),
Title = dataReader["Title"].ToString(),
Description = dataReader["Description"].ToString()
};
feedbackList.Add(currFeedback);
}
return(feedbackList);
}
/// <summary>
/// Gets all support staff.
/// </summary>
/// <returns></returns>
public static IEnumerable GetAllSupportStaff()
{
var connection =
new OleDbConnection(BallgameDatabase.GetConnectionString());
const string selection = "SELECT SupportID, Name, Email, Phone FROM Support ORDER BY Name";
var command = new OleDbCommand(selection, connection);
connection.Open();
var dataReader = command.ExecuteReader();
return(dataReader);
}
public static IEnumerable GetCustomersWithFeedback()
{
OleDbConnection con = new OleDbConnection(BallgameDatabase.GetConnectionString());
const string select = "SELECT CustomerID, Name FROM Customer WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Feedback WHERE SupportID IS NOT NULL)ORDER By Name ";
OleDbCommand cmd = new OleDbCommand(select, con);
con.Open();
OleDbDataReader reader = cmd.ExecuteReader();
return(reader);
}
public static IEnumerable GetAllSupportStaff()
{
OleDbConnection con = new OleDbConnection(BallgameDatabase.GetConnectionString());
const string select = "SELECT SupportID, Name FROM Support ORDER BY Name";
OleDbCommand cmd = new OleDbCommand(select, con);
con.Open();
OleDbDataReader reader = cmd.ExecuteReader();
return(reader);
}
/// <summary>
/// Gets the customer feedback.
/// </summary>
/// <param name="customerId">The customer identifier.</param>
/// <returns>IEnumerable of all customer feedback for certain customer</returns>
public static IEnumerable GetCustomerFeedback(int customerId)
{
OleDbConnection con = new OleDbConnection(BallgameDatabase.GetConnectionString());
const string select = "SELECT * FROM Feedback WHERE CustomerID = @customerId ORDER BY SupportID";
OleDbCommand cmd = new OleDbCommand(select, con);
cmd.Parameters.AddWithValue("CustomerID", customerId);
con.Open();
OleDbDataReader reader = cmd.ExecuteReader();
return(reader);
}
/// <summary>
/// Gets the customers with feedback.
/// </summary>
/// <returns>Collection of customer feedback</returns>
public static IEnumerable GetCustomersWithFeedback()
{
var connection =
new OleDbConnection(BallgameDatabase.GetConnectionString());
const string selection = "SELECT CustomerID, Name FROM Customer " +
"WHERE CustomerID IN " +
"(SELECT DISTINCT CustomerID FROM Feedback WHERE SupportID IS NOT NULL) " +
"ORDER BY Name";
var command = new OleDbCommand(selection, connection);
connection.Open();
var dataReader = command.ExecuteReader();
return(dataReader);
}
/// <summary>
/// Gets the open feedback incidents.
/// </summary>
/// <param name="supportStaffId">The support staff identifier.</param>
/// <returns>IEnumerable of all open feedback incidents</returns>
public static IEnumerable GetOpenFeedbackIncidents(int supportStaffId)
{
OleDbConnection con = new OleDbConnection(BallgameDatabase.GetConnectionString());
const string select = "SELECT Feedback.DateOpened, Software.Name, Customer.Name AS CustomerName FROM " +
"((Customer INNER JOIN Feedback ON Customer.CustomerID = Feedback.CustomerID) INNER JOIN " +
"Software ON Feedback.SoftwareID = Software.SoftwareID) WHERE SupportID = @supportStaffId AND DateClosed IS NULL ORDER BY DateOpened";
OleDbCommand cmd = new OleDbCommand(select, con);
cmd.Parameters.AddWithValue("SupportID", supportStaffId);
con.Open();
OleDbDataReader reader = cmd.ExecuteReader();
return(reader);
}
public static int UpdateFeedback(Feedback originalFeedback, Feedback feedback)
{
int updateCount;
const string update =
"UPDATE Feedback SET DateClosed = @DateClosed, Description = @Description WHERE DateClosed = @originalDateClosed AND Description =@originalDescription";
using (OleDbConnection con = new OleDbConnection(BallgameDatabase.GetConnectionString()))
{
using (OleDbCommand updateCommandcmd = new OleDbCommand(update, con))
{
if (Convert.ToDateTime(feedback.DateClosed) == Convert.ToDateTime("01/01/0001 12:00:00 AM"))
{
updateCommandcmd.Parameters.AddWithValue("DateClosed", DBNull.Value);
}
else
{
updateCommandcmd.Parameters.AddWithValue("DateClosed", Convert.ToDateTime(feedback.DateClosed));
}
updateCommandcmd.Parameters.AddWithValue("Description", feedback.Description);
if (originalFeedback.DateClosed == null)
{
originalFeedback.DateClosed = "01/01/0001 12:00:00 AM";
updateCommandcmd.Parameters.AddWithValue("originalDateClosed", Convert.ToDateTime(originalFeedback.DateClosed));
}
else
{
updateCommandcmd.Parameters.AddWithValue("originalDateClosed", Convert.ToDateTime(originalFeedback.DateClosed));
}
updateCommandcmd.Parameters.AddWithValue("originalDescription", originalFeedback.Description);
con.Open();
updateCount = updateCommandcmd.ExecuteNonQuery();
con.Close();
}
}
return(updateCount);
}
/// <summary>
/// Gets the open feedback incidents.
/// </summary>
/// <param name="supportStaffId">The support staff identifier.</param>
/// <returns></returns>
public static IEnumerable GetOpenFeedbackIncidents(int supportStaffId)
{
var connection =
new OleDbConnection(BallgameDatabase.GetConnectionString());
const string selection = "SELECT DateOpened, SoftwareID, Name " +
"FROM Feedback " +
"INNER JOIN Customer ON Feedback.CustomerID = Customer.CustomerID " +
"WHERE SupportID = @SupportID AND DateClosed IS NULL " +
"ORDER BY DateOpened";
var command = new OleDbCommand(selection, connection);
command.Parameters.AddWithValue("SupportID", supportStaffId);
connection.Open();
var dataReader = command.ExecuteReader();
return(dataReader);
}