private void loggedOut()
{
sqlCommandAB logout = new sqlCommandAB();
int id = logout.getSingleValue("SELECT loginID FROM loginLog ORDER BY loginID DESC");
logout.updateDB("UPDATE loginLog set loginEnd = getDate() WHERE loginID = '" + id + "'");
}
public void updateInv(string pCode, int q)
{
sqlCommandAB upIn = new sqlCommandAB();
int pqtyy = upIn.getSingleValue("SELECT pQty FROM Product WHERE pCode ='" + pCode + "'");
upIn.updateDB("UPDATE Product set pQty =' " + (pqtyy - q) + "' WHERE pCode ='" + pCode + "'");
}
public void orderItem(string a)
{
sqlCommandAB o = new sqlCommandAB();
DataTable dt = o.QueryDT("SELECT * FROM OrderItem " + a);
int count = dt.Rows.Count;
string[] dr;
for (int i = 0; i < count; i++)
{
dr = new string[] { (string)dt.Rows[i]["pCode"], (string)dt.Rows[i]["pName"], dt.Rows[i]["pSellingPrice"].ToString(), dt.Rows[i]["pQuantity"].ToString(), dt.Rows[i]["pTotalAmount"].ToString() };
dataGridView2.Rows.Add(dr);
}
DataTable dz = o.QueryDT("SELECT * FROM Payment " + a);
int c = dz.Rows.Count;
if (c > 0)
{
groupBox2.Visible = true;
grandTotal.Text = "R" + dz.Rows[0]["OrderAmount"].ToString();
pAmount.Text = "R" + dz.Rows[0]["pAmount"].ToString();
pChange.Text = "R" + dz.Rows[0]["pChange"].ToString();
pMode.Text = dz.Rows[0]["pType"].ToString();
}
}
//public string[] brandValues(string a, string b, string c) {
// string[] brandthings = new string[] {a,b,c };
// return brandthings;
//}
private void brandUBTN_Click(object sender, EventArgs e)
{
int selectedrowindex = dataGridView2.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView2.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["brandNameDT"].Value);
sqlCommandAB brandSql = new sqlCommandAB();
int id = brandSql.getSingleValue("SELECT brandID FROM Brand WHERE( categoryName = '" + selectCat.SelectedItem.ToString() + "' AND brandName ='" + a + "')");
if (brandNameTB.Text.Equals(""))
{
MessageBox.Show("Please Enter a Brand Name");
}
else
{
brandSql.updateDB("UPDATE Brand set brandName='" + (string)brandNameTB.Text + "',brandDescription='" + (string)brandDescriptionTB.Text + "' WHERE brandID='" + id + "'");
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message + "Couldn't update " + a + "\nDue to netwrok Error, Please check your network and try again");
}
else
{
MessageBox.Show("The brand " + a + " update succefully");
dataGridView2.Rows.Clear();
CategorysAndBrand[] customer;
getBrandData(out customer);
Clear();
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
Clear();
disable();
creditControl.Visible = true;
saveBTN.Enabled = false;
int selectedrowindex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["custNumber"].Value);
customer_Number = a;
textBox2.Text = getCreditA(a).ToString();
balanceTB.Text = getCreditB(a).ToString();
sqlCommandAB customerSql = new sqlCommandAB();
DataTable DT = customerSql.QueryDT("SELECT * FROM Customer where Customer_Number ='" + a + "' ");
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message);
}
else
{
populate(DT);
deleteBTN.Enabled = true;
}
// MessageBox.Show((string)DT.Rows[0]["Customer_Surname"]);
}
private void brandDBTN_Click(object sender, EventArgs e)
{
int selectedrowindex = dataGridView2.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView2.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["brandNameDT"].Value);
sqlCommandAB customerSql = new sqlCommandAB();
int id = customerSql.getSingleValue("SELECT brandID FROM Brand WHERE( categoryName = '" + selectCat.SelectedItem.ToString() + "' AND brandName ='" + a + "')");
DialogResult dr = MessageBox.Show("Are you sure you want to Delete " + brandNameTB.Text.ToString() + " Permanently?", "Confim Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dr == DialogResult.Yes)
{
DataTable DT = customerSql.QueryDT("DELETE FROM Brand WHERE brandID = '" + id + "' AND brandName ='" + a + "' ");
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message + "Couldn't delete " + a);
}
else
{
dataGridView2.Rows.Clear();
CategorysAndBrand[] customer;
getBrandData(out customer);
Clear();
}
}
}
private void updateBTN_Click(object sender, EventArgs e)
{
sqlCommandAB categroySql = new sqlCommandAB();
if (CategoryNameTB.Text == "")
{
MessageBox.Show("Category is Blank");
}
else
{
categroySql.updateDB("UPDATE Category set categoryDescription='" + (string)Category_DescriptionTB.Text + "' WHERE categoryName='" + (string)CategoryNameTB.Text + "'");
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message + "Couldn't update " + (string)CategoryNameTB.Text + "\nDue to netwrok Error, Please check your network and try again");
}
else
{
MessageBox.Show("Category Description for " + (string)CategoryNameTB.Text + " update succefully");
dataGridView2.Rows.Clear();
CategorysAndBrand[] customer;
getBrandData(out customer);
Clear();
}
}
}
private void button6_Click(object sender, EventArgs e)
{
int selectedrowindex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["custNumber"].Value);
sqlCommandAB customerSql = new sqlCommandAB();
DialogResult dr = MessageBox.Show("Are you sure you want to Delete " + nameTB.Text.ToString() + "Permanently?", "Confim Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dr == DialogResult.Yes)
{
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message);
}
else
{
DataTable DT = customerSql.QueryDT("DELETE FROM Customer where Customer_Number ='" + a + "' ");
dataGridView1.Rows.Clear();
Customer[] customer;
getCatData(out customer);
loadData();
Clear();
}
}
}
private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
Clear();
brandNameTB.Text = brandDescriptionTB.Text = "";
int selectedrowindex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["CategoryName"].Value);
sqlCommandAB customerSql = new sqlCommandAB();
DataTable DT = customerSql.QueryDT("SELECT * FROM Category where categoryName ='" + a + "' ");
CategoryNameTB.Text = (string)DT.Rows[0]["categoryName"];
Category_DescriptionTB.Text = (string)DT.Rows[0]["categoryDescription"];
//This should fill the Brand Table
dataGridView2.Rows.Clear();
CategorysAndBrand[] brandArray;
selectCat.SelectedItem = a;
getBrandData(out brandArray, a);
deleteBTN.Enabled = true;
updateBTN.Enabled = true;
saveBTN.Enabled = false;
}
public void getCustSearch(out Customer[] Cast_Array)
{
dataGridView1.Rows.Clear();
string allQ = (string)textBox1.Text;
sqlCommandAB brandSql = new sqlCommandAB();
DataTable DT = brandSql.QueryDT("SELECT * FROM Customer WHERE ((Customer_IDNumber LIKE '" + allQ + "%') OR (Customer_Number LIKE '%" + allQ + "%')) OR (Customer_Name LIKE '%" + allQ + "%')");
Cast_Array = null;
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message);
}
else
{
int rowCount = DT.Rows.Count;
Cast_Array = new Customer[rowCount];
string[] row;
for (int j = 0; j < rowCount; j++)
{
row = new string[] { (string)DT.Rows[j]["Customer_Number"], (string)DT.Rows[j]["Customer_Name"], (string)DT.Rows[j]["Customer_Surname"], DT.Rows[j]["Customer_Cell"].ToString(), (string)DT.Rows[j]["Customer_Address"] };
dataGridView1.Rows.Add(row);
loadData();
}
}
}
private DataTable getUserLoginDetails(string a)
{
sqlCommandAB customerSql = new sqlCommandAB();
DataTable DT = customerSql.QueryDT("SELECT staff_Number, staff_Username, staff_Password FROM Staff where staff_Number ='" + a + "' ");
return(DT);
}
public void InserPayment()
{
// MessageBox.Show((string)oNumberTB.Text + " " + (string)custNumberTB.Text + " " + Convert.ToDouble(orderAmount) + " " + Convert.ToInt32(orderQty) + " " +
// Convert.ToDouble(amountPaidTB.Text) + " " + (string)paymentCB.Text);
sqlCommandAB brandSql = new sqlCommandAB();
DataTable dt = brandSql.QueryDT("SELECT * FROM Payment");
DataRow dr = dt.NewRow();
dr["Order_Number"] = (string)oNumberTB.Text;
dr["staff_Number"] = (string)custNumberTB.Text;
dr["Customer_Number"] = (string)custNumberTB.Text;
dr["OrderAmount"] = Convert.ToDouble(orderAmount);
if (change > 0)
{
dr["pChange"] = Convert.ToDouble(change);
}
dr["OrderQuantity"] = Convert.ToInt32(orderQty);
try
{
dr["pAmount"] = Convert.ToDouble(amountPaidTB.Text);
}
catch (Exception) {
dr["pAmount"] = 0;
}
dr["pType"] = (string)paymentCB.Text;
dt.Rows.Add(dr);
brandSql.UpDate(dt);
}
public void InsertNewProduct()
{
sqlCommandAB brandSql = new sqlCommandAB();
DataTable dt = brandSql.QueryDT("SELECT * FROM Product");
DataRow dr = dt.NewRow();
try { dr["categoryName"] = selectCat.SelectedItem.ToString(); }
catch (NullReferenceException) {
MessageBox.Show("Please Select a category first.");
}
dr["brandID"] = brandID;
dr["pCode"] = tb_itemBarCode.Text.ToString();
dr["pName"] = tb_itemName.Text.ToString();
try
{
dr["pCostPrice"] = Convert.ToDecimal(tb_itemStockPrice.Text);
dr["pSellingPrice"] = Convert.ToDecimal(tb_itemSellingPrice.Text);
dr["pReorderQty"] = Convert.ToInt32(ReorderPoint.Value);
dr["pQty"] = Convert.ToInt32(qty.Value);
}
catch (FormatException) {
MessageBox.Show("Check your input values");
}
dr["pDescription"] = tb_itemDescription.Text.ToString();
// dr["pImage"] = imageString;
dt.Rows.Add(dr);
brandSql.UpDate(dt);
}
private void deleteBTN_Click(object sender, EventArgs e)
{
int selectedrowindex = dataGridView3.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView3.Rows[selectedrowindex];
string a = Convert.ToString(selectedRow.Cells["code"].Value);
sqlCommandAB customerSql = new sqlCommandAB();
DialogResult dr = MessageBox.Show("Are you sure you want to Delete " + tb_itemName.Text.ToString() + "Permanently?", "Confim Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dr == DialogResult.Yes)
{
if (sqlCommandAB.message != null)
{
MessageBox.Show(sqlCommandAB.message);
}
else
{
DataTable DT = customerSql.QueryDT("DELETE FROM Product where pCode ='" + a + "' ");
dataGridView3.Rows.Clear();
addProductForm[] products;
getProduct(out products);
loadData();
Clear();
}
}
}
public void updateCustomerCreditLimit()
{
string cNum = (string)custNumberTB.Text;
double credit = Convert.ToDouble(getCreditB(cNum)) + Convert.ToDouble(amountPaidTB.Text) * 0.20;
sqlCommandAB ab = new sqlCommandAB();
ab.updateDB("UPDATE Customer set Customer_CreditLimit ='" + credit + "' WHERE Customer_Number = '" + cNum + "'");
}
public void UpdateCustomerAccountLimit()
{
sqlCommandAB upC = new sqlCommandAB();
// upC.updateDB("UPDATE Customers Set Customer_CreditBalance = '" + Convert.ToDouble(TotalCredit) + "' WHERE (Customer_Number ='" + (string)custNumberTB.Text + "')");
upC.updateDB("UPDATE Customer set Customer_CreditLimit ='" + (getCreditB((string)custNumberTB.Text)) + (0.20) * (Convert.ToDouble(TotalCredit)) + "' WHERE Customer_Number = '" + (string)custNumberTB.Text + "'");
// MessageBox.Show(Convert.ToDouble(TotalCredit) + " " + (string)custNumberTB.Text);
}
private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int rowIndex = dataGridView2.SelectedCells[0].RowIndex;
DataGridViewRow dv = dataGridView2.Rows[rowIndex];
orderQty = dv.Cells["OrderQuantity"].Value.ToString();
orderAmount = dv.Cells["OrderAmount"].Value.ToString();
amountDueTB.Text = "R" + orderAmount;
orderNumber = dv.Cells["Order_Number"].Value.ToString();
oNumberTB.Text = orderNumber;
ClearAll();
panelPayment.Visible = true;
button_Edit.Visible = false;
buttonRemove.Visible = false;
string order_Num = dv.Cells["Order_Number"].Value.ToString();
cart2 = posSql.QueryDT("Select * from OrderItem where Order_Number ='" + order_Num + "'");
cart3 = cart2;
for (int i = 0; i < cart2.Rows.Count; i++)
{
invoiceTB.Text = cart2.Rows[i]["Order_Number"].ToString();
DataRow row = cart.NewRow();
row["pName"] = cart2.Rows[i]["pName"].ToString();
row["pCode"] = cart2.Rows[i]["pCode"].ToString();
row["pSellingPrice"] = cart2.Rows[i]["pSellingPrice"].ToString();
row["pQuantity"] = cart2.Rows[i]["pQuantity"].ToString();
row["pTotalAmount"] = cart2.Rows[i]["pTotalAmount"].ToString();
cart.Rows.Add(row);
}
dataGridView3.DataSource = cart;
sqlCommandAB custBal = new sqlCommandAB();
DataTable dt = custBal.QueryDT("SELECT Customer_CreditBalance, Customer_CreditLimit FROM Customer WHERE (Customer_Number ='" + (string)custNumberTB.Text + "')");
if (sqlCommandAB.message != null)
{
MessageBox.Show("There is an error by the Connecting to the Database, Cannot get Customer Details");
}
else
{
//dt.Rows[0]["Customer_CreditBalance"].ToString();
try
{
if ((decimal.TryParse(dt.Rows[0]["Customer_CreditLimit"].ToString(), out creditLimit) && decimal.TryParse(dt.Rows[0]["Customer_CreditBalance"].ToString(), out customerCreditBal)))
{
}
else
{
MessageBox.Show("Customer Credit Details unavaileable, Please Restart the system...");
}
AccountBalanceTB.Text = "R" + customerCreditBal.ToString();
}
catch (Exception) { AccountBalanceTB.Text = "0.00"; MessageBox.Show("You may recieve cash payments only."); }
}
}
public void insertUser()
{
sqlCommandAB iu = new sqlCommandAB();
DataTable dt = iu.QueryDT("SELECT * FROM loginLog");
DataRow dr = dt.NewRow();
dr["staff_Number"] = sNumber;
dr["staff_Name"] = sName;
dt.Rows.Add(dr);
iu.UpDate(dt);
}
public void InserNewCategory()
{
sqlCommandAB brandSql = new sqlCommandAB();
DataTable dt = brandSql.QueryDT("SELECT * FROM Category");
DataRow dr = dt.NewRow();
dr["categoryName"] = CategoryNameTB.Text;
dr["categoryDescription"] = Category_DescriptionTB.Text;
dt.Rows.Add(dr);
brandSql.UpDate(dt);
}
//Modifications to make to get customers from the database....
public void getCustomers()
{
dataGridView1.Rows.Clear();
sqlCommandAB addCustomer = new sqlCommandAB();
DataTable DT = addCustomer.QueryDT("SELECT * FROM customer");
String[] rows;
for (int j = 0; j < DT.Rows.Count; j++)
{
rows = new string[] { (string)DT.Rows[j]["Customer_Number"], (string)DT.Rows[j]["Customer_Name"], (string)DT.Rows[j]["Customer_Surname"], DT.Rows[j]["Customer_IDNumber"].ToString(), "0" + DT.Rows[j]["Customer_Cell"].ToString(), DT.Rows[j]["Customer_Email"].ToString(), (string)DT.Rows[j]["Customer_Address"] };
dataGridView1.Rows.Add(rows);
}
}
public void InsertNewBrand()
{
sqlCommandAB brandSql = new sqlCommandAB();
DataTable dt = brandSql.QueryDT("SELECT * FROM Brand");
DataRow dr = dt.NewRow();
dr["categoryName"] = selectCat.SelectedItem.ToString();
dr["brandName"] = brandNameTB.Text;
dr["brandDescription"] = brandDescriptionTB.Text;
dt.Rows.Add(dr);
brandSql.UpDate(dt);
}
public void updateInventory()
{
sqlCommandAB upInv = new sqlCommandAB();
DataTable dt = upInv.QueryDT("SELECT pCode,pQuantity FROM OrderItem WHERE (Order_Number ='" + (string)oNumberTB.Text + "')");
int count = dt.Rows.Count;
// MessageBox.Show(count.ToString() + " order Number" + (string)oNumberTB.Text );
for (int i = 0; i < count; i++)
{
updateInv(dt.Rows[i]["pCode"].ToString(), Convert.ToInt32(dt.Rows[i]["pQuantity"]));
}
}
public void getProducts()
{
dataGridView1.Rows.Clear();
sqlCommandAB addProducts = new sqlCommandAB();
DataTable DT = addProducts.QueryDT("SELECT * FROM Product");
String[] row;
for (int j = 0; j < DT.Rows.Count; j++)
{
string brand = addProducts.getSingleString("SELECT brandName FROM brand WHERE brandID = '" + DT.Rows[j]["brandID"] + "'");
row = new string[] { (string)DT.Rows[j]["pName"], DT.Rows[j]["pCode"].ToString(), (string)DT.Rows[j]["categoryName"], (string)brand, "R" + DT.Rows[j]["pSellingPrice"].ToString(), DT.Rows[j]["pQty"].ToString(), DT.Rows[j]["pReorderQty"].ToString(), (string)DT.Rows[j]["pDescription"] };
dataGridView1.Rows.Add(row);
}
}
public void fillGrid(string a)
{
dataGridView1.Rows.Clear();
sqlCommandAB o = new sqlCommandAB();
DataTable dt = o.QueryDT("SELECT * FROM Orders " + a);
int count = dt.Rows.Count;
string[] dr;
for (int i = 0; i < count; i++)
{
dr = new string[] { (string)dt.Rows[i]["Order_Number"], (string)dt.Rows[i]["Customer_Number"], (string)dt.Rows[i]["staff_Number"], dt.Rows[i]["OrderAmount"].ToString(), dt.Rows[i]["OrderQuantity"].ToString(), (string)dt.Rows[i]["OrderStatus"], dt.Rows[i]["OrderDate"].ToString() };
dataGridView1.Rows.Add(dr);
}
}
public void getBrandData(out CategorysAndBrand[] Cat_Array)
{
sqlCommandAB brandSql = new sqlCommandAB();
DataTable DT = brandSql.QueryDT("SELECT * FROM Brand");
int rowCount = DT.Rows.Count;
Cat_Array = new CategorysAndBrand[rowCount];
string[] row;
for (int j = 0; j < rowCount; j++)
{
row = new string[] { (string)DT.Rows[j]["brandName"], (string)DT.Rows[j]["categoryName"], (string)DT.Rows[j]["brandDescription"] };
dataGridView2.Rows.Add(row);
}
}
public void validateUser(string inUserName)
{
sqlCommandAB lg = new sqlCommandAB();
DataTable dt = lg.QueryDT("SELECT staff_Number, staff_Type, staff_Username,staff_Password,staff_Name FROM Staff WHERE (staff_Number = '" + inUserName + "' OR staff_Username ='******')");
int count = dt.Rows.Count;
if (count >= 1)
{
sPassword = dt.Rows[0]["staff_Password"].ToString();
sNumber = dt.Rows[0]["staff_Number"].ToString();
sUsername = dt.Rows[0]["staff_Username"].ToString();
sName = dt.Rows[0]["staff_Name"].ToString();
sType = dt.Rows[0]["staff_Type"].ToString();
}
}
public void getCatData(out Staff[] staff_Array)
{
sqlCommandAB brandSql = new sqlCommandAB();
DataTable DT = brandSql.QueryDT("SELECT * FROM staff");
int rowCount = DT.Rows.Count;
staff_Array = new Staff[rowCount];
string[] row;
for (int j = 0; j < rowCount; j++)
{
row = new string[] { (string)DT.Rows[j]["staff_Number"], (string)DT.Rows[j]["staff_Name"], (string)DT.Rows[j]["staff_Surname"], DT.Rows[j]["staff_Cell"].ToString(), (string)DT.Rows[j]["staff_Type"] };
dataGridView1.Rows.Add(row);
loadData();
}
}
private void updateCredit(double credit, string b)
{
sqlCommandAB ab = new sqlCommandAB();
ab.updateDB("UPDATE Customer set Customer_CreditLimit ='" + credit + "' WHERE Customer_Number = '" + b + "'");
if (sqlCommandAB.message != null)
{
MessageBox.Show("Credit Limit failed to update Customer");
}
else
{
MessageBox.Show("Credit limit set to " + credit);
Clear();
}
}
private static void getProductDetails(String id)
{
sqlCommandAB getPro = new sqlCommandAB();
DataTable DT = getPro.QueryDT("SELECT * FROM Product WHERE pCode = '" + id + "'");
if (DT.Rows.Count > 0)
{
pName = (string)DT.Rows[0]["pName"];
pCode = (string)DT.Rows[0]["pCode"];
pSellingPrice = Convert.ToDecimal(DT.Rows[0]["pSellingPrice"]);
pQty = Convert.ToInt32(DT.Rows[0]["pQty"]);
costPrice = Convert.ToDecimal(DT.Rows[0]["pCostPrice"]);
profit = (pSellingPrice - costPrice);
}
}
public decimal getCreditA(string a)
{
sqlCommandAB getCr = new sqlCommandAB();
string cr = getCr.getSingleString("SELECT Customer_CreditLimit from Customer WHERE Customer_Number ='" + a + "'");
decimal credit;
try
{
Decimal.TryParse(cr, out credit);
}
catch (Exception) {
return(0);
}
return(credit);
}