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);
    }
예제 #3
0
    /// <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);
    }
예제 #4
0
    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);
    }
예제 #5
0
    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);
    }
예제 #7
0
    /// <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);
    }