// // // WILSON - START // // // // // VINCENT - START // // private void UpdateData() { invoiceCollection = new Collection<ReportData_Invoice>(); OleDbConnection cn = new OleDbConnection(); //Connection OleDbCommand cmd = new OleDbCommand(); //Command OleDbDataReader dr; String path = Assembly.GetExecutingAssembly().Location; path = path.Replace("bin\\Debug\\ICTProjectPOS.exe", "RestaurantDB.accdb"); String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", path); cn.ConnectionString = @connectionString; cmd.Connection = cn; try { cmd.CommandText = "SELECT * FROM Invoice WHERE DineDate = ? ORDER BY InvoiceNum"; cmd.Parameters.AddWithValue("DineDate", selectedDate); cn.Open(); dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { //Create new ReportData ReportData_Invoice invoice = new ReportData_Invoice(); //SetInvoiceNumber invoice.SetInvoiceNumber(dr[0].ToString()); //SetTimeStamp invoice.SetTimeStamp(dr[4].ToString().Substring(11)); //Output is 'dd/mm/yyyy hh:mm:ss AM', so we remove the first 11 char to get 'hh:mm:ss: AM' //SetAmountDue invoice.SetAmountDue(dr[5].ToString().Replace("$", "")); //SetPaymentMethod invoice.SetPaymentMethod(dr[6].ToString()); //SetItemsOrdered OleDbCommand cmd2 = new OleDbCommand(); cmd2.Connection = cn; cmd2.CommandText = "SELECT * FROM InvoiceItem WHERE InvoiceNum = ?"; cmd2.Parameters.AddWithValue("InvoiceNum", dr[0].ToString()); OleDbDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { while (dr2.Read()) { ReportData_InvoiceItem invoiceItem = new ReportData_InvoiceItem(dr2[1].ToString(), Int32.Parse(dr2[2].ToString())); OleDbCommand cmd3 = new OleDbCommand(); cmd3.Connection = cn; cmd3.CommandText = "SELECT * FROM Item WHERE ItemID = ?"; cmd3.Parameters.AddWithValue("ItemID", dr2[1].ToString()); OleDbDataReader dr3 = cmd3.ExecuteReader(); if(dr3.HasRows) { while (dr3.Read()) { invoiceItem.SetPricePerItem(dr3[2].ToString()); invoice.AddInvoiceItem(invoiceItem); } } } } //AddToReportDataCollection invoiceCollection.Add(invoice); } } cn.Close(); } catch { //empty } }
// // // WILSON - START // // // // // VINCENT - START // // private void UpdateData() { invoiceCollection = new Collection <ReportData_Invoice>(); OleDbConnection cn = new OleDbConnection(); //Connection OleDbCommand cmd = new OleDbCommand(); //Command OleDbDataReader dr; String path = Assembly.GetExecutingAssembly().Location; path = path.Replace("bin\\Debug\\ICTProjectPOS.exe", "RestaurantDB.accdb"); String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", path); cn.ConnectionString = @connectionString; cmd.Connection = cn; try { cmd.CommandText = "SELECT * FROM Invoice WHERE DineDate = ? ORDER BY InvoiceNum"; cmd.Parameters.AddWithValue("DineDate", selectedDate); cn.Open(); dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { //Create new ReportData ReportData_Invoice invoice = new ReportData_Invoice(); //SetInvoiceNumber invoice.SetInvoiceNumber(dr[0].ToString()); //SetTimeStamp invoice.SetTimeStamp(dr[4].ToString().Substring(11)); //Output is 'dd/mm/yyyy hh:mm:ss AM', so we remove the first 11 char to get 'hh:mm:ss: AM' //SetAmountDue invoice.SetAmountDue(dr[5].ToString().Replace("$", "")); //SetPaymentMethod invoice.SetPaymentMethod(dr[6].ToString()); //SetItemsOrdered OleDbCommand cmd2 = new OleDbCommand(); cmd2.Connection = cn; cmd2.CommandText = "SELECT * FROM InvoiceItem WHERE InvoiceNum = ?"; cmd2.Parameters.AddWithValue("InvoiceNum", dr[0].ToString()); OleDbDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { while (dr2.Read()) { ReportData_InvoiceItem invoiceItem = new ReportData_InvoiceItem(dr2[1].ToString(), Int32.Parse(dr2[2].ToString())); OleDbCommand cmd3 = new OleDbCommand(); cmd3.Connection = cn; cmd3.CommandText = "SELECT * FROM Item WHERE ItemID = ?"; cmd3.Parameters.AddWithValue("ItemID", dr2[1].ToString()); OleDbDataReader dr3 = cmd3.ExecuteReader(); if (dr3.HasRows) { while (dr3.Read()) { invoiceItem.SetPricePerItem(dr3[2].ToString()); invoice.AddInvoiceItem(invoiceItem); } } } } //AddToReportDataCollection invoiceCollection.Add(invoice); } } cn.Close(); } catch { //empty } }