public override Table GetTable(ulong tableID) { Table result = Table.Null; string statement = "SELECT * FROM Tables WHERE TableID=@ID"; try { using (SqlConnection sqlConn = new SqlConnection(GetConnectionString())) { sqlConn.Open(); if (sqlConn.State == ConnectionState.Open) { SqlCommand sqlQuery = new SqlCommand(statement, sqlConn); sqlQuery.Parameters.AddWithValue("@ID", tableID); SqlDataReader sqlDR = sqlQuery.ExecuteReader(); if (sqlDR.Read()) { Table t = new Table(); t.TableID = (ulong)sqlDR["TableID"]; t.TableNumber = (uint)sqlDR["TableNumber"]; t.AmountOfChairs = (uint)sqlDR["AmountOfChairs"]; return t; } result = Table.Null; } } } catch { result = Table.Null; } return result; }
private void btnCreateNewOrder_Click(object sender, EventArgs e) { try { ulong clientid = Convert.ToUInt64(tbxClientID_tpOrderCreation.Text); int personsCount = 0; List<Table> tableCount = new List<Table>(); int.TryParse(tbxAmountOfPersons_tpOrderCreation.Text, out personsCount); List<ulong> tableidList = new List<ulong>(); string[] TableIds = this.tbxTableID_tpOrderCreation.Text.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); foreach (String s in TableIds) { tableidList.Add(Convert.ToUInt64(s)); } foreach (ulong ul in tableidList) { //personsCount -= new Database().GetAllTables().Find(new Predicate<Table>(new Table() { TableID = ul })).AmountOfChairs; Table tb = new Table(); foreach(Table t in new Database().GetAllTables()) { if (t.TableID == ul) { tb = t; tableCount.Add(t); } } personsCount -= (int)tb.AmountOfChairs; } if (personsCount > 0) { MessageBox.Show("Please select the table(s) first"); return; } ulong? orderid = null; //string orders = lbxSelectedMenuItems.Text.Replace("\n", ""); List<String> orders = new List<String>(); foreach (Object o in this.lbxSelectedMenuItems.Items) { orders.Add(o.ToString().Replace(",", "")); } //lbxSelectedMenuItems.Items.CopyTo(orders.ToArray<String>(), 0); DateTime TimeToInject = dtpOrderDate.Value; TimeToInject = TimeToInject.AddHours(Convert.ToInt32(cbxOrderHour.SelectedItem) - TimeToInject.Hour); TimeToInject = TimeToInject.AddMinutes(Convert.ToInt32(cbxOrderMinute.SelectedItem) - TimeToInject.Minute); new Database().Insert(Database.TableName.Orders, new String[] { this.tbxClientID_tpOrderCreation.Text, TimeToInject.ToString("yyyy-MM-dd HH:mm:ss"), TimeToInject.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss") }); //Injects Succesfull orderid = (ulong)new Database().GetOrderID((ulong)int.Parse(this.tbxClientID_tpOrderCreation.Text), TimeToInject.ToString("yyyy-MM-dd HH:mm:ss"), TimeToInject.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss")); for(int i = 0; i < int.Parse(tbxAmountOfPersons_tpOrderCreation.Text); i++) { int tableindex; int tablenr = GetTableNumber(tableCount, out tableindex); if (orders.Count > i) new Database().Insert(Database.TableName.Persons, new String[] { orders[i].ToString(), orderid.ToString(), tablenr.ToString() }); else new Database().Insert(Database.TableName.Persons, new String[] { "NULL", orderid.ToString(), tablenr.ToString() }); int decreasedAmountOfChairs = (Convert.ToInt32(tableCount[tableindex].AmountOfChairs) - 1); int decreasedID = (int)tableCount[tableindex].TableID; int decreasedTableNR = (int)tableCount[tableindex].TableNumber; List<Table> TempTableList = new List<Table>(); foreach (Table t in tableCount) { if ((int)t.TableID == tablenr) TempTableList.Add(new Table((ulong)decreasedID, (uint)decreasedTableNR, (uint)decreasedAmountOfChairs)); else TempTableList.Add(t); } tableCount.Clear(); tableCount.AddRange(TempTableList); } foreach (Table tb in tableCount) { new Database().Insert(Database.TableName.TableOrders, new String[] { tb.TableID.ToString(), orderid.ToString(), false.ToString() }); } new Database().UpdateClientVisit(ulong.Parse(this.tbxClientID_tpOrderCreation.Text)); this.pnlOverview.Controls.Clear(); this.pnlOverview.Update(); this.CreateTableLayoutData(); this.tpClientSelect.Enabled = true; this.tpOrderCreation.Enabled = false; this.tpMenuSelection.Enabled = false; this.tpTableSelection.Enabled = false; this.tctrlCreateOrder.SelectedTab = this.tpClientSelect; MessageBox.Show("Order Succesful,\n"+ "Mr./Ms. " + new Database().GetClient(clientid).LastName + "\n" + "Can seat around " + new Database().GetOrder((ulong)orderid).StartDateTime.Value.TimeOfDay.ToString(), "Order Succesfull", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1 ); } catch ( Exception ex ) { MessageBox.Show("Please fill the records with valid data."); return; } }
public override List<Table> GetAllTables() { List<Table> tables = new List<Table>(); string statement = "SELECT * FROM Tables"; try { using (SqlConnection sqlConn = new SqlConnection(GetConnectionString())) { sqlConn.Open(); if (sqlConn.State == ConnectionState.Open) { SqlCommand sqlQuery = new SqlCommand(statement, sqlConn); SqlDataReader sqlDR = sqlQuery.ExecuteReader(); while (sqlDR.Read()) { Table t = new Table(); t.TableID = Convert.ToUInt64(sqlDR["TableID"]); t.TableNumber = Convert.ToUInt32(sqlDR["TableNumber"]); t.AmountOfChairs = Convert.ToUInt32(sqlDR["AmountOfChairs"]); tables.Add(t); } return tables; } return null; } } catch { return null; } }