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);
}