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