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 GetVATInfo() { try { SQLConn.sqL = "SELECT VATID, VatPercent FROM VatSetting"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.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) { MessageBox.Show(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
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(); } }
private void GetStaffID() { try { SQLConn.sqL = "SELECT StaffID FROM STAFF ORDER BY StaffID 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["StaffID"]) + 1).ToString(); } else { lblProductNo.Text = "1"; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } 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 GetCompanyInfo() { try { SQLConn.sqL = "SELECT CompanyID, Name, Address, PhoneNo, Email, Website, TINNumber FROM Company"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.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) { MessageBox.Show(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void GetCategoryNo() { try { SQLConn.sqL = "SELECT CategoryNo FROM Category ORDER BY CategoryNo DESC"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblCategoryNo.Text = (Convert.ToInt32(SQLConn.dr["CategoryNo"]) + 1).ToString(); } else { lblCategoryNo.Text = "1"; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
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 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 bool IsAdding() { bool ret = false; try { SQLConn.sqL = "SELECT * 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 = false; } else { ret = true; } } 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 bool IsPasswordCorrect() { bool ret = false; try { SQLConn.sqL = "SELECT * FROM Staff WHERE Role ='Admin' AND UPassword = '******'"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
private void GetProductInfo() { try { SQLConn.sqL = "SELECT ProductCode, Description, UnitPrice, StocksOnHand FROM Product 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) { lblProductCode.Text = SQLConn.dr[0].ToString(); lblDescription.Text = SQLConn.dr[1].ToString(); lblPrice.Text = String.Format(SQLConn.dr[2].ToString(), "#.000").ToString(); lblCurrentStocks.Text = SQLConn.dr[3].ToString(); } } 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 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(); } }
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 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(); } }
private void LoadUpdateCategory() { try { SQLConn.sqL = "SELECT * FROM Category WHERE CategoryNo = '" + categoryID + "'"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.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) { MessageBox.Show(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadReport() { try { if (InvoiceSetting() == 1) { SQLConn.sqL = "SELECT ProductCode, P.Description, TDate as DateOut, SUM(TD.Quantity) as Quantity, TD.ItemPrice as Price, (SUM(TD.Quantity) * TD.ItemPrice) as TotalAmount FROM Product as P, Transactions as T, TransactionDetails as TD WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' AND T.Status != 1 GROUP BY P.ProductNo, TDate ORDER By TDate, Description"; } else { SQLConn.sqL = "SELECT ProductCode, P.Description,TDate as DateOut, SUM(TD.Quantity) as Quantity, TD.ItemPrice as Price, (SUM(TD.Quantity) * TD.ItemPrice) as TotalAmount FROM Product as P, Transactions as T, TransactionDetails as TD WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, TDate ORDER By TDate, 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.StocksOut.Clear(); // SQLConn.da.Fill(this.dsReportC.StocksOut); 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) { MessageBox.Show(ex.ToString()); } }
private void AddProducts() { string barcode = ""; if (txtBarcode.Text.Trim() == "") { barcode = "NO BARCODE"; } else { barcode = txtBarcode.Text; } try { SQLConn.sqL = "INSERT INTO TransactionDetail(Id,SaleDate,InvoiceNo,ProductNo, ItemPrice, Quantity, Discount) VALUES('" + lblProductNo.Text + "', '" + textSaleDate.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(); } }
public void LoadCategories(string strSearch) { try { SQLConn.sqL = "SELECT * FROM CATEGORY WHERE CategoryName LIKE '" + strSearch + "%' ORDER By CategoryName"; SQLConn.ConnDB(); SQLConn.cmd = new System.Data.SqlClient.SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(CommandBehavior.CloseConnection); 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(); } }
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 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(); } }
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(); } }