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