private void btnRefundOrder_Click(object sender, EventArgs e)
{
DialogResult result = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to Refund this Order?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
//Remove Sale
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string cmd = "DELETE FROM Sales WHERE SaleID ='" + listBox1.Text.ToString() + "'";
SqlCommand sqlcom = new SqlCommand(cmd, sqlcon);
sqlcom.ExecuteNonQuery();
sqlcon.Close();
//Refresh Listbox
listBox1.Items.Clear();
SqlConnection sqlcon1 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon1.Open();
string Select = "SELECT SaleID FROM Sales";
SqlCommand sqlcom1 = new SqlCommand(Select, sqlcon1);
SqlDataReader Dr;
Dr = sqlcom1.ExecuteReader();
if (Dr.HasRows)
{
while (Dr.Read())
{
listBox1.Items.Add(Dr["SaleID"].ToString());
}
}
Dr.Close();
sqlcon1.Close();
//Get Values Required
int QuantityRefunded = Convert.ToInt32(txtQuantityPurchased.Text);
int QuantityInStock;
int FinalQuantity;
string ProductRefunded = txtItemName.Text;
SqlConnection sqlcon3 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon3.Open();
string Cmd3 = "SELECT ProductQuantityInStock From Products WHERE ProductName ='" + ProductRefunded.ToString() + "'";
SqlCommand sqlcom3 = new SqlCommand(Cmd3, sqlcon3);
SqlDataReader Reader;
Reader = sqlcom3.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
QuantityInStock = Convert.ToInt32((Reader["ProductQuantityInStock"]));
FinalQuantity = QuantityInStock + QuantityRefunded;
// Update Quantity in Stock
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string Command = "UPDATE Products SET ProductQuantityInStock ='" + FinalQuantity.ToString() + "' WHERE ProductName ='" + ProductRefunded.ToString() + "'";
SqlCommand sqlcom2 = new SqlCommand(Command, sqlcon2);
sqlcom2.ExecuteNonQuery();
sqlcon2.Close();
}
}
Reader.Close();
sqlcon3.Close();
MetroFramework.MetroMessageBox.Show(this, "The Sale has been Successfully Refunded!", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
//Deduct Points
//Get Membership Status
SelectedCustomer = cbxCustomerName.Text;
try
{
SqlConnection sqlcon5 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon5.Open();
string GetMembership = "SELECT isMember From Customers Where CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom5 = new SqlCommand(GetMembership, sqlcon5);
SqlDataReader dr5;
dr5 = sqlcom5.ExecuteReader();
if (dr5.HasRows)
{
while (dr5.Read())
{
isMember = Convert.ToInt32((dr5["isMember"]));
if (isMember == 1)
{
try
{
SqlConnection sqlcon6 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon6.Open();
string GetPoints = "SELECT LoyaltyPointsAvailable FROM Customers WHERE CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom6 = new SqlCommand(GetPoints, sqlcon6);
SqlDataReader dr;
dr = sqlcom6.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
CurrentPoints = Convert.ToDecimal((dr["LoyaltyPointsAvailable"]));
decimal Cost = Convert.ToDecimal(txtCostOfSale.Text);
decimal PointsReceived;
decimal FinalPoints;
PointsReceived = Cost * Convert.ToDecimal(Globals_Class.loyaltyPointsPercentage);
FinalPoints = CurrentPoints - PointsReceived;
//Add Points
SqlConnection sqlcon9 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon9.Open();
string UpdatePoints = "UPDATE Customers SET LoyaltyPointsAvailable ='" + FinalPoints.ToString() + "' WHERE CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom9 = new SqlCommand(UpdatePoints, sqlcon9);
sqlcom9.ExecuteNonQuery();
sqlcon9.Close();
}
}
dr.Close();
sqlcon6.Close();
}
catch
{
}
}
else
{
}
}
}
dr5.Close();
sqlcon5.Close();
}
catch
{
isMember = 0;
}
//Get Current Points
cbxCustomerName.Text = "";
cbxCustomerName.Items.Clear();
//Add Customers
SqlConnection sqlcon7 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon7.Open();
string Select7 = "SELECT CustomerFullName FROM Customers";
SqlCommand sqlcom7 = new SqlCommand(Select7, sqlcon7);
SqlDataReader dr2;
dr2 = sqlcom7.ExecuteReader();
if (dr2.HasRows)
{
while (dr2.Read())
{
cbxCustomerName.Items.Add((dr2["CustomerFullName"].ToString()));
}
}
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "An Error Occurred Whislt Connecting to the Database!", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
}
private void btnCaptureSale_Click(object sender, EventArgs e)
{
try
{
//Place Order
foreach (DataGridViewRow row in dataGridView1.Rows)
{
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Command = "INSERT INTO Sales(ItemName, ItemQuantity, LinePrice) VALUES(@ItemName, @ItemQuantity, @LinePrice)";
SqlCommand sqlcom = new SqlCommand(Command, sqlcon);
sqlcom.Parameters.AddWithValue("@ItemName", row.Cells["colItemName"].Value);
sqlcom.Parameters.AddWithValue("@ItemQuantity", row.Cells["colItemQuantity"].Value);
sqlcom.Parameters.AddWithValue("@LinePrice", row.Cells["colLinePrice"].Value);
sqlcom.ExecuteNonQuery();
sqlcon.Close();
//Update Stock Quantity
int QinStock;
int QPurchased;
int QLeft;
int ProductID;
SqlConnection sqlcon1 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon1.Open();
string SelectQinStock = "SELECT ProductQuantityInStock, ProductID FROM Products WHERE ProductName ='" + row.Cells["colItemName"].Value.ToString() + "'";
SqlCommand sqlcom2 = new SqlCommand(SelectQinStock, sqlcon1);
SqlDataReader dr;
dr = sqlcom2.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
QinStock = Convert.ToInt32((dr["ProductQuantityInStock"]));
QPurchased = Convert.ToInt32(row.Cells["colItemQuantity"].Value);
QLeft = QinStock - QPurchased;
ProductID = Convert.ToInt32((dr["ProductID"]));
SqlConnection sqlcon3 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon3.Open();
string cmd = "UPDATE Products SET ProductQuantityInStock ='" + QLeft.ToString() + "' WHERE ProductID ='" + ProductID.ToString() + "'";
SqlCommand sqlcom3 = new SqlCommand(cmd, sqlcon3);
sqlcom3.ExecuteNonQuery();
sqlcon3.Close();
}
}
//Add Loyalty Points
//Get Loyalty Membership
try
{
SqlConnection sqlcon5 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon5.Open();
string GetMembership = "SELECT isMember From Customers Where CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom5 = new SqlCommand(GetMembership, sqlcon5);
SqlDataReader dr5;
dr5 = sqlcom5.ExecuteReader();
if (dr5.HasRows)
{
while (dr5.Read())
{
isMember = Convert.ToInt32((dr5["isMember"]));
}
}
dr5.Close();
sqlcon5.Close();
}
catch
{
isMember = 0;
}
//Get Current Points
try
{
SqlConnection sqlcon9 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon9.Open();
string GetPoints = "SELECT LoyaltyPointsAvailable FROM Customers WHERE CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom9 = new SqlCommand(GetPoints, sqlcon9);
SqlDataReader dr9;
dr9 = sqlcom9.ExecuteReader();
if (dr9.HasRows)
{
while (dr9.Read())
{
CurrentPoints = Convert.ToDecimal((dr9["LoyaltyPointsAvailable"]));
}
}
dr9.Close();
sqlcon9.Close();
}
catch
{
}
if (isMember == 1)
{
decimal PointsReceived;
decimal FinalPoints;
PointsReceived = Convert.ToDecimal(row.Cells["colLinePrice"].Value) * Convert.ToDecimal(Globals_Class.loyaltyPointsPercentage);
FinalPoints = CurrentPoints + PointsReceived;
//Add Points
SqlConnection sqlcon66 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon66.Open();
string UpdatePoints = "UPDATE Customers SET LoyaltyPointsAvailable ='" + FinalPoints.ToString() + "' WHERE CustomerFullName ='" + SelectedCustomer.ToString() + "'";
SqlCommand sqlcom66 = new SqlCommand(UpdatePoints, sqlcon66);
sqlcom66.ExecuteNonQuery();
sqlcon66.Close();
}
else
{
}
}
//Calculate Sale Total
decimal sum = 0;
for (int i = 0; i < dataGridView1.Rows.Count; ++i)
{
sum += Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value);
}
MetroFramework.MetroMessageBox.Show(this, "The New Sale was Captured Successfully! The Total Cost of this Sale is:" + "\n" + "R" + " " + sum.ToString() + " ", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
this.dataGridView1.Rows.Clear();
cbxCustomerName.Text = "";
cbxCustomerName.Enabled = true;
//Add To SalesMAde by Employee
//Get Employee Instance
SqlConnection sqlcons = new SqlConnection(Globals_Class.ConnectionString);
sqlcons.Open();
string cmds = "SELECT EmployeeName From Employees WHERE UserName ='******'";
SqlCommand sqlcoms = new SqlCommand(cmds, sqlcons);
SqlDataReader rs;
rs = sqlcoms.ExecuteReader();
if (rs.HasRows)
{
while (rs.Read())
{
EmployeeName = (rs["EmployeeName"].ToString());
}
}
rs.Close();
sqlcons.Close();
//Get Current Amount of Sales
SqlConnection sql = new SqlConnection(Globals_Class.ConnectionString);
sql.Open();
string sqlcmd = "SELECT TotalSalesMade FROM SalesMade WHERE EmployeeName ='" + EmployeeName.ToString() + "'";
SqlCommand sqlcomss = new SqlCommand(sqlcmd, sql);
SqlDataReader sqlreader = sqlcomss.ExecuteReader();
if (sqlreader.HasRows)
{
while (sqlreader.Read())
{
SalesMadeSoFar = Convert.ToInt32((sqlreader["TotalSalesMade"]));
}
}
sqlreader.Close();
sql.Close();
int TotalSalesMadeSoFar = SalesMadeSoFar + MadeSale;
//Update TotalAmount of Sales Made
SqlConnection sqlz = new SqlConnection(Globals_Class.ConnectionString);
sqlz.Open();
string cmdz = "UPDATE SalesMade SET TotalSalesMade = @TotalAmountofSalesMade WHERE EmployeeName ='" + EmployeeName.ToString() + "'";
SqlCommand sqlcomz = new SqlCommand(cmdz, sqlz);
sqlcomz.Parameters.Add(new SqlParameter("@TotalAmountofSalesMade", TotalSalesMadeSoFar));
sqlcomz.ExecuteNonQuery();
sqlz.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "An Error Occurred Whilst Capturing your Sale Information!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.dataGridView1.Rows.Clear();
cbxCustomerName.Enabled = true;
}
}