public void UpdateRevenueItem()
{
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
string query = "UPDATE RevenueItem SET Title = @Title, Amount = @Amount WHERE RevenueItemID = @RevenueItemID";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@Title", Title);
command.Parameters.AddWithValue("@Amount", Amount);
command.Parameters.AddWithValue("@RevenueItemID", RevenueItemID);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public void AddRevenueItemToDatabase()
{
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
string query = "INSERT INTO RevenueItem(MonthlyRevenueRef, Title, Amount, Notes) VALUES(@MonthlyRevenueRef, @Title, @Amount, @Notes)";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@MonthlyRevenueRef", MonthlyRevenueRef);
command.Parameters.AddWithValue("@Title", Title);
command.Parameters.AddWithValue("@Amount", Amount);
command.Parameters.AddWithValue("@Notes", Notes);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public List <Part> AvailableParts()
{
List <Part> list = new List <Part>();
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
SqlDataReader reader;
string query = "SELECT * FROM Parts WHERE Quantity > 0 AND Active = 1 ORDER BY Category, Name";
command = new SqlCommand(query, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Part part = new Part
{
PartID = reader["PartID"].ToString(),
Name = reader["Name"].ToString(),
KeyWords = StringToList(reader["Keywords"].ToString()),
Specs = StringToDict(reader["specs"].ToString()),
Description = reader["Description"].ToString(),
Price = Convert.ToDecimal(reader["Price"]),
Active = Convert.ToBoolean(reader["Active"]),
ListDate = Convert.ToDateTime(reader["ListDate"]),
Location = reader["Location"].ToString(),
Notes = reader["Notes"].ToString(),
Quantity = Convert.ToDecimal(reader["Quantity"]),
Category = reader["Category"].ToString()
};
list.Add(part);
}
reader.Close();
connection.Close();
return(list);
}
public void AddPartSaleToDatabase()
{
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
string query = "INSERT INTO PartSale(PartRef, CustomerRef, SaleDate, Price, Quantity, Notes) VALUES(@PartRef, @CustomerRef, @SaleDate, @Price, @Quantity, @Notes)";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@PartRef", PartRef);
command.Parameters.AddWithValue("@CustomerRef", CustomerRef);
command.Parameters.AddWithValue("@SaleDate", SaleDate.ToShortDateString());
command.Parameters.AddWithValue("@Price", Price.ToString());
command.Parameters.AddWithValue("@Quantity", Quantity.ToString());
command.Parameters.AddWithValue("@Notes", Notes);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
Part.Quantity -= Quantity;
Part.UpdatePart();
}
public void UpdatePartSale(PartSale oldPartSale)
{
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
string query = "UPDATE PartSale SET SaleDate = @SaleDate, Price = @Price, Quantity = @Quantity, Notes = @Notes WHERE PartSaleID = @PartSaleID";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@PartRef", PartRef);
command.Parameters.AddWithValue("@CustomerRef", CustomerRef);
command.Parameters.AddWithValue("@SaleDate", SaleDate.ToShortDateString());
command.Parameters.AddWithValue("@Price", Price.ToString());
command.Parameters.AddWithValue("@Quantity", Quantity.ToString());
command.Parameters.AddWithValue("@Notes", Notes);
command.Parameters.AddWithValue("@PartSaleID", PartSaleID);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
Part.Quantity = Part.Quantity - (Quantity - oldPartSale.Quantity);
}
public void UpdateMonthlyRevenue()
{
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
string query = "UPDATE MonthlyRevenue SET Month = @Month, Year = @Year, BankDeposits = @BankDeposits, OutOfState = @OutOfState, Wholesale = @Wholesale, Consignments = @Consignments, Motorized = @Motorized, StoreUse = @Use, Notes = @Notes WHERE MonthlyRevenueID = MonthlyRevenueID";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@Month", Month);
command.Parameters.AddWithValue("@Year", Year);
command.Parameters.AddWithValue("@BankDeposits", BankDeposits);
command.Parameters.AddWithValue("@OutOfState", OutOfState);
command.Parameters.AddWithValue("@Wholesale", Wholesale);
command.Parameters.AddWithValue("@Consignments", Consignments);
command.Parameters.AddWithValue("@Motorized", Motorized);
command.Parameters.AddWithValue("@Use", Use);
command.Parameters.AddWithValue("@Notes", Notes);
command.Parameters.AddWithValue("@MonthlyRevenueIDs", MonthlyRevenueID);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
public List <int> Years()
{
List <int> list = new List <int>();
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
SqlDataReader reader;
string query = "SELECT DISTINCT Year FROM MonthlyRevenue ORDER BY Year";
command = new SqlCommand(query, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
int year = Convert.ToInt16(reader["Year"]);
list.Add(year);
}
reader.Close();
connection.Close();
return(list);
}
public List <string> Categories()
{
List <string> list = new List <string>();
MyConnection currentConnection = new MyConnection();
SqlConnection connection = currentConnection.CurrentConnection;
SqlCommand command;
SqlDataReader reader;
string query = "SELECT DISTINCT Category FROM Parts ORDER BY Category";
command = new SqlCommand(query, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
list.Add(reader["Category"].ToString());
}
reader.Close();
connection.Close();
return(list);
}
