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(); } }
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 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 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 { } }
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 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(); } }
public void LoadData() { DataGridMaterials.DataSource = null; lblTotalCost.Text = "£" + DatabaseAssist.GetOneCellValue("SELECT ROUND(SUM(MaterialWorth),2) From vwMaterialsWithUsed", "", "Column1"); SqlCommand command; SqlDataAdapter adapter = new SqlDataAdapter(); string sql = ""; if (DatabaseAssist.ConnectToDatabase() == true) { if (chkLowLevel.Checked) { sql = "SELECT * FROM vwMaterialsWithUsed where QTYinStock <= LowLevelWarning Order By MaterialId"; } else { sql = "SELECT * FROM vwMaterialsWithUsed Order By MaterialId"; } command = new SqlCommand(sql, DatabaseAssist.ConnectToLexlets); adapter.SelectCommand = command; DataSet ds = new DataSet(); adapter.Fill(ds, "tblMaterials"); DataGridMaterials.DataSource = ds; DataGridMaterials.DataMember = "tblMaterials"; DataGridMaterials.Columns["MaterialId"].HeaderText = "Id"; DataGridMaterials.Columns["CategoryName"].HeaderText = "Category"; DataGridMaterials.Columns["ColourName"].HeaderText = "Colour"; DataGridMaterials.Columns["QTYinStock"].HeaderText = "Quanity"; DataGridMaterials.Columns["LowLevelWarning"].HeaderText = "Low Alarm"; DataGridMaterials.Columns["CostPerItem"].HeaderText = "Cost"; DataGridMaterials.Columns["CostPerItem"].DefaultCellStyle.Format = "£0.00"; DataGridMaterials.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; SqlCommand command2 = new SqlCommand("Select * from vwMaterialsUsed order by MaterialId asc", DatabaseAssist.ConnectToLexlets); SqlDataAdapter ad2 = new SqlDataAdapter(); ad2.SelectCommand = command2; DataSet ds2 = new DataSet(); ad2.Fill(ds2, "table2"); DatabaseAssist.ConnectToLexlets.Close(); } }
private int GetCustomerID(DataTable orders) { // check if customer exists, if it does return id number if not add to table then search again to get id number then return int id = 1; string fname = orders.Rows[0]["FirstName"].ToString(); string sname = orders.Rows[0]["Surname"].ToString(); string postcode = orders.Rows[0]["Postcode"].ToString(); DataTable customer = DatabaseAssist.CreateDataTable("SELECT * FROM tblCustomers WHERE FirstName = @param AND Surname=@param2 AND Postcode =@param3", fname, sname, postcode); if (customer.Rows.Count > 0) { id = Convert.ToInt32(customer.Rows[0]["CustomerID"]); } else { string sqlquery = "insert into tblCustomers VALUES (@firstName, @surname, @add1, @add2, @postcode, @email, @sub, @comments)"; SqlCommand com = new SqlCommand(sqlquery, DatabaseAssist.ConnectToLexlets); com.Parameters.AddWithValue("@firstName", orders.Rows[0]["FirstName"]); com.Parameters.AddWithValue("@surname", orders.Rows[0]["Surname"]); com.Parameters.AddWithValue("@add1", orders.Rows[0]["Address1"]); com.Parameters.AddWithValue("@add2", orders.Rows[0]["Address2"]); com.Parameters.AddWithValue("@postcode", orders.Rows[0]["Postcode"]); com.Parameters.AddWithValue("@email", orders.Rows[0]["Email"]); com.Parameters.AddWithValue("@sub", "No"); com.Parameters.AddWithValue("@comments", ""); if (DatabaseAssist.ConnectToDatabase() == true) { com.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); } id = DatabaseAssist.GetOneCellValue("SELECT CustomerID FROM tblCustomers WHERE FirstName = @param AND Surname=@param2 AND Postcode =@param3", fname, sname, postcode, "CustomerID"); listBox1.Items.Add("New Id : " + id + " - " + fname + " " + sname); } customer.Dispose(); return(id); }
private void UpdateMaterials() { foreach (var item in MaterialUpdate) { int id = item.Key; int qty = item.Value; int currentQty = Convert.ToInt32(DatabaseAssist.GetOneCellValue("SELECT QTYinStock FROM tblMaterials WHERE MaterialID = @param", id, "QtyInStock")); int newQty = currentQty + qty; SqlDataAdapter adapter = new SqlDataAdapter("UPDATE tblMaterials SET QTYinStock = @qty Where MaterialID = @id", DatabaseAssist.ConnectToLexlets); adapter.SelectCommand.Parameters.AddWithValue("@id", id); adapter.SelectCommand.Parameters.AddWithValue("@qty", newQty); if (DatabaseAssist.ConnectToDatabase() == true) { adapter.SelectCommand.ExecuteNonQuery(); } string description = DatabaseAssist.GetOneCellValue("SELECT * FROM tblMaterials WHERE MaterialID = @param", id, "Description"); int colourId = Convert.ToInt32(DatabaseAssist.GetOneCellValue("SELECT * FROM tblMaterials WHERE MaterialID = @param", id, "ColourID")); string colour = DatabaseAssist.GetOneCellValue("SELECT * FROM tblColours WHERE ColourID = @param", colourId, "ColourName"); User.AddToUserLog("Materials Added To Stock", User.Username + " Added " + qty + " " + colour + " " + description); } MessageBox.Show("Materials have been added to the system", "Success", MessageBoxButtons.OK); DatabaseAssist.ConnectToLexlets.Close(); }
private void CmbSKU_SelectedValueChanged(object sender, EventArgs e) { lblDescription.Text = DatabaseAssist.GetOneCellValue("Select Description from tblProductData WHERE SKU = @param", Convert.ToInt32(cmbSKU.SelectedItem), "Description"); }
private bool CheckUserExists() { bool userExists = false; string lockAccount = "false"; if (DatabaseAssist.ConnectToDatabase() == true) { SqlDataAdapter da = new SqlDataAdapter("Select * FROM tblUsers WHERE Username =@user AND Password =@pass", DatabaseAssist.ConnectToLexlets); da.SelectCommand.Parameters.AddWithValue("@user", txtUser.Text.ToLower()); da.SelectCommand.Parameters.AddWithValue("@pass", HashPassword()); DataTable dt = new DataTable(); da.Fill(dt); DatabaseAssist.ConnectToLexlets.Close(); if (dt.Rows.Count == 0) { string failedAttempts = ""; try { failedAttempts = DatabaseAssist.GetOneCellValue("select * From tblUsers WHERE Username = @param", txtUser.Text.ToLower(), "FailedLoginAttempts"); } catch { MessageBox.Show("User Does Not Exist", "No User Found"); return(false); } int failed = Convert.ToInt16(failedAttempts); bool locked = Convert.ToBoolean(DatabaseAssist.GetOneCellValue("select * From tblUsers WHERE Username = @param", txtUser.Text.ToLower(), "AccountLocked")); if (failed >= 3 && locked == false) { MessageBox.Show("Too many failed attempts account is now locked. Click yes to request for account to be unlocked", "Too many failed attempts", MessageBoxButtons.YesNo); lockAccount = "true"; } else if (locked == true) { MessageBox.Show("Account is locked. Please contact the system administrator to regain access", "Too many failed attempts", MessageBoxButtons.OK); return(false); } else { MessageBox.Show("Invalid Username or Password - " + (3 - failed) + " Attemps Remaining", "Error", MessageBoxButtons.OK); } SqlCommand command = new SqlCommand(); command = new SqlCommand(@"UPDATE tblUsers SET FailedLoginAttempts = @failed, AccountLocked =@locked WHERE Username =@user", DatabaseAssist.ConnectToLexlets); command.Parameters.AddWithValue("@failed", Convert.ToInt16(failed + 1)); command.Parameters.AddWithValue("@locked", lockAccount); command.Parameters.AddWithValue("@user", txtUser.Text.ToLower()); DatabaseAssist.ConnectToDatabase(); command.ExecuteNonQuery(); DatabaseAssist.ConnectToLexlets.Close(); userExists = false; return(userExists); } else { User.Username = (dt.Rows[0]["Username"].ToString()); User.AccessLevel = Convert.ToInt16(dt.Rows[0]["AccessLevel"]); User.UserId = Convert.ToInt16(dt.Rows[0]["UserId"]); User.AcountLocked = Convert.ToBoolean(dt.Rows[0]["AccountLocked"]); userExists = true; return(userExists); } } return(userExists); }