private void storeItemsInComboBox()
{
//TODO: Weird results
//var itemNameQuery =
//from product in data.Products
//select new {product.productName};
//itemName.ItemsSource = itemNameQuery.ToList();
//Copied from Products page as above query was not working as intended
SqlAccessor.Open();
int i = 0;
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT DISTINCT productID FROM dbo.Sales ORDER BY(productID);"))
{
if (reader != null)
{
while (reader.Read())
{
itemName.Items.Add(reader["productID"]);
i++;
}
}
}
SqlAccessor.Close();
}
private void Change_Clicked(object sender, RoutedEventArgs e)
{
// Update existing product
List <SqlParameter> sqlParameters = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@name", Value = edititemname.Text
},
new SqlParameter()
{
ParameterName = "@curQuan", Value = editProductQuantity.Text
}
};
SqlAccessor.Open();
using (SqlDataReader reader = SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE productName = @name;", sqlParameters))
{
if (reader != null)
{
while (reader.Read())
{
if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
{
LoadProductsToDataGrid();
}
}
}
}
SqlAccessor.Close();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
InventoryItem newInvItem;
if ((sender as Button) == salesadditem)
{
int currentItemQuantity = 0;
SqlAccessor.Open();
// Get the current quantity of a product
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
{
if (reader != null)
{
while (reader.Read())
{
if ((reader["productName"].ToString().ToLower() == itemnamebox.Text.ToLower()))
{
currentItemQuantity = int.Parse((reader["currentQuantity"].ToString()));
}
}
}
}
// Reduce the current quantity of the product based on value
List <SqlParameter> sqlParameters = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@curQuan", Value = currentItemQuantity - int.Parse(qtextbox.Text)
},
new SqlParameter()
{
ParameterName = "@name", Value = itemnamebox.Text
}
};
SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE productName = @name;", sqlParameters);
SqlAccessor.Close();
// UI / Storing
newInvItem = new InventoryItem {
Name = itemnamebox.Text, QuantityCurrent = int.Parse(qtextbox.Text)
};
qtextbox.Clear();
_inventoryItems.Add(newInvItem);
dataGrid.Items.Add(newInvItem);
}
}
private void LoadProductsToDropdown()
{
SqlAccessor.Open();
int i = 0;
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
{
if (reader != null)
{
while (reader.Read())
{
edititemname.Items.Add(reader["productName"].ToString());
i++;
}
}
}
SqlAccessor.Close();
}
public SalesScreen()
{
InitializeComponent();
InitDataGrid(dataGrid);
SqlAccessor.Open();
// Load from database -> combo box
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
{
if (reader != null)
{
int i = 0;
while (reader.Read())
{
itemnamebox.Items.Add(reader["productName"].ToString());
i++;
}
}
}
SqlAccessor.Close();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
IQueryable query = null;
int x = int.Parse(itemName.SelectedItem.ToString());
int total = 0;
//Monthly
var query2 =
from p in data.Products
where p.productID == x
select new { p.productName };
if (requestedPeriod.Text == "Monthly Forecast")
{
query =
from s in data.Sales
where s.productID == x
group s by s.productID into sales
select new
{
sales = sales.Sum(a => a.salesQuantity)
};
// Output Monthly
foreach (var row in query)
{
foreach (var row2 in query2)
{
forecastDescriptor.Text = "Monthly Sales Forecast for: " + row2.productName.ToString() + row.ToString().Replace("{", "").Replace("}", " ");
}
}
}
//Weekly
else if (requestedPeriod.Text == "Weekly Forecast")
{
SqlAccessor.Open();
int i = 0;
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT productID, DATEADD(week, DATEDIFF(week, 0, saleDate), 0) AS WeekStart, SUM(salesQuantity) as WeeklySales FROM Sales WHERE productID =" + x + "GROUP BY DATEADD(week, DATEDIFF(week, 0, saleDate), 0), productID;"))
{
if (reader != null)
{
while (reader.Read())
{
//pass the reader variables to a string
string readerToString = "ID: " + reader["productID"].ToString() + " DateTime: " + Convert.ToString(reader["WeekStart"]) + " Sales: " + reader["WeeklySales"].ToString();
//calculate total
total = reader.GetInt32(2) + total;
// forecastDescriptor.Text = read;
i++;
}
}
}
// i being the number of entries in reader
int average = total / i;
SqlAccessor.Close();
// Output Weekly
//forecastDescriptor.Text = "Weekly Sales Prediction for product: " + + " is " + average;
foreach (var row in query2)
{
forecastDescriptor.Text = "Weekly Sales Prediction for product: " + row.productName.ToString() + " is " + average;
}
}
}
private void SaveChanges_Clicked(object sender, RoutedEventArgs e)
{
int currentProductID = int.MaxValue;
SqlAccessor.Open();
// Match each datagrid item with product table item, and insert product into sales table
for (int j = 0; j < dataGrid.Items.Count; j++)
{
// Match product
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
{
if (reader != null)
{
while (reader.Read())
{
if ((reader["productName"].ToString().ToLower() == _inventoryItems[j].Name.ToLower()))
{
currentProductID = int.Parse((reader["productID"].ToString()));
}
}
}
}
// Add product to sale
if (currentProductID != int.MaxValue)
{
DateTime t = DateTime.Today;
if (saleDatePicker.SelectedDate.HasValue)
{
t = saleDatePicker.SelectedDate.Value;
}
List <SqlParameter> sqlParameters = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@pid", Value = currentProductID
},
new SqlParameter()
{
ParameterName = "@date", Value = t
},
new SqlParameter()
{
ParameterName = "@quantity", Value = _inventoryItems[j].QuantityCurrent
},
new SqlParameter()
{
ParameterName = "@loginid", Value = loginscreen.GetLoginName()
}
};
using (SqlDataReader reader = SqlAccessor.RunQuery("INSERT INTO dbo.Sales (productID, saleDate, salesQuantity, employee) VALUES (@pid, @date, @quantity, @loginid)", sqlParameters))
{
if (reader != null)
{
while (reader.Read())
{
if ((reader["productName"].ToString().ToLower() == _inventoryItems[j].Name.ToLower()))
{
currentProductID = int.Parse((reader["productID"].ToString()));
}
}
}
}
}
else
{
Console.WriteLine("Error, currentProductID not found in database");
}
}
SqlAccessor.Close();
// UI / Storing
_inventoryItems.Clear();
dataGrid.Items.Clear();
}
private void Add_Clicked(object sender, RoutedEventArgs e)
{
if ((sender as Button) == additembutton)
{
bool checkItemInDB = false;
int currentItemQuantity = 0;
SqlAccessor.Open();
// Confirm product is in database, and get the current quantity of that item
using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
{
if (reader != null)
{
while (reader.Read())
{
if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
{
checkItemInDB = true;
currentItemQuantity = int.Parse(reader["currentQuantity"].ToString());
}
}
}
}
if (checkItemInDB == false)
{
// Add new product
List <SqlParameter> sqlParameters = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@name", Value = addProductName.Text
},
new SqlParameter()
{
ParameterName = "@quantity", Value = addProductQuantity.Text
},
new SqlParameter()
{
ParameterName = "@brandID", Value = addProductID.Text
}
};
using (SqlDataReader reader = SqlAccessor.RunQuery("INSERT INTO dbo.Products (productName, currentQuantity, brandID) VALUES (@name, @quantity, @brandID)", sqlParameters))
{
if (reader != null)
{
while (reader.Read())
{
if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
{
LoadProductsToDataGrid();
edititemname.Items.Add(addProductName.Text);
}
}
}
}
}
else
{
// Update existing product
List <SqlParameter> sqlParameters = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@id", Value = editProductQuantity.Text
},
new SqlParameter()
{
ParameterName = "@curQuan", Value = currentItemQuantity + int.Parse(addProductQuantity.Text)
}
};
using (SqlDataReader reader = SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE brandID = @id;", sqlParameters))
{
LoadProductsToDataGrid();
}
}
SqlAccessor.Close();
}
}