private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT ProductCode, P.Description, STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') 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 MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new MySqlDataAdapter(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 = 40;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
public void priftLoss()
{
try
{
SQLConn.sqL = "select SUM(Total),sum(PurchasePrice * Quantity) from transactiondetails INNER JOIN transactions ON transactiondetails.InvoiceNo = transactions.InvoiceNo where TDate like '7/9/2019' ";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
//txtName = SQLConn.dr[0].ToString();
//txtName.Text = SQLConn.dr[1].ToString();
totalselling = int.Parse(SQLConn.dr[0].ToString());
totalpurchase = int.Parse(SQLConn.dr[1].ToString());
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private bool IsPasswordCorrect()
{
bool ret = false;
try
{
SQLConn.sqL = "SELECT * FROM Staff WHERE Role ='Admin' AND UPassword = '******'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
private void GetProductNo()
{
try
{
SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = (Convert.ToInt32(SQLConn.dr["ProductNo"]) + 1).ToString();
}
else
{
lblProductNo.Text = "1";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
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 '" + strSearch + "%' ORDER BY Description";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["ProductNo"].ToString());
x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
x.SubItems.Add(Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00"));
x.SubItems.Add(SQLConn.dr["StocksOnHand"].ToString());
x.SubItems.Add(SQLConn.dr["ReOrderLevel"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void GetInvoiceNo()
{
try
{
SQLConn.sqL = "SELECT Id FROM Invoice ORDER BY Id DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
txtInvoiceNo.Text = "INV-000-" + (Convert.ToInt32(SQLConn.dr["Id"]) + 1).ToString();
}
else
{
txtInvoiceNo.Text = "INV-000-1";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadInvoices(DateTime startDate, DateTime endDate)
{
try
{
SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName, TotalAmount, Status FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
dgw.Rows.Clear();
string status = "";
while (SQLConn.dr.Read() == true)
{
if (Convert.ToInt32(SQLConn.dr["Status"]) == 0)
{
status = "Hide";
}
else
{
status = "Unhide";
}
dgw.Rows.Add(status, Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy"), SQLConn.dr["InvoiceNo"].ToString(), SQLConn.dr["StaffName"].ToString(), Strings.FormatNumber(SQLConn.dr["TotalAmount"]));
}
}
catch (Exception ex)
{
Interaction.MsgBox(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 MySqlCommand(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(Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00"));
x.SubItems.Add(Strings.Format(SQLConn.dr["ExtendedPrice"], "#,##0.00"));
//Strings.Format(SQLConn.dr["ExtendedPrice"], "#,##0.00")
ListView2.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
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 MySqlCommand(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)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadReceiptInfo()
{
try
{
SQLConn.sqL = "SELECT T.InvoiceNo, CONCAT(Lastname,', ', Firstname, ' ', MI, '.') as StaffName, Quantity, Description, ItemPrice, (ItemPRice * Quantity) as ItemAmount, VatAmount, NonVatTotal, TotalAmount, Cash, PChange FROM Product as P, TransactionDetails as TD, Transactions as T, Staff as S, Payment as Pay WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND S.StaffID = T.StaffID AND Pay.InvoiceNo = T.InvoiceNO AND T.InvoiceNo = '" + InvoiceNo + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblInvoice.Text = SQLConn.dr["InvoiceNo"].ToString();
lblEmpName.Text = SQLConn.dr["StaffName"].ToString();
lblDate.Text = System.DateTime.Now.ToString("ddMMMyy") + " " + System.DateTime.Now.ToString("hh:mm tt");
lblVat.Text = Strings.FormatNumber(SQLConn.dr["VatAmount"]);
lblSubtotal.Text = Strings.FormatNumber(SQLConn.dr["NonVatTotal"]);
lblTotal.Text = Strings.FormatNumber(SQLConn.dr["TotalAmount"]);
lblCash.Text = Strings.FormatNumber(SQLConn.dr["Cash"]);
lblChange.Text = Strings.FormatNumber(SQLConn.dr["PChange"]);
LoadItemstoDatagrid();
}
}
catch (Exception ex)
{
Interaction.MsgBox(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 ORDER BY ReturnDate, sr.InvoiceNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["SalesReturnId"].ToString());
x.SubItems.Add(Strings.Format(SQLConn.dr["ReturnDate"], "MM/dd/yyyy"));
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
x.SubItems.Add(Strings.Format(SQLConn.dr["TotalAmount"], "#,##0.00"));
x.SubItems.Add(SQLConn.dr["TotalQuantity"].ToString());
//Strings.Format(SQLConn.dr["TotalAmount"], "#,##0.00")
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void GetVATInfo()
{
try
{
SQLConn.sqL = "SELECT VATID, VatPercent FROM VatSetting";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
txtPercent.Tag = SQLConn.dr[0];
txtPercent.Text = SQLConn.dr[1].ToString();
isAddingVat = false;
}
else
{
isAddingVat = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private bool IsAdding()
{
bool ret = false;
try
{
SQLConn.sqL = "SELECT * FROM Company";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
ret = false;
}
else
{
ret = true;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(ret);
}
private void GetProductInfo()
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description,PurchasePrice, StocksOnHand FROM Product WHERE ProductNo =" + productID + "";
SQLConn.ConnDB();
SQLConn.cmd = new 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 = Strings.FormatNumber(SQLConn.dr[2]).ToString();
lblCurrentStocks.Text = SQLConn.dr[3].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void GetCustomerInfo(int custId)
{
try
{
SQLConn.sqL = "SELECT address FROM customer where id = " + custId;
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
txtAddress.Text = SQLConn.dr[0].ToString();
txtShipTo.Text = SQLConn.dr[0].ToString();
}
}
catch (Exception ex)
{
//Interaction.MsgBox(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 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();
txtStreet.Text = SQLConn.dr["Street"].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["Password"].ToString();
txtConfirmPWD.Text = SQLConn.dr["Password"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private double GetTax()
{
double vatDecimal = 0;
try
{
SQLConn.sqL = "SELECT VatPercent FROm VatSetting";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
vatDecimal = (Convert.ToDouble(SQLConn.dr[0]) / 100);
tax_percent = Convert.ToDouble(SQLConn.dr[0]);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
return(vatDecimal);
}
//Decrease product's stocks on hand
public void UpdateProductQuantity()
{
try
{
SQLConn.sqL = "UPDATE Product SET StocksOnHand = StocksOnHand - @Quantity WHERE ProductNo = @ProductNo";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32);
SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double);
for (int i = 0; i <= dgw.Rows.Count - 1; i++)
{
SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value;
SQLConn.cmd.Parameters["Quantity"].Value = Convert.ToDouble(dgw.Rows[i].Cells[1].Value);
SQLConn.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadInvoices(DateTime startDate, DateTime endDate, string searchString)
{
try
{
SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy"));
x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString());
x.SubItems.Add(SQLConn.dr["StaffName"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void UpdateInvoice()
{
try
{
SQLConn.sqL = "UPDATE invoice SET InvoiceDate=@InvoiceDate, CustomerPONo=@CustomerPONo, Terms=@Terms, Duration=@Duration, SalesPerson=@SalesPerson, DiscountPercent=@DiscountPercent, DiscountAmount=@DiscountAmount, TotalAmount=@TotalAmount, Subtotal=@Subtotal, TaxAmount=@TaxAmount, Note=@Note, CustomerId =@CustomerId, BillAddress=@BillAddress, Customer=@Customer WHERE InvoiceNo=@InvoiceNo";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@InvoiceDate", dtpDate.Value.ToString("yyyy-MM-dd"));
SQLConn.cmd.Parameters.AddWithValue("@InvoiceNo", txtInvoiceNo.Text);
SQLConn.cmd.Parameters.AddWithValue("@CustomerPONo", txtPONumber.Text);
SQLConn.cmd.Parameters.AddWithValue("@Terms", cmbTerms.Text);
SQLConn.cmd.Parameters.AddWithValue("@Duration", txtDuration.Text);
SQLConn.cmd.Parameters.AddWithValue("@SalesPerson", txtSalesperson.Text);
SQLConn.cmd.Parameters.AddWithValue("@DiscountPercent", lblDiscountPercent.Text);
SQLConn.cmd.Parameters.AddWithValue("@DiscountAmount", lblDiscountAmount.Text.Replace(",", ""));
SQLConn.cmd.Parameters.AddWithValue("@TotalAmount", lblTotalAmount.Text.Replace(",", ""));
SQLConn.cmd.Parameters.AddWithValue("@Subtotal", lblSubtotal.Text.Replace(",", ""));
SQLConn.cmd.Parameters.AddWithValue("@TaxAmount", lblTax.Text.Replace(",", ""));
SQLConn.cmd.Parameters.AddWithValue("@Note", txtNoteComment.Text);
SQLConn.cmd.Parameters.AddWithValue("@CustomerId", customer_id);
SQLConn.cmd.Parameters.AddWithValue("@BillAddress", txtAddress.Text);
SQLConn.cmd.Parameters.AddWithValue("@Customer", cmbCustomer.Text);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
is_invoice_success = false;
Interaction.MsgBox(ex.ToString());
}
}
private void LoadCompanySetting()
{
try
{
SQLConn.sqL = "SELECT HInvoice FROM Company";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
if (Convert.ToInt32(SQLConn.dr["HInvoice"]) == 1)
{
checkBox1.Checked = true;
}
else
{
checkBox1.Checked = false;
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void GetInvoiceShipping()
{
try
{
SQLConn.sqL = "SELECT ShiptoAddress, ShipBy, TrackingNo, ShippingCost, ShippingTax FROM InvoiceShipping where InvoiceNo ='" + txtInvoiceNo.Text + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
txtShipTo.Text = SQLConn.dr[0].ToString();
txtShipBy.Text = SQLConn.dr[1].ToString();
txtTrackingNo.Text = SQLConn.dr[2].ToString();
txtShippingCost.Text = SQLConn.dr[3].ToString();
cmbShippingTax.Text = SQLConn.dr[4].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(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 MySqlCommand(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();
txtCategory.Text = SQLConn.dr["CategoryName"].ToString();
txtCategory.Tag = SQLConn.dr["CategoryNo"];
txtUnitPrice.Text = Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00");
txtStocksOnHand.Text = SQLConn.dr["StocksOnHand"].ToString();
txtReorderLevel.Text = SQLConn.dr["ReorderLevel"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddItemDisplay(int itemNo)
{
try
{
SQLConn.sqL = "SELECT ProductNo, ProductCode, Description, UnitPrice, ProductNo FROM Product WHERE ProductNo = '" + itemNo + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
dgw.Rows.Add(SQLConn.dr[0].ToString(), 1, SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), Strings.FormatNumber(SQLConn.dr[3]).ToString(), '0', cmbCustomerTax.Text, Strings.FormatNumber(SQLConn.dr[3]).ToString(), "Remove");
}
//display amounts
ComupteAmount();
//dgw.SelectedRows.Clear();
dgw.ClearSelection();
selected_item = "";
//disable remove button
btnRemove.Enabled = false;
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
public void totalAmount()
{
try
{
SQLConn.sqL = "SELECT sum(TotalAmount) FROM transactions WHERE TDate BETWEEN '" + fromdate.Value.ToShortDateString() + "' AND '" + todate.Value.ToShortDateString() + "'";
SQLConn.ConnDB();
SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
//txtName = SQLConn.dr[0].ToString();
//txtName.Text = SQLConn.dr[1].ToString();
total = SQLConn.dr[0].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadCustomer()
{
try
{
SQLConn.sqL = "SELECT Id, CONCAT(Lastname,', ', Firstname) customername FROM customer ORDER BY lastname";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
cmbCustomer.Items.Clear();
Dictionary <string, string> customer = new Dictionary <string, string>();
while (SQLConn.dr.Read() == true)
{
customer.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString());
}
cmbCustomer.DataSource = new BindingSource(customer, null);
cmbCustomer.DisplayMember = "Value";
cmbCustomer.ValueMember = "Key";
cmbCustomer.SelectedIndex = -1;
}
catch (Exception)
{
// Interaction.MsgBox(ex.ToString());
}
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description, SUM(Quantity) as Quantity, STR_TO_DATE(REPLACE(DateIn, '-', '/'), '%m/%d/%Y') as DateIn FROM Product as P, StockIn as S WHERE S.ProductNo = P.ProductNo AND STR_TO_DATE(REPLACE(DateIn, '-', '/'), '%m/%d/%Y') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, DateIN ORDER BY DateIn, Description";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new MySqlDataAdapter(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(DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 40;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
private void getStaffInfo()
{
try
{
SQLConn.sqL = "SELECT CONCAT(lastname, ', ', Firstname, ' ', MI) as StaffName, StaffId FROM Staff WHERE StaffID = '" + SQLConn.staffid + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
txtSalesperson.Tag = SQLConn.dr["StaffId"].ToString();
txtSalesperson.Text = SQLConn.dr["Staffname"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
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 SqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
//txtName = SQLConn.dr[0].ToString();
//txtName.Text = SQLConn.dr[1].ToString();
txtAddress = SQLConn.dr[2].ToString();
// txtPhoneNo = SQLConn.dr[3].ToString();
txtEmail = SQLConn.dr[4].ToString();
txtWebsite = SQLConn.dr[5].ToString();
//txtTINNumber.Text = SQLConn.dr[6].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadProducts(string strSearch)
{
try
{
SQLConn.sqL = "SELECT ProductNo, ProductCOde, P.Description, UnitPrice, StocksOnHand FROM Product as P LEFT JOIN Category C ON P.CategoryNo = C.CategoryNo WHERE P.Description LIKE @Description OR P.ProductCOde LIKE @ProductCOde ORDER BY Description";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@Description", "%" + strSearch + "%");
SQLConn.cmd.Parameters.AddWithValue("@ProductCOde", "%" + strSearch + "%");
SQLConn.dr = SQLConn.cmd.ExecuteReader();
dgw.Rows.Clear();
while (SQLConn.dr.Read() == true)
{
dgw.Rows.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), Strings.Format(SQLConn.dr[3], "#,##0.00"), SQLConn.dr[4].ToString());
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}