private void SaveToCollectionContains()
{
List <int> SKU = new List <int>();
string sql = "";
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
foreach (ComboBox combo in flowLayoutSKU.Controls)
{
SKU.Add(Convert.ToInt32(combo.SelectedItem));
}
for (int i = 0; i < SKU.Count; i++)
{
sql = "INSERT into tblCollectionContains VALUES (@collectionsku, @productsku)";
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@collectionsku", Convert.ToInt32(TxtSKU.Text));
command.Parameters.AddWithValue("@productsku", SKU[i]);
if (DatabaseAssist.ConnectToDatabase() == true)
{
command.ExecuteNonQuery();
}
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void UpdateRow(int invoice, int sku, int qtyToRemove)
{
int quantity = Convert.ToInt16(DatabaseAssist.GetOneCellValue("Select Quantity From tblInvoiceProducts " +
"WHERE InvoiceNumber = @param AND SKU = @param2", invoice, sku, "Quantity"));
quantity -= qtyToRemove;
UpdateInvoiceData(invoice);
SqlCommand cmd = new SqlCommand("UPDATE tblInvoiceProducts set Quantity = @quantity WHERE InvoiceNumber =@invoice AND SKU =@sku", DatabaseAssist.ConnectToLexlets);
SqlCommand cmd2 = new SqlCommand("UPDATE tblRefundedInvoiceProducts set Quantity = @quantity WHERE InvoiceNumber =@invoice AND SKU =@sku", DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@invoice", invoice);
cmd.Parameters.AddWithValue("@sku", sku);
cmd.Parameters.AddWithValue("@quantity", quantity);
cmd2.Parameters.AddWithValue("@invoice", invoice);
cmd2.Parameters.AddWithValue("@sku", sku);
cmd2.Parameters.AddWithValue("@quantity", qtyToRemove);
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd.Dispose();
cmd2.Dispose();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void SaveTransaction(string sql)
{
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
if (DatabaseAssist.ConnectToDatabase() == true)
{
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@date", Convert.ToDateTime(dateTimePicker1.Value.ToShortDateString()));
command.Parameters.AddWithValue("@category", cmbCategory.SelectedItem.ToString());
command.Parameters.AddWithValue("@description", txtDescription.Text);
command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(txtAmount.Text)));
command.Parameters.AddWithValue("@paymentmethod", cmbMethod.SelectedItem.ToString());
if (chkReceipt.Checked)
{
command.Parameters.AddWithValue("@receipt", "Yes");
}
else
{
command.Parameters.AddWithValue("@receipt", "No");
}
command.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
public void AddToOutgoings()
{
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
DateTime today = DateTime.Today;
sql = "INSERT into tblOutgoing (Date, Category, Description, Amount, PaymentMethod) VALUES (@date, @category, @description, @amount, @method)";
if (DatabaseAssist.ConnectToDatabase() == true)
{
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@date", today);
command.Parameters.AddWithValue("@method", CurrentCustomer.PaymentMethod);
if (radFullRefund.Checked)
{
string description = "Invoice No: " + txtInvoice.Text + " - " + lblName.Text;
command.Parameters.AddWithValue("@description", description);
command.Parameters.AddWithValue("@category", "Full Refund");
command.Parameters.AddWithValue("@amount", Helper.RemovePoundSign(lblInvoiceTotal.Text));
}
else if (radPartRefund.Checked)
{
string description = "Invoice No: " + txtInvoice.Text + " - " + lblName.Text;
command.Parameters.AddWithValue("@description", description);
command.Parameters.AddWithValue("@category", "Part Refund");
command.Parameters.AddWithValue("@amount", Helper.RemovePoundSign(lblRefundTotal.Text));
}
command.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void SaveToCollectionData()
{
byte[] imagebt = null;
FileStream fstream = new FileStream(TxtPicPath.Text, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fstream);
imagebt = br.ReadBytes((int)fstream.Length);
string sql = "INSERT into tblProductData (SKU, Category, Description, CostToMake, SellPrice, Image, QTYSold, DateAdded) VALUES (@sku, @category, @description, @cost, @sell, @image, @sold, @added)";
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@sku", Convert.ToInt32(TxtSKU.Text));
command.Parameters.AddWithValue("@category", CmbCategory.SelectedItem.ToString());
command.Parameters.AddWithValue("@description", TxtDescription.Text);
command.Parameters.AddWithValue("@cost", Helper.RemovePoundSign(TxtCost.Text));
command.Parameters.AddWithValue("@sell", Helper.RemovePoundSign(TxtPrice.Text));
command.Parameters.AddWithValue("@image", imagebt);
command.Parameters.AddWithValue("@sold", 0);
command.Parameters.AddWithValue("@added", DateTime.Now);
if (DatabaseAssist.ConnectToDatabase() == true)
{
command.ExecuteNonQuery();
}
DatabaseAssist.ConnectToLexlets.Close();
}
public void AddToRefunds()
{
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
DateTime today = DateTime.Today;
sql = "INSERT into tblRefunds VALUES (@date, @custID, @invoice, @refundtype, @amount, @reason, @refundmethod)";
if (DatabaseAssist.ConnectToDatabase() == true)
{
command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
command.Parameters.AddWithValue("@date", today);
command.Parameters.AddWithValue("@custID", CurrentCustomer.CustomerID);
command.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice);
command.Parameters.AddWithValue("@reason", txtReason.Text);
command.Parameters.AddWithValue("@refundmethod", CurrentCustomer.PaymentMethod);
if (radFullRefund.Checked)
{
command.Parameters.AddWithValue("@refundtype", "Full Refund");
command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(lblInvoiceTotal.Text)));
}
else if (radPartRefund.Checked)
{
command.Parameters.AddWithValue("@refundtype", "Part Refund");
command.Parameters.AddWithValue("@amount", Convert.ToDouble(Helper.RemovePoundSign(lblRefundTotal.Text)));
}
command.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void LoadData(string sql)
{
string userID = "1";
dataGridView1.DataSource = null;
dataGridView1.Update();
dataGridView1.Refresh();
SqlCommand cmd;
SqlDataAdapter adapter = new SqlDataAdapter();
try
{
if (cmbUsers.SelectedItem != null)
{
userID = DatabaseAssist.GetOneCellValue("SELECT UserId FROM tblUsers WHERE Username = @param", cmbUsers.SelectedItem.ToString(), "UserId");
}
}
catch
{
}
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@user", Convert.ToInt32(userID));
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds, "tbl1");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "tbl1";
DatabaseAssist.ConnectToLexlets.Close();
}
}
public void GetCostandMaterialID(Label cost, Label id, Label cat, Label Col, ComboBox desc)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter da = new SqlDataAdapter("Select MaterialId, CostPerItem FROM tblMaterials WHERE Category = @category AND ColourID = @colour AND Description =@desc", DatabaseAssist.ConnectToLexlets);
da.SelectCommand.Parameters.AddWithValue("@category", Convert.ToInt32(cat.Text));
da.SelectCommand.Parameters.AddWithValue("@colour", Convert.ToInt32(Col.Text));
da.SelectCommand.Parameters.AddWithValue("@desc", desc.SelectedItem.ToString());
DataTable dt = new DataTable();
try
{
da.Fill(dt);
cost.Text = (dt.Rows[0]["CostPerItem"].ToString());
id.Text = (dt.Rows[0]["MaterialId"].ToString());
}
catch
{
MessageBox.Show("No Entries Found");
}
DatabaseAssist.ConnectToLexlets.Close();
da.Dispose();
dt.Dispose();
}
}
private void InsertIntoInvoiceProducts(DataTable orders)
{
for (int i = 0; i < orders.Rows.Count; i++)
{
string GiftBox = orders.Rows[i]["GiftBox"].ToString();
double giftPrice;
string sqlquery = "insert into tblInvoiceProducts VALUES (@invoice, @sku, @size, @qty, @giftbox, @giftboxprice, @skuprice)";
SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets);
if (GiftBox == "Yes")
{
GiftBox = "Silver Box - Bracelet";
giftPrice = 4.50;
}
else
{
GiftBox = "No";
giftPrice = 0;
}
com.Parameters.AddWithValue("@invoice", Convert.ToInt32(orders.Rows[i]["Order"]));
com.Parameters.AddWithValue("@sku", Convert.ToInt32(orders.Rows[i]["SKU"]));
com.Parameters.AddWithValue("@size", orders.Rows[i]["Size"]);
com.Parameters.AddWithValue("@qty", Convert.ToInt32(orders.Rows[i]["Qty"]));
com.Parameters.AddWithValue("@GiftBox", GiftBox);
com.Parameters.AddWithValue("@GiftBoxprice", giftPrice);
com.Parameters.AddWithValue("@skuprice", Convert.ToDouble(orders.Rows[i]["ItemPrice"]));
if (DatabaseAssist.ConnectToDatabase() == true)
{
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
DatabaseAssist.UpdateProductQtySold(Convert.ToInt32(orders.Rows[i]["SKU"]), Convert.ToInt32(orders.Rows[i]["Qty"]), "Add");
}
}
private void BtnCopyToTable_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow dr in dataGridViewExcel.Rows)
{
string sqlquery = "insert into tblImportSales VALUES (@order, @fname, @sname, @add1, @add2, @email," +
"@date, @subtotal, @shipping, @total, @percent, @gbp, @payment, @sku, @qty, @price, @gift, @size, @postcode)";
SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@order", dr.Cells["Order ."].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@fname", dr.Cells["FirstName"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@sname", dr.Cells["SurName"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@add1", dr.Cells["Shipping Street Address"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@add2", dr.Cells["Shipping City"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@email", dr.Cells["Email Address"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@date", Convert.ToDateTime(dr.Cells["Order Date and Time Stamp"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@subtotal", Convert.ToDouble(dr.Cells["Subtotal"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@shipping", Convert.ToDouble(dr.Cells["Shipping Cost"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@total", Convert.ToDouble(dr.Cells["Total"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@percent", dr.Cells["Percent"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@gbp", Convert.ToDouble(dr.Cells["GBP"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@payment", dr.Cells["PaymentMethod"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@sku", dr.Cells["SKU"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@qty", dr.Cells["LineItem Qty"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@price", Convert.ToDouble(dr.Cells["LineItem Sale Price"].Value ?? DBNull.Value));
com.Parameters.AddWithValue("@gift", dr.Cells["GiftBox"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@size", dr.Cells["size"].Value ?? DBNull.Value);
com.Parameters.AddWithValue("@postcode", dr.Cells["Shipping Zip"].Value ?? DBNull.Value);
if (DatabaseAssist.ConnectToDatabase() == true)
{
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
MessageBox.Show("Table Has Been Inserted");
}
private void TxtByMatID_KeyUp(object sender, KeyEventArgs e)
{
string byMaterialID = txtByMatID.Text;
if (Helper.CheckTextBoxForString(txtByMatID.Text) == "notnumber")
{
MessageBox.Show("ID Numbers do not contain letters, Please Enter a Number", "Invalid Charactor");
txtByMatID.Text = "";
txtByMatID.Focus();
}
else
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter adapter = new SqlDataAdapter("select tblMaterials.MaterialID, tblMaterialCategory.CategoryName, tblColours.ColourName, tblMaterials.Description from tblMaterials INNER JOIN tblMaterialCategory ON tblMaterialCategory.CategoryID = tblMaterials.Category INNER JOIN tblColours ON tblColours.ColourID = tblMaterials.ColourID WHERE tblMaterials.MaterialID = @byid", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@byid", byMaterialID);
DataSet ds = new DataSet();
adapter.Fill(ds, "tblMaterials");
dataGridEditMaterial.DataSource = ds;
dataGridEditMaterial.DataMember = "tblMaterials";
dataGridEditMaterial.Columns["CategoryName"].HeaderText = "Category";
dataGridEditMaterial.Columns["CategoryName"].Width = 100;
dataGridEditMaterial.Columns["ColourName"].HeaderText = "Colour";
dataGridEditMaterial.Columns["ColourName"].Width = 125;
dataGridEditMaterial.Columns["Description"].Width = 175;
DatabaseAssist.ConnectToLexlets.Close();
}
}
}
private void AddToIncome(DataTable orders)
{
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
string invoice = orders.Rows[0]["Order"].ToString();
string fname = orders.Rows[0]["FirstName"].ToString();
string sname = orders.Rows[0]["Surname"].ToString();
string description = "Invoice No: " + invoice + " - " + fname + " " + sname;
double price;
double fee;
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);
price = (Convert.ToDouble(orders.Rows[0]["Subtotal"]) + Convert.ToDouble(orders.Rows[0]["Shipping"]));
try
{
price -= Convert.ToDouble(orders.Rows[0]["CouponCode"]);
}
catch
{
}
try
{
price -= Convert.ToDouble(orders.Rows[0]["CouponCode"]);
}
catch
{
}
price -= fee;
sql = "INSERT into tblincome VALUES (@date, @category, @description, @amount, @method, @receipt)";
SqlCommand com = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@date", Convert.ToDateTime(orders.Rows[0]["Date"]));
com.Parameters.AddWithValue("@category", "Sale");
com.Parameters.AddWithValue("@description", description);
com.Parameters.AddWithValue("@amount", price);
com.Parameters.AddWithValue("@method", orders.Rows[0]["PaymentMethod"]);
com.Parameters.AddWithValue("@receipt", "No");
if (DatabaseAssist.ConnectToDatabase() == true)
{
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void CopyToRefundTable()
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
if (radFullRefund.Checked == true)
{
SqlCommand cmd = new SqlCommand("INSERT tblRefundedInvoiceData SELECT * FROM tblInvoiceData WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets);
SqlCommand cmd2 = new SqlCommand("INSERT tblRefundedInvoiceProducts SELECT * FROM tblInvoiceProducts WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice);
cmd2.Parameters.AddWithValue("@invoice", CurrentCustomer.Invoice);
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
cmd.Dispose();
cmd2.Dispose();
}
else if (radPartRefund.Checked == true)
{
for (int i = 0; i < dgvRefunds.Rows.Count - 1; i++)
{
int invoice = (Convert.ToInt32(dgvRefunds.Rows[i].Cells[1].Value));
int sku = (Convert.ToInt16(dgvRefunds.Rows[i].Cells[2].Value));
int qtyToRemove = (Convert.ToInt16(dgvRefunds.Rows[i].Cells[7].Value));
int quantity = Convert.ToInt16(DatabaseAssist.GetOneCellValue("Select Quantity From tblInvoiceProducts " +
"WHERE InvoiceNumber = @param AND SKU = @param2", invoice, sku, "Quantity"));
int quantityLeft = quantity - qtyToRemove;
SqlCommand cmd = new SqlCommand("INSERT INTO tblRefundedInvoiceProducts SELECT * FROM tblInvoiceProducts WHERE InvoiceNumber = @invoice AND SKU=@sku", DatabaseAssist.ConnectToLexlets);
cmd.Parameters.AddWithValue("@invoice", invoice);
cmd.Parameters.AddWithValue("@sku", sku);
SqlCommand cmd2 = new SqlCommand("INSERT tblRefundedInvoiceData SELECT * FROM tblInvoiceData WHERE InvoiceNumber = @invoice", DatabaseAssist.ConnectToLexlets);
cmd2.Parameters.AddWithValue("@invoice", invoice);
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
UpdateRow(invoice, sku, qtyToRemove); // Updates invoice by reducing the quantity sold
if (quantityLeft == 0)
{
RemoveRow(invoice, sku); // Removes row as there is none left
}
}
}
}
}
}
private void LoadData()
{
dataGridView1.DataSource = null;
dataGridView2.DataSource = null;
dataGridView3.DataSource = null;
dataGridView4.DataSource = null;
dataGridView1.Update();
dataGridView2.Update();
dataGridView3.Update();
dataGridView4.Update();
dataGridView1.Refresh();
dataGridView2.Refresh();
dataGridView3.Refresh();
dataGridView4.Refresh();
SqlCommand cmd, cmd2, cmd3, cmd4;
SqlDataAdapter adapter = new SqlDataAdapter();
SqlDataAdapter adapter2 = new SqlDataAdapter();
SqlDataAdapter adapter3 = new SqlDataAdapter();
SqlDataAdapter adapter4 = new SqlDataAdapter();
if (DatabaseAssist.ConnectToDatabase() == true)
{
cmd = new SqlCommand("SELECT * FROM tblInvoiceData", DatabaseAssist.ConnectToLexlets);
cmd2 = new SqlCommand("SELECT * FROM tblInvoiceProducts", DatabaseAssist.ConnectToLexlets);
cmd3 = new SqlCommand("SELECT * FROM tblRefundedInvoiceData", DatabaseAssist.ConnectToLexlets);
cmd4 = new SqlCommand("SELECT * FROM tblRefundedInvoiceProducts", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand = cmd;
adapter2.SelectCommand = cmd2;
adapter3.SelectCommand = cmd3;
adapter4.SelectCommand = cmd4;
DataSet ds = new DataSet();
DataSet ds2 = new DataSet();
DataSet ds3 = new DataSet();
DataSet ds4 = new DataSet();
adapter.Fill(ds, "tbl1");
adapter2.Fill(ds2, "tbl2");
adapter3.Fill(ds3, "tbl3");
adapter4.Fill(ds4, "tbl4");
dataGridView1.DataSource = ds;
dataGridView2.DataSource = ds2;
dataGridView3.DataSource = ds3;
dataGridView4.DataSource = ds4;
dataGridView1.DataMember = "tbl1";
dataGridView2.DataMember = "tbl2";
dataGridView3.DataMember = "tbl3";
dataGridView4.DataMember = "tbl4";
DatabaseAssist.ConnectToLexlets.Close();
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlDataAdapter getcatID = new SqlDataAdapter("SELECT CategoryId FROM tblMaterialCategory WHERE CategoryName = @category", DatabaseAssist.ConnectToLexlets);
getcatID.SelectCommand.Parameters.AddWithValue("@category", cmbCategory.SelectedItem.ToString());
DataTable dt = new DataTable();
getcatID.Fill(dt);
int catid = Convert.ToInt32((dt.Rows[0]["CategoryId"]));
SqlDataAdapter getcolID = new SqlDataAdapter("SELECT ColourId FROM tblColours WHERE ColourName = @cols", DatabaseAssist.ConnectToLexlets);
getcolID.SelectCommand.Parameters.AddWithValue("@cols", cmbColour.SelectedItem.ToString());
DataTable dt1 = new DataTable();
getcolID.Fill(dt1);
int colid = Convert.ToInt32((dt1.Rows[0]["ColourId"]));
SqlDataAdapter getsupID = new SqlDataAdapter("SELECT SupplierId FROM tblSuppliers WHERE SupplierName = @sup", DatabaseAssist.ConnectToLexlets);
getsupID.SelectCommand.Parameters.AddWithValue("@sup", cmbSupplier.SelectedItem.ToString());
DataTable dt2 = new DataTable();
getsupID.Fill(dt2);
int supid = Convert.ToInt32((dt2.Rows[0]["SupplierId"]));
dt.Dispose();
dt1.Dispose();
dt2.Dispose();
SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblMaterials SET Category = @cat, ColourID = @cols, Description = @desc, QTYinStock = @qty, LowLevelWarning = @low, CostPerItem = @cost, SizeOnBracelet = @size, SupplierID = @supplier Where MaterialID = @id", DatabaseAssist.ConnectToLexlets);
adapter.SelectCommand.Parameters.AddWithValue("@cat", catid);
adapter.SelectCommand.Parameters.AddWithValue("@cols", colid);
adapter.SelectCommand.Parameters.AddWithValue("@desc", descriptionTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@qty", qTYinStockTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@Low", lowLevelWarningTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@cost", costPerItemTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@size", sizeonBraceletTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text);
adapter.SelectCommand.Parameters.AddWithValue("@supplier", supid);
adapter.SelectCommand.ExecuteNonQuery();
if (!String.IsNullOrEmpty(txtImagePath.Text))
{
byte[] imagebt = null;
FileStream fstream = new FileStream(txtImagePath.Text, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fstream);
imagebt = br.ReadBytes((int)fstream.Length);
SqlDataAdapter adapter1 = new SqlDataAdapter("UPDATE tblMaterials SET Image =@img Where MaterialID = @id", DatabaseAssist.ConnectToLexlets);
adapter1.SelectCommand.Parameters.AddWithValue("@img", imagebt);
adapter1.SelectCommand.Parameters.AddWithValue("@id", materialIdTextBox.Text);
adapter1.SelectCommand.ExecuteNonQuery();
}
DatabaseAssist.ConnectToLexlets.Close();
User.AddToUserLog("Update Material", User.Username + " Updated Material (" + cmbCategory.SelectedItem.ToString() + " - " + descriptionTextBox.Text + ")");
MessageBox.Show("Material Updated");
ClearAll();
}
}
private void DeleteRow(int id)
{
if (DatabaseAssist.ConnectToDatabase() == true)
{
SqlCommand com = new SqlCommand("DELETE FROM tblCustomers WHERE CustomerId = @id", DatabaseAssist.ConnectToLexlets);
com.Parameters.AddWithValue("@id", id);
com.ExecuteNonQuery();
DatabaseAssist.ConnectToLexlets.Close();
}
}
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 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 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 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 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 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 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;
}
}
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 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");
}
}
}
}
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();
}
}
