private void GetProductInfo()
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description, UnitPrice, StocksOnHand FROM Product WHERE ProductNo =" + productID + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductCode.Text = SQLConn.dr[0].ToString();
lblDescription.Text = SQLConn.dr[1].ToString();
lblPrice.Text = Strings.FormatNumber(SQLConn.dr[2]).ToString();
lblCurrentStocks.Text = SQLConn.dr[3].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private bool IsAdding()
{
bool ret = false;
try
{
SQLConn.sqL = "SELECT * FROM Company";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = false;
}
else
{
ret = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
private void GetProductNo()
{
try
{
SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = (Convert.ToInt32(SQLConn.dr["ProductNo"]) + 1).ToString();
}
else
{
lblProductNo.Text = "1";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadInvoices()
{
dgwInvoice.Visible = true;
try
{
SQLConn.sqL = "SELECT TDate, InvoiceNo FROM Transactions WHERE TDate = '" + DateTime.Now.ToString("MM/dd/yyyy") + "' ORDER BY InvoiceNo Desc";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
dgwInvoice.Rows.Clear();
while (SQLConn.dr.Read() == true)
{
dgwInvoice.Rows.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString());
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT ReturnDate as DateReturn, SR.InvoiceNo, TotalAmount as AmountRefund, SUM(SRI.Quantity) as ItemQuantity, P.UnitPrice as ItemPrice, (SUM(SRI.Quantity) * P.UnitPrice) as ExtendedPrice, Description as Product, QtyTotal.TotQuantity as TotalItemReturn FROM SalesReturn as SR INNER JOIN SalesReturnItem SRI ON SR.InvoiceNo =SRI.InvoiceNo INNER JOIN Product P ON P.ProductNo = SRI.ProductID INNER JOIN (SELECT SUM(Quantity) as TotQuantity, InvoiceNo FROM SalesReturnItem GROUP BY InvoiceNo ) QtyTotal ON QtyTotal.InvoiceNo = SR.InvoiceNo WHERE ReturnDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, SR.InvoiceNo ORDER By ReturnDate, SR.InvoiceNo";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new MySqlDataAdapter(SQLConn.cmd);
this.dsReportC.Return.Clear();
SQLConn.da.Fill(this.dsReportC.Return);
ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString());
ReportParameter endDate = new ReportParameter("EndDate", EndDate.ToString());
this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate });
this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 90;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
private void getStaffInfo()
{
try
{
SQLConn.sqL = "SELECT CONCAT(lastname, ', ', Firstname, ' ', MI) as StaffName, Role FROM Staff WHERE StaffID = '" + lblStaffID.Text + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblPosition.Text = SQLConn.dr["Role"].ToString();
lblCustName.Text = SQLConn.dr["Staffname"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void GetInvoiceNo()
{
try
{
SQLConn.sqL = "SELECT InvoiceNo FROM Transactions ORDER BY InvoiceNO DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblInvoice.Text = (Convert.ToInt32(SQLConn.dr["InvoiceNo"]) + 1).ToString();
}
else
{
lblInvoice.Text = "100100000";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadUpdateCategory()
{
try
{
SQLConn.sqL = "SELECT * FROM Category WHERE CategoryNo = '" + categoryID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblCategoryNo.Text = SQLConn.dr["CategoryNo"].ToString();
txtCatName.Text = SQLConn.dr["CategoryName"].ToString();
txtDescription.Text = SQLConn.dr["Description"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddCustomer()
{
try
{
if (txtDue.Text == "" || txtComments.Text == "" || txtRemaining.Text == "" || txtShop.Text == "")
{
Interaction.MsgBox("Please Enter All Required Business Fields", MsgBoxStyle.Information, "Incomplete Form Submission");
}
else
{
SQLConn.sqL = "INSERT INTO customer(Lastname, Firstname, CNIC, Street, City, Province, ContactNo, DuePayment, CommentOnDuePayment, RemainingBalance, shopname, mobileNo) VALUES('" + txtLastname.Text + "', '" + txtFirstname.Text + "', '" + txtCNIC.Text + "', '" + txtStreet.Text + "', '" + txtCity.Text + "', '" + txtProvince.Text + "', '" + txtContractNo.Text + "', '" + txtDue.Text + "', '" + txtComments.Text + "', '" + txtRemaining.Text + "', '" + txtShop.Text + "', '" + textBox1.Text + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
Interaction.MsgBox("New Customer successfully added.", MsgBoxStyle.Information, "Add Customer");
this.Close();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadInvoiceDetail(string search)
{
try
{
SQLConn.sqL = "SELECT description_product,ProductNo,ItemPrice,Quantity,ProductName,InvoiceNo FROM transactiondetails WHERE InvoiceNo LIKE '" + search.Trim() + "%' ORDER By InvoiceNo";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["ProductNo"].ToString());
x.SubItems.Add(SQLConn.dr["ProductName"].ToString());
x.SubItems.Add(SQLConn.dr["ItemPrice"].ToString());
x.SubItems.Add(SQLConn.dr["Quantity"].ToString());
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["description_product"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private bool IsPasswordCorrect()
{
bool ret = false;
try
{
SQLConn.sqL = "SELECT * FROM Staff WHERE Role ='Admin' AND UPassword = '******'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
public void LoadStaffs(string search)
{
try
{
SQLConn.sqL = "SELECT StaffID, CONCAT(Lastname, ', ', Firstname, ' ', MI) as ClientName, CONCAT(Street, ', ', Barangay, ', ', City , ', ', Province) as Address, ContactNo, username, role FROM Staff WHERE LASTNAME LIKE '" + search.Trim() + "%' ORDER By Lastname";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["StaffId"].ToString());
x.SubItems.Add(SQLConn.dr["ClientName"].ToString());
x.SubItems.Add(SQLConn.dr["ContactNo"].ToString());
x.SubItems.Add(SQLConn.dr["Address"].ToString());
x.SubItems.Add(SQLConn.dr["username"].ToString());
x.SubItems.Add(SQLConn.dr["Role"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public string GetInvoiceNo()
{
string ret = "";
try
{
SQLConn.sqL = "SELECT Id FROM Invoice ORDER BY Id DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = "INV-000-" + (Convert.ToInt32(SQLConn.dr["Id"]) + 1).ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
public void SalesReturns(DateTime startDate, DateTime endDate, string searchString)
{
try
{
SQLConn.sqL = "SELECT InvoiceDate, InvoiceNo, Customer, SalesPerson, TotalAmount FROM Invoice WHERE InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND (InvoiceNo LIKE '%" + txtSearch.Text + "%' OR SalesPerson LIKE '%" + txtSearch.Text + "%' OR Customer LIKE '%" + txtSearch.Text + "%') GROUP BY InvoiceNo ORDER BY InvoiceDate, InvoiceNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
dgw.Rows.Clear();
while (SQLConn.dr.Read() == true)
{
dgw.Rows.Add(Convert.ToDateTime(SQLConn.dr[0]).ToString("MM/dd/yyyy"), SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), SQLConn.dr[3].ToString(), Strings.FormatNumber(SQLConn.dr[4]).ToString(), Strings.FormatNumber(SQLConn.dr[4]).ToString());
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void AddTransactionDetails()
{
try
{
SQLConn.sqL = "INSERT INTO TransactionDetails(InvoiceNo, ProductNo, ItemPrice, Quantity, Discount) VALUES(@InvoiceNo, @ProductNo, @ItemPrice, @Quantity, @Discount)";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.Add("InvoiceNo", MySql.Data.MySqlClient.MySqlDbType.VarChar);
SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32);
SQLConn.cmd.Parameters.Add("ItemPrice", MySql.Data.MySqlClient.MySqlDbType.Double);
SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double);
SQLConn.cmd.Parameters.Add("Discount", MySql.Data.MySqlClient.MySqlDbType.Double);
for (int i = 0; i <= dgw.Rows.Count - 1; i++)
{
SQLConn.cmd.Parameters["InvoiceNo"].Value = lblInvoice.Text;
SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value;
SQLConn.cmd.Parameters["ItemPrice"].Value = Convert.ToDouble(dgw.Rows[i].Cells[3].Value.ToString().Replace(",", ""));
SQLConn.cmd.Parameters["Quantity"].Value = Convert.ToDouble(dgw.Rows[i].Cells[4].Value);
SQLConn.cmd.Parameters["Discount"].Value = Convert.ToDouble(dgw.Rows[i].Cells[6].Value.ToString().Replace(",", ""));
SQLConn.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadCategory()
{
try
{
SQLConn.sqL = "SELECT * FROM Product WHERE ProductCode LIKE '" + txtCatName.Text + "%' ORDER BY ProductCode ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["ProductNo"].ToString());
x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["UnitPrice"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
//Decrease product's stocks on hand
public void UpdateProductQuantity()
{
try
{
SQLConn.sqL = "UPDATE Product SET StocksOnHand = StocksOnHand - @Quantity WHERE ProductNo = @ProductNo";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32);
SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double);
for (int i = 0; i <= dgw.Rows.Count - 1; i++)
{
SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value;
SQLConn.cmd.Parameters["Quantity"].Value = Convert.ToDouble(dgw.Rows[i].Cells[4].Value);
SQLConn.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadCustomer()
{
try
{
SQLConn.sqL = "SELECT * FROM customer WHERE Firstname LIKE '" + textBox1.Text + "%' ORDER BY Firstname ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
listView3.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["CusID"].ToString());
x.SubItems.Add(SQLConn.dr["Firstname"].ToString());
x.SubItems.Add(SQLConn.dr["DuePayment"].ToString());
x.SubItems.Add(SQLConn.dr["RemainingBalance"].ToString());
listView3.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void GetBarcodeForAutoComplete()
{
try
{
SQLConn.sqL = "SELECT CONCAT(Barcode,' - ', TRIM(Description),' => P', FORMAT(UnitPrice,2)) as Barcode FROM Product Order By Barcode";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(SQLConn.cmd);
da.Fill(ds, "list");
var source = new List <string>();
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
source.Add(ds.Tables[0].Rows[i]["Barcode"].ToString());
}
this.txtBarcode.AutoCompleteList = source;
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadInvoices(DateTime startDate, DateTime endDate, string searchString)
{
try
{
SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy"));
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private int GetProductIDByBarcode(string barcode)
{
int ret = 0;
try
{
SQLConn.sqL = "SELECT ProductNo FROM Product WHERE barcode = '" + barcode + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = Convert.ToInt32(SQLConn.dr[0]);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
private bool checkQuantity(int quantity)
{
try
{
SQLConn.sqL = "SELECT StocksOnHand FROM product WHERE ProductCode = '" + Productname + "'";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
int dbquantity = int.Parse(SQLConn.dr["StocksOnHand"].ToString());
if (dbquantity - quantity > 0)
{
return(true);
}
return(false);
}
else
{
//MessageBox.Show("Invalid Password. Please try again.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(false);
}
private void GetCustomerInformation()
{
try
{
SQLConn.sqL = "SELECT Id, Lastname, Firstname, ContactNo, Address FROM customer WHERE id = '" + customerID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
txtLastname.Tag = SQLConn.dr[0].ToString();
txtLastname.Text = SQLConn.dr[1].ToString();
txtFirstname.Text = SQLConn.dr[2].ToString();
txtContactno.Text = SQLConn.dr[3].ToString();
txtAddress.Text = SQLConn.dr[4].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void UpdateProductQuantity()
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
try
{
SQLConn.sqL = "select StocksOnHand from product where Barcode = '" + row.Cells["Code"].Value.ToString() + "'";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
int stocks = int.Parse(SQLConn.dr["StocksOnHand"].ToString());
SQLConn.sqL = "update product SET StocksOnHand = @stocks where Barcode = '" + row.Cells["Code"].Value.ToString() + "'";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@stocks", (stocks - int.Parse(row.Cells["Quantity"].Value.ToString())));
SQLConn.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
}
public void GetCompanyInfo()
{
try
{
SQLConn.sqL = "SELECT CompanyID, Name, Address, PhoneNo, Email, Website, TINNumber FROM Company";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
txtName.Tag = SQLConn.dr[0].ToString();
txtName.Text = SQLConn.dr[1].ToString();
txtAddress.Text = SQLConn.dr[2].ToString();
txtPhoneNo.Text = SQLConn.dr[3].ToString();
txtEmail.Text = SQLConn.dr[4].ToString();
txtWebsite.Text = SQLConn.dr[5].ToString();
txtTINNumber.Text = SQLConn.dr[6].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void getInvoiceNo()
{
try
{
SQLConn.sqL = "SELECT TOP 1 InvoiceNo FROM transactions ORDER BY InvoiceNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
InvoiceNo = int.Parse(SQLConn.dr["InvoiceNo"].ToString()) + 1;
invoiceno.Text = "Invoice No: " + InvoiceNo.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void GetVATInfo()
{
try
{
SQLConn.sqL = "SELECT VATID, VatPercent FROM VatSetting";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
txtPercent.Tag = SQLConn.dr[0];
txtPercent.Text = SQLConn.dr[1].ToString();
isAddingVat = false;
}
else
{
isAddingVat = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private bool IsValidQuantity(double EnteredQuantity)
{
bool ret = false;
try
{
SQLConn.sqL = "SELECT * FROM PRODUCT WHERE StocksOnHand >= " + EnteredQuantity + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = true;
}
else
{
ret = false;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
private void AddProducts()
{
string barcode = "";
if (txtBarcode.Text.Trim() == "")
{
barcode = "NO BARCODE";
}
else
{
barcode = txtBarcode.Text;
}
try
{
SQLConn.sqL = "INSERT INTO Product(ProductCode, Description, Barcode, UnitPrice, StocksOnHand, ReorderLevel, CategoryNo) VALUES('" + txtProductCode.Text + "', '" + txtDescription.Text + "', '" + barcode + "', '" + txtUnitPrice.Text.Replace(",", "") + "', '" + txtStocksOnHand.Text.Replace(",", "") + "', '" + txtReorderLevel.Text + "', '" + categoryID + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
Interaction.MsgBox("Product successfully added.", MsgBoxStyle.Information, "Add Product");
AddStockIn();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddStaff()
{
try
{
if (txtUsername.Text == "" || txtPassword.Text == "" || txtRole.Text == "" || txtConfirmPWD.Text == "")
{
Interaction.MsgBox("Please Enter All Required User Fields", MsgBoxStyle.Information, "Incomplete Form Submission");
}
else
{
SQLConn.sqL = "INSERT INTO STAFF(Lastname, Firstname, Street, City, Province, ContactNo, Username, Role, UPassword,mobileNo) VALUES('" + txtLastname.Text + "', '" + txtFirstname.Text + "', '" + txtStreet.Text + "', '" + txtCity.Text + "', '" + txtProvince.Text + "', '" + txtContractNo.Text + "', '" + txtUsername.Text + "', '" + txtRole.Text + "', '" + txtPassword.Text + "', '" + textBox1.Text + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
Interaction.MsgBox("New staff successfully added.", MsgBoxStyle.Information, "Add Staff");
this.Close();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
