private void Frmmainmenu_Load(object sender, EventArgs e)
{
DatabaseAssist.ConnectToLexlets.StateChange += ConnectToLexlets_StateChange;
this.Size = Screen.PrimaryScreen.WorkingArea.Size;
menuTop.Size = new Size(1000, 50);
lbldatabase.Text = "Using " + DatabaseAssist.GetDatabaseName() + " Database";
lblUser.Text = "User : " + User.Username.ToString();
Forminpanel(new FrmOverview());
}
private void cmbMaterial_Leave(object sender, EventArgs e)
{
try
{
materialId = DatabaseAssist.GetOneCellValue("SELECT MaterialID FROM vwMaterials WHERE CategoryName = @param AND ColourName = @param2 AND Description = @param3", cmbCategory.SelectedItem.ToString(), cmbColour.SelectedItem.ToString(), cmbMaterial.SelectedItem.ToString(), "MaterialID");
}
catch
{
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
if (validateEntry() != 8)
{
MessageBox.Show("Please Fill In All Fields Correctly");
}
else
{
byte[] imagebt = null;
FileStream fstream = new FileStream(txtImagePath.Text, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fstream);
imagebt = br.ReadBytes((int)fstream.Length);
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
sql = "INSERT into tblMaterials (Category, ColourID, Description, QTYinStock, LowLevelWarning, CostPerItem, SizeOnBracelet, Image, SupplierID) VALUES (@category, @colourID, @description, @qty, @lowlevel, @costperitem, @sizeon, @image, @supplierID)";
if (DatabaseAssist.ConnectToDatabase() == true)
{
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@category", cmbCategory.SelectedValue.ToString());
command.Parameters.AddWithValue("@colourID", cmbColour.SelectedValue.ToString());
command.Parameters.AddWithValue("@description", descriptionTextBox.Text);
command.Parameters.AddWithValue("@qty", qTYinStockTextBox.Text);
command.Parameters.AddWithValue("@lowlevel", lowLevelWarningTextBox.Text);
command.Parameters.AddWithValue("@costPerItem", costPerItemTextBox.Text);
command.Parameters.AddWithValue("@sizeon", sizeonBraceletTextBox.Text);
command.Parameters.AddWithValue("@image", imagebt);
command.Parameters.AddWithValue("@supplierID", (cmbSupplier.SelectedItem as ComboBoxItem).Value.ToString());
int i = command.ExecuteNonQuery();
command.Dispose();
DatabaseAssist.ConnectToLexlets.Close();
if (i != 0)
{
User.AddToUserLog("Added Material", User.Username + " Added " + cmbCategory.SelectedText + " " + descriptionTextBox.Text + " To the system");
DialogResult result = MessageBox.Show("Material Saved - Would you like to add another?", "Success", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
ClearAll();
}
else
{
this.Close();
}
}
else
{
MessageBox.Show("Error Please Try Again", "Error");
}
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
DatabaseAssist.ConnectToDatabase();
SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblUsers SET AccessLevel = @access Where UserId = @user", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@access", cmbAccess.SelectedIndex + 1);
adapter.SelectCommand.Parameters.AddWithValue("@user", lblUserId.Text);
adapter.SelectCommand.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
MessageBox.Show("User Access Level Updated", "Success");
}
private void FillCustomers()
{
flowLayoutPanelCustomers.Controls.Clear();
DataTable customers = DatabaseAssist.CreateDataTable("SELECT * FROM vwTopCustomers");
for (int i = 0; i <= 9; i++)
{
CreateLabel((i + 1).ToString().PadRight(5) + customers.Rows[i]["FirstName"].ToString() + " " + customers.Rows[i]["Surname"].ToString(), flowLayoutPanelCustomers, "Customer ID : " + customers.Rows[i]["CustomerId"].ToString(), "BlackAlignLeft");
CreateLabel("£" + customers.Rows[i]["Total Spent"].ToString(), flowLayoutCustomersSpent, "None", "BlackAlignLeft");
}
}
private Dictionary <string, double> GetMonthOutgoingByCategory(int monthNum)
{
Dictionary <String, Double> month = new Dictionary <string, double>();
DataTable test = DatabaseAssist.CreateDataTable("Select Category, SUM (Amount) From tblOutgoing Where MONTH(Date) = @param GROUP BY Category ", monthNum);
for (int i = 0; i < test.Rows.Count; i++)
{
month.Add(test.Rows[i][0].ToString(), Convert.ToDouble(test.Rows[i][1]));
}
return(month);
}
private void AlterIdFromInvoices(int newid, int oldId)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlCommand com = new SqlCommand("Update tblInvoiceData set CustomerId = @newId WHERE CustomerId = @oldId", DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@newId", newid);
com.Parameters.AddWithValue("@oldId", oldId);
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void CopyToOldAdresses(int id, string add1, string add2, string postcode)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlCommand com = new SqlCommand("INSERT INTO tblCustomersOtherAdresses VALUES(@id, @add1, @add2, @postcode)", DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@id", id);
com.Parameters.AddWithValue("@add1", add1);
com.Parameters.AddWithValue("@add2", add2);
com.Parameters.AddWithValue("@postcode", postcode);
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void BtnUnlock_Click(object sender, EventArgs e)
{
DatabaseAssist.ConnectToDatabase();
SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblUsers SET AccountLocked = @locked, FailedLoginAttempts = @attempts Where UserId = @user", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@locked", "false");
adapter.SelectCommand.Parameters.AddWithValue("@attempts", 0);
adapter.SelectCommand.Parameters.AddWithValue("@user", lblUserId.Text);
adapter.SelectCommand.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
MessageBox.Show("Account unlocked", "Success");
LoadUser();
}
private void RemoveRow(int invoice, int sku)
{
SqlCommand cmd = new SqlCommand("DELETE From tblInvoiceProducts WHERE InvoiceNumber = @invoice AND SKU = @sku", DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@invoice", invoice);
cmd.Parameters.AddWithValue("@sku", sku);
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd.ExecuteNonQuery();
cmd.Dispose();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
User.AddToUserLog("Delete Material", User.Username + " Deleted Material (" + cmbCategory.SelectedItem.ToString() + " - " + descriptionTextBox.Text + ")");
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter adapter = new SqlDataAdapter("Delete From tblMaterials WHERE MaterialID = @id", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text);
adapter.SelectCommand.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
MessageBox.Show("Material Deleted");
ClearAll();
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter adapter = new SqlDataAdapter("Delete From tblUsers WHERE UserId = @id", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@id", lblUserId.Text);
adapter.SelectCommand.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
MessageBox.Show(cmbUser.SelectedItem.ToString() + " has been removed", "User Deleted");
pnlUserInfo.Visible = false;
cmbUser.Text = "";
Helper.FillCombo(cmbUser, "Select Username from tblUsers", "Username");
}
}
private void AddNewUserToDatabase(string hashed, string salt)
{
DatabaseAssist.ConnectToDatabase();
SqlCommand command = new SqlCommand(@"INSERT into tblUsers (Username, Password, AccessLevel, FailedLoginAttempts, AccountLocked, Salt, Email) VALUES (@user, @password, @access, @failed, @locked, @salt, @email)", DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@user", txtUsername.Text.ToCamelCase());
command.Parameters.AddWithValue("@password", hashed);
command.Parameters.AddWithValue("@access", 3);
command.Parameters.AddWithValue("@failed", 0);
command.Parameters.AddWithValue("@locked", "false");
command.Parameters.AddWithValue("@salt", salt);
command.Parameters.AddWithValue("@email", txtEmail.Text.ToLower());
command.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
private void DataGridEditMaterial_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
int id = Convert.ToInt32(dataGridEditMaterial.Rows[e.RowIndex].Cells["MaterialId"].Value.ToString());
BtnCancel.Enabled = true;
BtnUpdate.Enabled = true;
BtnDelete.Enabled = true;
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlCommand cmd = DatabaseAssist.ConnectToLexlets.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tblMaterials INNER JOIN tblColours on tblColours.ColourId = tblMaterials.ColourID INNER JOIN tblMaterialCategory on tblMaterialCategory.CategoryId = tblMaterials.Category INNER JOIN tblSuppliers on tblSuppliers.SupplierId = tblMaterials.SupplierID WHERE tblMaterials.MaterialID= @id ";
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
materialIdTextBox.Text = dr["MaterialID"].ToString();
cmbCategory.Text = dr["CategoryName"].ToString();
cmbColour.Text = dr["ColourName"].ToString();
descriptionTextBox.Text = dr["Description"].ToString();
qTYinStockTextBox.Text = dr["QTYinStock"].ToString();
lowLevelWarningTextBox.Text = dr["LowLevelWarning"].ToString();
costPerItemTextBox.Text = dr["CostPerItem"].ToString();
sizeonBraceletTextBox.Text = dr["SizeOnBracelet"].ToString();
cmbSupplier.Text = dr["SupplierName"].ToString();
Image x = (Bitmap)((new ImageConverter()).ConvertFrom(dr["Image"]));
picLoadImage.Image = x;
}
DatabaseAssist.ConnectToLexlets.Close();
gbxEditMaterial.Enabled = true;
gbxFindMaterial.Enabled = false;
btnChange.Visible = true;
}
}
catch
{
MessageBox.Show("Please Select a Row, Not a Column");
return;
}
}
static public void GetMonthlyFigures(int monthNum, int year, FlowLayoutPanel panel, string BreifOrFull, MonthlyReports reports)
{
double income = 0;
double outgoing = 0;
double profit;
Dictionary <string, Double> expenses = GetMonthOutgoingByCategory(monthNum, year);
if (monthNum == 13)
{
income = GetAccounts("SELECT Amount From tblIncome where YEAR(Date) = @year", monthNum, year);
outgoing = GetAccounts("SELECT Amount From tblOutgoing where YEAR(Date) = @year", monthNum, year);
//CreateHeaderLabel("Total", fl13);
}
else
{
income = GetAccounts("SELECT Amount From tblIncome where MONTH(Date) = @month AND YEAR(Date) = @year", monthNum, year);
outgoing = GetAccounts("SELECT Amount From tblOutgoing where MONTH(Date) = @month AND YEAR(Date) = @year", monthNum, year);
}
profit = income - outgoing;
profit = Math.Round(profit, 2);
reports.totalIn += income;
reports.totalOut += outgoing;
reports.orders += GetSalesThisMonth(monthNum, year);
reports.itemsSold += (ItemsSoldInMonth(monthNum, year));
Helper.CreateLabel(GetSalesThisMonth(monthNum, year).ToString(), panel, "None", "BlackAlignCenter");
Helper.CreateLabel(ItemsSoldInMonth(monthNum, year).ToString(), panel, "None", "BlackAlignCenter");
Helper.CreateLabel("£" + income.ToString(), panel, "None", "BlackAlignCenter12");
if (BreifOrFull == "Full")
{
Helper.CreateLabel("", panel, "None", "BlackAlignCenter12");
DataTable expenseCategory = DatabaseAssist.CreateDataTable("Select CategoryName From tblOutgoingCategory Order by CategoryName Asc", 1, 1);
Helper.ChangeBackgoundColour = false;
for (int i = 0; i < expenseCategory.Rows.Count; i++)
{
double cost = Convert.ToDouble(DatabaseAssist.GetOneCellValue("Select Category, SUM(Amount) as Amount From tblOutgoing Where MONTH(Date) = @param And YEAR(Date) =@param2 And Category = @param3 GROUP BY Category ", monthNum, year, expenseCategory.Rows[i]["CategoryName"].ToString(), "Amount"));
reports.AddToTotal(i, cost);
Helper.CreateLabel("£" + cost, panel, "None", "BlackAlignLeftItalic");
}
}
Helper.CreateLabel("£" + outgoing, panel, "None", "");
Helper.CreateLabel("£" + profit, panel, "None", "MoneyRedGreen");
}
private void LoadUser()
{
if (cmbUser.SelectedItem != null)
{
DataTable selectedUser = DatabaseAssist.CreateDataTable("Select * from tblUsers Where Username=@param", cmbUser.SelectedItem.ToString());
lblAccessLevel.Text = $"Current Access Level : { selectedUser.Rows[0]["AccessLevel"].ToString() }";
lblFailedLoginAttemps.Text = selectedUser.Rows[0]["FailedLoginAttempts"].ToString();
lblUserId.Text = selectedUser.Rows[0]["UserId"].ToString();
lblAccessLevel.Visible = true;
SetAccountStateLabel(Convert.ToBoolean(selectedUser.Rows[0]["AccountLocked"]));
}
else
{
MessageBox.Show("Please Select a User", "No user selected");
}
}
private void lblNewUser_Click(object sender, EventArgs e)
{
if (cmbDatabase.SelectedItem == null)
{
MessageBox.Show("Please select a database to register too", "No database selected");
return;
}
DatabaseAssist.SetConnectionStringAsync(cmbDatabase.SelectedItem.ToString());
if (DatabaseAssist.ConnectToDatabase() == true)
{
FrmRegisterNewUser newUser = new FrmRegisterNewUser();
newUser.ShowDialog();
}
}
private void FillLowStock()
{
flowLayoutLowStockQty.Controls.Clear();
string sql = "SELECT TOP 10 tblColours.ColourName, tblMaterials.MaterialId, tblMaterials.Description, tblMaterials.QTYinStock," +
"tblSuppliers.SupplierName FROM (((tblMaterials INNER JOIN tblMaterialCategory on tblMaterialCategory.CategoryId = tblMaterials.Category)" +
"INNER JOIN tblColours on tblColours.ColourId = tblMaterials.ColourID)" +
"INNER JOIN tblSuppliers on tblSuppliers.SupplierId = tblMaterials.SupplierId) where tblmaterials.QTYinStock < tblmaterials.LowLevelWarning AND tblMaterials.LowLevelWarning > -1 ORDER BY tblMaterials.QtyinStock asc";
DataTable lowStock = DatabaseAssist.CreateDataTable(sql);
for (int i = 0; i < lowStock.Rows.Count; i++)
{
CreateLabel(lowStock.Rows[i]["Description"].ToString(), flowLayoutLowStockItem, "Material Id : " + lowStock.Rows[i]["MaterialId"].ToString() + "\nColour : " + (lowStock.Rows[i]["ColourName"].ToString()) + "\nBuy From " + lowStock.Rows[i]["SupplierName"].ToString(), "BlackAlignLeft");
CreateLabel(lowStock.Rows[i]["QTYinStock"].ToString().PadRight(3) + " Left", flowLayoutLowStockQty, "None", "BlackAlignLeft");
}
}
private void UpdateInvoiceData(int invoice)
{
double invoiceTotal = Convert.ToDouble(DatabaseAssist.GetOneCellValue("Select InvoiceTotal From tblInvoiceData " +
"WHERE InvoiceNumber = @param", invoice, "InvoiceTotal"));
double newPrice = invoiceTotal - Convert.ToDouble(Helper.RemovePoundSign(lblRefundTotal.Text));
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlCommand cmd3 = new SqlCommand("UPDATE tblInvoiceData set InvoiceTotal = @price WHERE InvoiceNumber =@invoice", DatabaseAssist.ConnectToLexlets);
cmd3.Parameters.AddWithValue("@invoice", invoice);
cmd3.Parameters.AddWithValue("@price", newPrice);
cmd3.ExecuteNonQuery();
cmd3.Dispose();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
if (ValidateEntries() > 0)
{
MessageBox.Show("Please correct highlighted fields", "Error");
}
else
{
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
sql = "INSERT into tblCustomers VALUES (@fname, @sname, @add1, @add2, @pcode, @email, @sub)";
if (DatabaseAssist.ConnectToDatabase() == true)
{
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@fname", TxtFirstName.Text);
command.Parameters.AddWithValue("@sname", TxtSurName.Text);
command.Parameters.AddWithValue("@add1", TxtAdd1.Text);
command.Parameters.AddWithValue("@add2", TxtAdd2.Text);
command.Parameters.AddWithValue("@pcode", TxtPostCode.Text);
command.Parameters.AddWithValue("@email", TxtEmail.Text);
command.Parameters.AddWithValue("@sub", CmbSub.SelectedItem);
int i = command.ExecuteNonQuery();
command.Dispose();
DatabaseAssist.ConnectToLexlets.Close();
if (i != 0)
{
MessageBox.Show("Customer Saved");
TxtFirstName.Text = "";
TxtSurName.Text = "";
TxtAdd1.Text = "";
TxtAdd2.Text = "";
TxtEmail.Text = "";
TxtPostCode.Text = "";
CmbSub.Text = "";
}
else
{
MessageBox.Show("Error");
}
}
}
}
private void BtnRegister_Click(object sender, EventArgs e)
{
string salt = Helper.CreateSalt(20);
string hashedPassword = Helper.GenerateSHA256HASH(txtPassword2.Text, salt);
string originalDatabase = DatabaseAssist.ConnectionName;
string testDatabase = originalDatabase == "Home" ? "HomeTest" : "WorkTest";
AddNewUserToDatabase(hashedPassword, salt);
//DatabaseAssist.SetConnectionString(testDatabase);
//AddNewUserToDatabase(hashedPassword, salt);
DatabaseAssist.SetConnectionStringAsync(originalDatabase);
this.Enabled = false;
MessageBox.Show("Access Level by defult is set to minimum\n\nPlease contact administrator to request a higher access level.", "User Created", MessageBoxButtons.OK);
this.Close();
}
private void button2_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtCheck.Text))
{
return;
}
else
{
listSKU.Items.Clear();
DataTable sku = DatabaseAssist.CreateDataTable("Select tblProductData.Description, tblProductContains.SKU from tblProductData inner join tblProductContains on tblProductData.SKU = tblProductContains.SKU Where tblProductContains.MaterialId = @param", Convert.ToInt32(txtCheck.Text), 1);
//Select tblProductContains.SKU tblProductData.Description from tblProductContains Where MaterialId = @param"
for (int i = 0; i < sku.Rows.Count; i++)
{
listSKU.Items.Add(("SKU : " + sku.Rows[i][1]).ToString() + " - " + (sku.Rows[i][0]).ToString());
}
}
}
private void FillCombo(ComboBox combo, string sql, string columnName)
{
combo.Items.Clear();
SqlDataAdapter adapter = new SqlDataAdapter(sql, DatabaseAssist.ConnectToLexlets);
DataTable dt = new DataTable();
if (DatabaseAssist.ConnectToDatabase() == true)
{
adapter.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
combo.Items.Add(dt.Rows[i][columnName].ToString());
}
DatabaseAssist.ConnectToLexlets.Close();
}
}
public void GetColID(ComboBox colID, Label labelID)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter da = new SqlDataAdapter("Select ColourID FROM tblColours WHERE ColourName = @name", DatabaseAssist.ConnectToLexlets);
da.SelectCommand.Parameters.AddWithValue("@name", colID.SelectedItem);
DataTable dt = new DataTable();
da.Fill(dt);
labelID.Text = (dt.Rows[0]["ColourID"].ToString());
DatabaseAssist.ConnectToLexlets.Close();
da.Dispose();
dt.Dispose();
dt.Clear();
}
}
private void InsertSaleIntoInvoiceData(int id, DataTable orders)
{
double fee;
double coupon;
if (orders.Rows[0]["PaymentMethod"].ToString() == "PayPal")
{
fee = (3.4 / 100) * Convert.ToDouble(orders.Rows[0]["Total"]) + 0.20;
}
else
{
fee = (1.4 / 100) * Convert.ToDouble(orders.Rows[0]["Total"]) + 0.20;
}
fee = Math.Round(fee, 2);
try
{
coupon = Convert.ToDouble(orders.Rows[0]["CouponCode"]);
}
catch
{
coupon = 0;
}
string sqlquery = "insert into tblInvoiceData VALUES (@invoice, @id, @sale, @Shipping, @PayMethod, @addCost, @comment, @percent, @gbp, @total, @fee)";
SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@invoice", Convert.ToInt32(orders.Rows[0]["Order"]));
com.Parameters.AddWithValue("@id", id);
com.Parameters.AddWithValue("@sale", Convert.ToDateTime(orders.Rows[0]["Date"]));
com.Parameters.AddWithValue("@Shipping", Convert.ToDouble(orders.Rows[0]["Shipping"]));
com.Parameters.AddWithValue("@PayMethod", orders.Rows[0]["PaymentMethod"]);
com.Parameters.AddWithValue("@addCost", 0);
com.Parameters.AddWithValue("@comment", "");
com.Parameters.AddWithValue("@percent", coupon);
com.Parameters.AddWithValue("@gbp", Convert.ToDouble(orders.Rows[0]["Discount"]));
com.Parameters.AddWithValue("@total", (Convert.ToDouble(orders.Rows[0]["Subtotal"]) + Convert.ToDouble(orders.Rows[0]["Shipping"])));
com.Parameters.AddWithValue("@fee", fee);
if (DatabaseAssist.ConnectToDatabase() == true)
{
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void RemoveFromInvoices()
{
SqlCommand cmd = new SqlCommand("DELETE FROM tblInvoiceData WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets);
SqlCommand cmd2 = new SqlCommand("DELETE FROM tblInvoiceProducts WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice);
cmd2.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice);
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
cmd.Dispose();
cmd2.Dispose();
}
}
private string GetGiftBoxPrice(string giftname)
{
string price = "";
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter da = new SqlDataAdapter("Select GiftBoxPrice FROM tblInvoiceProducts WHERE GiftBox = @box", DatabaseAssist.ConnectToLexlets);
da.SelectCommand.Parameters.AddWithValue("@box", giftname);
DataTable dt = new DataTable();
da.Fill(dt);
price = (dt.Rows[0]["GiftBoxPrice"].ToString());
DatabaseAssist.ConnectToLexlets.Close();
da.Dispose();
dt.Dispose();
}
return(price);
}
private string HashPassword()
{
string salt = "";
DataTable user = DatabaseAssist.CreateDataTable("Select * FROM tblUsers WHERE Username =@param", txtUser.Text.ToLower());
try
{
salt = user.Rows[0]["Salt"].ToString();
}
catch
{
return(string.Empty);
}
//string hash = user.Rows[0]["Password"].ToString();
string password = txtPassword.Text;
return(Helper.GenerateSHA256HASH(password, salt));
}
private void FillCombo(ComboBox combo, string sql, string column, string param, string param2)
{
combo.Items.Clear();
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter da = new SqlDataAdapter(sql, DatabaseAssist.ConnectToLexlets);
da.SelectCommand.Parameters.AddWithValue("param", param);
da.SelectCommand.Parameters.AddWithValue("param2", param2);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
combo.Items.Add(dt.Rows[i][column]);
}
}
}
void FillPanels()
{
string sku;
string category;
string description;
string colour;
string textToSplit;
double price;
int qtySold;
int index;
Image image;
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter da = new SqlDataAdapter("select sku, category, Description, sellprice, Image, QTYSold from tblProductData", DatabaseAssist.ConnectToLexlets);
DataTable dt = new DataTable();
da.Fill(dt);
DatabaseAssist.ConnectToLexlets.Close();
for (int i = 0; i < dt.Rows.Count; i++)
{
image = (Bitmap)((new ImageConverter()).ConvertFrom(dt.Rows[i]["Image"]));
sku = (dt.Rows[i]["SKU"].ToString());
category = (dt.Rows[i]["Category"].ToString());
textToSplit = (dt.Rows[i]["Description"].ToString());
try
{
index = textToSplit.IndexOf("-");
description = textToSplit.Substring(0, index - 1);
colour = textToSplit.Substring(index + 2);
}
catch
{
description = textToSplit;
colour = "";
}
price = Convert.ToDouble(dt.Rows[i]["SellPrice"]);
qtySold = Convert.ToInt32(dt.Rows[i]["QTYSold"]);
CreatePanel(flowLayoutPanelItemsDetailed, image, sku, category, description, colour, price, qtySold);
}
}
}