public String CreateBooking(Booking booking) { double outstandingFines = CheckClientForOutStandingFines(booking); if (outstandingFines < 0) { return(string.Format("Client with id {0} not found", booking.clientId)); } if (outstandingFines > 0) { return(string.Format("Client {0} has outstanding fines of ${1}", booking.clientId, outstandingFines)); } var insertQuery = GenerateInsertBookingQuery(booking); // Insert new booking int success = connection.Insert(insertQuery); if (success != 1) { return("Booking Failed! \n"); } int bookingId = CreateBookingId(booking); // Create new bookingId column table //CreateBookingIdTable(); var bookingBuilder = String.Format( "UPDATE {0}.booking SET bookingId='{1}' WHERE clientId=('{2}') AND droneId=('{3}') AND dateTaken=('{4}')", connection.database, bookingId, booking.clientId, booking.droneId, booking.dateTaken ); success = connection.Insert(bookingBuilder.ToString()); if (success != 1) { return("Failed to store bookingId!"); } return(String.Format("Booking Successful! Your booking id is {0} \n", bookingId)); }
public String runProcess(String clientName, String clientAddress) { // Check if this client already exists // SELECT count(*) FROM new_schema.client WHERE(clientName='Glendon') AND (clientAddress='Waterloo'); StringBuilder query = new StringBuilder(); query.Append("SELECT count(*) FROM " + conn.database + ".client WHERE(clientName='" + clientName); query.Append("') AND (clientAddress='" + clientAddress + "');"); if (conn.Select(query.ToString(), 1)[0][0] == "1") { return("Error: Client Exists!"); } //Produce new clientId for this client by checking the last added id //SELECT MAX(clientId) FROM new_schema.client; query = new StringBuilder(); query.Append("SELECT MAX(clientId) FROM " + conn.database + ".client;"); List <String>[] output = conn.Select(query.ToString(), 1); String id = conn.Select(query.ToString(), 1)[0][0]; int clientID = 1; if (id != null) { clientID = Int32.Parse(id) + 1; } //Insert the new client information //INSERT INTO sys.client (clientId, clientName, clientAddress) VALUES(2, 'Glen', 'Waterloo'); StringBuilder insert = new StringBuilder(); insert.Append("INSERT INTO " + conn.database + ".client (clientId, clientName, clientAddress) VALUES("); insert.Append(clientID + ", '" + clientName + "', '" + clientAddress + "');"); if (conn.Insert(insert.ToString()) == 1) { return("Client Added!"); } else { return("Error while inserting new client info!"); } }