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