private void LoadReport()
{
try
{
if (InvoiceSetting() == 1)
{
SQLConn.sqL = "SELECT CONCAT(Lastname, ', ', Firstname, ' ', MI) as StaffName, ProductCode, P.Description, REPLACE(TD.TDate, '-', '/') as TDate, TD.TTime,TD.ItemPrice, SUM(TD.Quantity) as totalQuantity, (TD.ItemPrice * SUM(TD.Quantity)) as TotalPrice FROM Product as P, TransactionDetails as T, TransactionDetails as TD, Staff as St WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND St.StaffID = T.StaffID AND TD.TDate = '" + ReportDate.ToString("MM/dd/yyyy") + "' AND T.Status != 1 GROUP BY St.StaffID,St.MI,St.firstname,St.lastname,P.ProductCode,P.Description, TD.TDate, TD.TTime, TD.ItemPrice ORDER By TD.TDate";
}
else
{
SQLConn.sqL = "SELECT CONCAT(Lastname, ', ', Firstname, ' ', MI) as StaffName, ProductCode, P.Description, REPLACE(TD.TDate, '-', '/') as TDate, TD.TTime,TD.ItemPrice, SUM(TD.Quantity) as totalQuantity, (TD.ItemPrice * SUM(TD.Quantity)) as TotalPrice FROM Product as P, TransactionDetails as T, TransactionDetails as TD, Staff as St WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND St.StaffID = T.StaffID AND TD.TDate = '" + ReportDate.ToString("MM/dd/yyyy") + "' GROUP BY St.StaffID,St.firstname,St.MI,St.lastname, P.ProductCode,P.Description,TD.TTime, TD.TDate , TD.ItemPrice ORDER By TD.TDate";
}
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new System.Data.SqlClient.SqlDataAdapter(SQLConn.cmd);
this.dsReportC.DailySalesByStaff.Clear();
SQLConn.da.Fill(this.dsReportC.DailySalesByStaff);
this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 90;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void LoadCategory()
{
try
{
SQLConn.sqL = "SELECT * FROM Category WHERE CategoryName LIKE '" + txtCatName.Text + "%' ORDER BY CategoryName ";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(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["CategoryNo"].ToString());
x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
int InvoiceSetting()
{
int ret = 0;
try
{
SQLConn.sqL = "SELECT HInvoice FROM Company";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = Convert.ToInt32(SQLConn.dr["HInvoice"]);
}
}
catch (Exception ex)
{
MessageBox.Show(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 System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Product successfully added.", "Add Product", MessageBoxButtons.OK, MessageBoxIcon.Information);
AddStockIn();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadInvoices(DateTime startDate, DateTime endDate, string searchString)
{
try
{
SQLConn.sqL = "SELECT TDate 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 System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(String.Format(SQLConn.dr["TDate"].ToString(), "MM/dd/yyyy"));
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void GetProductNo()
{
try
{
SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(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)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadUpdateCategory()
{
try
{
SQLConn.sqL = "SELECT ProductNo, ProductCode, P.Description, Barcode, P.CategoryNo, CategoryName, UnitPrice, StocksOnHand, ReorderLevel FROM Product as P LEFT JOIN Category as C ON P.CategoryNo = C.CategoryNo WHERE ProductNo = '" + productID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = SQLConn.dr["ProductNo"].ToString();
txtProductCode.Text = SQLConn.dr["ProductCode"].ToString();
txtDescription.Text = SQLConn.dr["Description"].ToString();
txtBarcode.Text = SQLConn.dr["Barcode"].ToString();
txtCategory.Text = SQLConn.dr["CategoryName"].ToString();
txtCategory.Tag = SQLConn.dr["CategoryNo"];
txtUnitPrice.Text = String.Format(SQLConn.dr["UnitPrice"].ToString(), "#,##0.00");
txtStocksOnHand.Text = SQLConn.dr["StocksOnHand"].ToString();
txtReorderLevel.Text = SQLConn.dr["ReorderLevel"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description, SUM(Quantity) as Quantity, DateIn as DateIn FROM Product as P, StockIn as S WHERE S.ProductNo = P.ProductNo AND DateIn BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductCode, Description,DateIN ORDER BY DateIn, Description";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new System.Data.SqlClient.SqlDataAdapter(SQLConn.cmd);
this.dsReportC.StocksIn.Clear();
SQLConn.da.Fill(this.dsReportC.StocksIn);
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(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 90;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
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 System.Data.SqlClient.SqlCommand(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)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void SalesReturns(DateTime startDate, DateTime endDate, string searchString)
{
try
{
SQLConn.sqL = "SELECT SalesReturnId, ReturnDate, sr.InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName, TotalAmount, SUM(sri.Quantity) as TotalQuantity FROM SalesReturn sr INNER JOIN SalesReturnItem sri ON sr.InvoiceNo = sri.InvoiceNo INNER JOIN Staff s ON s.StaffId = sr.userID WHERE ReturnDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND sr.InvoiceNo LIKE '%" + txtName.Text + "%' GROUP BY sr.InvoiceNo,sr.SalesReturnID,sr.ReturnDate,s.lastname,s.firstname,s.mi, TotalAmount ORDER BY ReturnDate, sr.InvoiceNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(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["SalesReturnId"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["ReturnDate"].ToString(), "MM/dd/yyyy"));
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["TotalAmount"].ToString(), "#,##0.00"));
x.SubItems.Add(SQLConn.dr["TotalQuantity"].ToString());
//Strings.Format(SQLConn.dr["TotalAmount"], "#,##0.00")
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadReturnItems(string invoiceNo)
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description, Quantity, UnitPrice, (Quantity * UnitPrice) ExtendedPrice FROM SalesReturnItem sri INNER JOIN Product p ON p.ProductNo = sri.ProductID WHERE Sri.InvoiceNo = '" + invoiceNo + "' ORDER BY Description ";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView2.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
x.SubItems.Add(SQLConn.dr["Quantity"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["UnitPrice"].ToString(), "#,##0.00"));
x.SubItems.Add(String.Format(SQLConn.dr["ExtendedPrice"].ToString(), "#,##0.00"));
//Strings.Format(SQLConn.dr["ExtendedPrice"], "#,##0.00")
ListView2.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void AddEditCompany(bool isAdding)
{
try
{
if (isAdding == true)
{
SQLConn.sqL = "INSERT INTO Company(Name, Address, PhoneNo, Email, Website, TINNumber) VALUES(@Name, @Address, @PhoneNo, @Email, @Website, @TINNumber)";
}
else
{
SQLConn.sqL = "UPDATE Company SET Name=@Name, Address=@Address, PhoneNo=@PhoneNO, Email=@Email, Website=@Website, TINNumber =@TINNumber WHERE CompanyID=@CompanyID ";
}
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@Name", txtName.Text);
SQLConn.cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
SQLConn.cmd.Parameters.AddWithValue("@PhoneNo", txtPhoneNo.Text);
SQLConn.cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
SQLConn.cmd.Parameters.AddWithValue("@Website", txtWebsite.Text);
SQLConn.cmd.Parameters.AddWithValue("@TINNumber", txtTINNumber.Text);
if (isAdding == false)
{
SQLConn.cmd.Parameters.AddWithValue("@CompanyID", txtName.Tag);
}
int i = SQLConn.cmd.ExecuteNonQuery();
if (i > 0)
{
if (isAdding == true)
{
MessageBox.Show("Company Information Successfully Added", "Adding Company", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Company Information Successfully Updated", "Editing Company", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Saving Company Information Failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void AddEditVAT(bool isAdding)
{
try
{
if (isAdding == true)
{
SQLConn.sqL = "INSERT INTO VATSetting(VatPercent) VALUES(@VatPercent)";
}
else
{
SQLConn.sqL = "UPDATE VATSetting SET VatPercent=@VatPercent WHERE VATID=@VATID ";
}
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@VatPercent", txtPercent.Text);
if (isAdding == false)
{
SQLConn.cmd.Parameters.AddWithValue("@VATID", txtPercent.Tag);
}
int i = SQLConn.cmd.ExecuteNonQuery();
if (i > 0)
{
if (isAdding == true)
{
MessageBox.Show("VAT Information Successfully Added", "Adding VAT", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("VAT Information Successfully Updated", "Editing VAT", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Saving VAT Information Failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddCategory(string category)
{
try
{
SQLConn.sqL = "INSERT INTO Category(CategoryName, Description) VALUES('" + category + "','" + category + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void UpdateProductQuantity()
{
try
{
SQLConn.sqL = "UPDATE Product SET StocksOnhand = StocksOnHand + '" + txtQuantity.Text.Replace(",", "") + "' WHERE ProductNo = '" + productID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddStockIn()
{
try
{
SQLConn.sqL = "INSERT INTO StockIn(ProductNo, Quantity, DateIn) Values('" + lblProductNo.Text + "', '" + txtStocksOnHand.Text + "', '" + System.DateTime.Now.ToString("MM/dd/yyyy") + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddPayment()
{
try
{
SQLConn.sqL = "INSERT INTO PAYMENT(InvoiceNo, Cash, PChange) VALUES('" + InvoiceNo + "', '" + txtCash.Text.Replace(",", "") + "', '" + txtChange.Text.Replace(",", "") + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddStaff()
{
try
{
SQLConn.sqL = "INSERT INTO STAFF(Lastname, Firstname, MI, Street, Barangay, City, Province, ContactNo, Username, Role, UPassword) VALUES('" + txtLastname.Text + "', '" + txtFirstname.Text + "', '" + txtMI.Text + "', '" + txtStreet.Text + "', '" + txtBarangay.Text + "', '" + txtCity.Text + "', '" + txtProvince.Text + "', '" + txtContractNo.Text + "', '" + txtUsername.Text + "', '" + txtRole.Text + "', '" + txtPassword.Text + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("New staff successfully added.", "Add Staff", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void UpdateStaff()
{
try
{
SQLConn.sqL = "Update STAFF SET Lastname = '" + txtLastname.Text + "', Firstname = '" + txtFirstname.Text + "', MI = '" + txtMI.Text + "', Street= '" + txtStreet.Text + "', Barangay = '" + txtBarangay.Text + "', City = '" + txtCity.Text + "', Province = '" + txtProvince.Text + "', ContactNo = '" + txtContractNo.Text + "', Username ='******', Role = '" + txtRole.Text + "', UPassword = '******' WHERE StaffID = '" + LSStaffID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Staff record successfully updated", "Update Staff", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddCategory()
{
try
{
SQLConn.sqL = "INSERT INTO Category(CategoryName, Description) VALUES('" + txtCatName.Text + "', '" + txtDescription.Text + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("New category successfully added.", "Add Category", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void UpdateCategory()
{
try
{
SQLConn.sqL = "UPDATE Category SET CategoryName= '" + txtCatName.Text + "', Description = '" + txtDescription.Text + "' WHERE CategoryNo = '" + categoryID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Category successfully updated.", "Update Category", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadTransaction(string strSearch)
{
try
{
SQLConn.sqL = "SELECT t.SaleDate,t.id,t.InvoiceNo,t.ProductNo,t.ItemPrice, t.Quantity, t.Discount," +
"P.Description, Barcode, UnitPrice, StocksOnHand, ReorderLevel, CategoryName FROM TransactionDetails t, Product as P LEFT JOIN Category C ON P.CategoryNo = C.CategoryNo WHERE p.ProductNo=t.ProductNo and P.Description LIKE @Description OR P.Barcode LIKE @Barcode ORDER BY Description";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@Description", "%" + strSearch + "%");
SQLConn.cmd.Parameters.AddWithValue("@Barcode", "%" + strSearch + "%");
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["Id"].ToString());
x.SubItems.Add(SQLConn.dr["SaleDate"].ToString());
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
x.SubItems.Add(SQLConn.dr["Barcode"].ToString());
x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["UnitPrice"].ToString(), "#,##0.00"));
x.SubItems.Add(SQLConn.dr["ItemPrice"].ToString());
x.SubItems.Add(SQLConn.dr["Quantity"].ToString());
x.SubItems.Add(SQLConn.dr["PaymentMethod"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void UpdateProduct()
{
try
{
SQLConn.sqL = "UPDATE Product SET ProductCode = '" + txtProductCode.Text + "', Description = '" + txtDescription.Text + "', Barcode = '" + txtBarcode.Text.Trim() + "', UnitPrice = '" + txtUnitPrice.Text.Replace(",", "") + "', StocksOnHand = '" + txtStocksOnHand.Text.Replace(",", "") + "', ReorderLevel = '" + txtReorderLevel.Text + "', CategoryNo ='" + categoryID + "' WHERE ProductNo = '" + productID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Product successfully Updated.", "Update Product", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadProducts(string strSearch)
{
try
{
SQLConn.sqL = "SELECT ProductNo, ProductCOde, P.Description, Barcode, UnitPrice, StocksOnHand, ReorderLevel, CategoryName FROM Product as P LEFT JOIN Category C ON P.CategoryNo = C.CategoryNo WHERE P.Description LIKE @Description OR P.Barcode LIKE @Barcode ORDER BY Description";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@Description", "%" + strSearch + "%");
SQLConn.cmd.Parameters.AddWithValue("@Barcode", "%" + strSearch + "%");
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["Description"].ToString());
x.SubItems.Add(SQLConn.dr["Barcode"].ToString());
x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["UnitPrice"].ToString(), "#,##0.00"));
x.SubItems.Add(SQLConn.dr["StocksOnHand"].ToString());
x.SubItems.Add(SQLConn.dr["ReOrderLevel"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddStockIn()
{
try
{
SQLConn.sqL = "INSERT INTO StockIn(ProductNo, Quantity, DateIn) Values('" + productID + "', '" + txtQuantity.Text + "', '" + System.DateTime.Now.ToString("MM/dd/yyyy") + "')";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Stocks successfully added.", "Add Stocks", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
UpdateProductQuantity();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void Addproducts(string productCode, string productName, string barcodeImport, double unitPrice, int stocks, int categoryNo, int reOrderLevel)
{
string barcode = "";
if (barcodeImport.Trim() == "")
{
barcode = "NO BARCODE";
}
else
{
barcode = barcodeImport.Trim();
}
try
{
SQLConn.sqL = "INSERT INTO PRODUCT(ProductCode, Description, Barcode, UnitPrice, StocksOnhand, CategoryNo, ReOrderLevel) VALUES(@ProductCode, @Description, @Barcode, @UnitPrice, @StocksOnhand, @CategoryNo, @ReOrderLevel)";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@ProductCode", productCode);
SQLConn.cmd.Parameters.AddWithValue("@Description", productName);
SQLConn.cmd.Parameters.AddWithValue("@Barcode", barcode);
SQLConn.cmd.Parameters.AddWithValue("@UnitPrice", unitPrice);
SQLConn.cmd.Parameters.AddWithValue("@StocksOnhand", stocks);
SQLConn.cmd.Parameters.AddWithValue("@CategoryNo", categoryNo);
SQLConn.cmd.Parameters.AddWithValue("@ReOrderLevel", reOrderLevel);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void Login()
{
try
{
SQLConn.sqL = "SELECT * FROM Staff WHERE Username = '******' AND UPassword = '******'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);//System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
if (SQLConn.dr["Role"].ToString().ToUpper() == "ADMIN")
{
frmMain m = new frmMain(SQLConn.dr["Username"].ToString(), Convert.ToInt32(SQLConn.dr["StaffID"]));
m.Show();
this.Hide();
}
else
{
}
}
else
{
MessageBox.Show("Invalid Password. Please try again.", "Login");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadProducts(string barcode)
{
try
{
SQLConn.sqL = "SELECT ProductNo, Barcode, ProductCOde, P.Description, UnitPrice FROM Product as P WHERE P.Barcode LIKE @Barcode ORDER BY Description";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@Barcode", barcode);
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["Barcode"].ToString());
x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
x.SubItems.Add(String.Format(SQLConn.dr["UnitPrice"].ToString(), "#,##0.00"));
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadUpdateStaff()
{
try
{
SQLConn.sqL = "SELECT * FROM STAFF WHERE StaffID = '" + LSStaffID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = SQLConn.dr["StaffID"].ToString();
txtLastname.Text = SQLConn.dr["lastname"].ToString();
txtFirstname.Text = SQLConn.dr["Firstname"].ToString();
txtMI.Text = SQLConn.dr["MI"].ToString();
txtStreet.Text = SQLConn.dr["Street"].ToString();
txtBarangay.Text = SQLConn.dr["barangay"].ToString();
txtCity.Text = SQLConn.dr["City"].ToString();
txtProvince.Text = SQLConn.dr["Province"].ToString();
txtContractNo.Text = SQLConn.dr["ContactNo"].ToString();
txtUsername.Text = SQLConn.dr["username"].ToString();
txtRole.Text = SQLConn.dr["Role"].ToString();
txtPassword.Text = SQLConn.dr["UPassword"].ToString();
txtConfirmPWD.Text = SQLConn.dr["UPassword"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void frmMain_Load(object sender, EventArgs e)
{
SQLConn.getData();
this.lbluser.Text = "Login user : " + Username.ToUpper();
}
