private bool IsUsernameValid() { if (txtUsername.TextLength >= 4) { DataTable users = DatabaseAssist.CreateDataTable("Select Username FROM tblUsers"); for (int i = 0; i < users.Rows.Count; i++) { if (users.Rows[i]["Username"].ToString() == txtUsername.Text.ToCamelCase()) { lblUserExists.Text = "Username Taken"; lblUserExists.ForeColor = Color.Red; lblUserExists.Visible = true; users.Dispose(); return(false); } } lblUserExists.Text = "Username Available"; lblUserExists.ForeColor = Color.Green; lblUserExists.Visible = true; users.Dispose(); return(true); } if (txtUsername.TextLength <= 4) { lblUserExists.Text = "Must Be At Least 4 Charactors"; lblUserExists.ForeColor = Color.Red; lblUserExists.Visible = true; } return(false); }
private void FillBestSellers() { flowLayoutSellersSold.Controls.Clear(); string sql = "SELECT TOP 10 SKU, Category, Description, QTYSold From tblProductData Order by QTYSold desc"; DataTable sellers = DatabaseAssist.CreateDataTable(sql); string stringToSplit, description, colour; int index; for (int i = 0; i <= 9; i++) { stringToSplit = (sellers.Rows[i]["Description"].ToString()); try { index = stringToSplit.IndexOf("-"); colour = stringToSplit.Substring(index + 2); description = stringToSplit.Substring(0, index - 1); } catch { description = sellers.Rows[i]["Description"].ToString(); colour = ""; } CreateLabel(description, flowLayoutSellersName, "SKU : " + sellers.Rows[i]["SKU"].ToString() + "\nColour : " + colour + "\nCategory : " + sellers.Rows[i]["Category"], "BlackAlignLeft"); CreateLabel(sellers.Rows[i]["QTYSold"].ToString(), flowLayoutSellersSold, "None", "BlackAlignLeft"); } }
private void Button2_Click(object sender, EventArgs e) { //Selects all invoice numbers DataTable Invoices = DatabaseAssist.CreateDataTable("SELECT DISTINCT [Order] From tblImportSales", 1, 1); int currentId; for (int i = 0; i < Invoices.Rows.Count; i++) //Selects each individual invoice number { DataTable orders = DatabaseAssist.CreateDataTable("SELECT * FROM tblImportSales WHERE [Order] = @param", Convert.ToInt32(Invoices.Rows[i]["Order"]), 1); currentId = GetCustomerID(orders); DataTable invoiceData = DatabaseAssist.CreateDataTable("SELECT * FROM tblInvoiceData WHERE InvoiceNumber= @Param", Convert.ToInt32(Invoices.Rows[i]["Order"])); if (invoiceData.Rows.Count == 0) // skips this step if invoice already exists { InsertSaleIntoInvoiceData(currentId, orders); AddToIncome(orders); } DataTable invoiceProducts = DatabaseAssist.CreateDataTable("SELECT * FROM tblInvoiceProducts WHERE InvoiceNumber= @Param", Convert.ToInt32(Invoices.Rows[i]["Order"])); if (invoiceProducts.Rows.Count == 0) // skips this step if invoice already exists { InsertIntoInvoiceProducts(orders); } } }
private string HashPassword() { DataTable user = DatabaseAssist.CreateDataTable("Select * FROM tblUsers WHERE Username =@param", txtUsername.Text); string salt = user.Rows[0]["Salt"].ToString(); //string hash = user.Rows[0]["Password"].ToString(); string password = txtPassword2.Text; return(Helper.GenerateSHA256HASH(password, salt)); }
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 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"); } }
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 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 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 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 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 BtnCust_Click(object sender, EventArgs e) { DataTable duplicates = DatabaseAssist.CreateDataTable("SELECT Email, count(*) as Entires From tblCustomers GROUP BY Email HAVING count(*) > 1 ORDER BY count(*) DESC"); for (int i = 0; i < duplicates.Rows.Count; i++) { DataTable currentCustomer = DatabaseAssist.CreateDataTable("select * from tblCustomers where Email = @param", duplicates.Rows[i]["Email"].ToString()); for (int j = 1; j < currentCustomer.Rows.Count; j++) { int firstId = Convert.ToInt32(currentCustomer.Rows[0]["CustomerID"]); int idToAlter = Convert.ToInt32(currentCustomer.Rows[j]["CustomerID"]); string add1 = currentCustomer.Rows[j]["Address1"].ToString(); string add2 = currentCustomer.Rows[j]["Address2"].ToString(); string postcode = currentCustomer.Rows[j]["postcode"].ToString(); CopyToOldAdresses(firstId, add1, add2, postcode); AlterIdFromInvoices(firstId, idToAlter); DeleteRow(idToAlter); } } }
private void LoadRowNames() { DataTable Expenses = DatabaseAssist.CreateDataTable("Select CategoryName From tblOutgoingCategory Order By CategoryName Asc", 1, 1); flHeadings.Invoke((MethodInvoker) delegate() { flHeadings.Controls.Clear(); }); Helper.CreateLabel("", flHeadings, "None", "BlackAlignLeft12"); Helper.CreateLabel("Sales", flHeadings, "None", "BlackAlignLeft12"); Helper.CreateLabel("Items Sold", flHeadings, "None", "BlackAlignLeft12"); Helper.CreateLabel("Income", flHeadings, "None", "BlackAlignLeft12"); Helper.CreateLabel("Expenses", flHeadings, "None", "BlackAlignLeft12"); for (int i = 0; i < Expenses.Rows.Count; i++) { Helper.CreateLabel(Expenses.Rows[i][0].ToString(), flHeadings, "None", "BlackAlignLeft"); } Helper.CreateLabel("Total Expenses", flHeadings, "None", "BlackAlignLeft12"); Helper.CreateLabel("Profit", flHeadings, "None", "BlackAlignLeft12"); }
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); }