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!"); } }
private string CheckMaintananceRecords(Drone drone, BookingDates dates) { var maintananceRecordQuery = String.Format("SELECT * FROM {0}.drone WHERE {1}=(false) AND {2}=('{3}') AND {4}=('{5}') AND {6}=('{7}')" , connection.database, "available", "droneType", drone.type, "drone.range", drone.range, "manufacturer", drone.manufacturer); var availableDroneQuery = String.Format("SELECT * FROM {0}.drone WHERE {1}=(true) AND {2}=('{3}') AND {4}=('{5}') AND {6}=('{7}')" , connection.database, "available", "droneType", drone.type, "drone.range", drone.range, "manufacturer", drone.manufacturer); var dronesInMaintanance = connection.Select(maintananceRecordQuery); var availableDrones = connection.Select(availableDroneQuery); var builder = new StringBuilder(); builder.Append("Here are the drones in maintanence: \n"); foreach (var d in dronesInMaintanance) { foreach (var droneColumn in d) { builder.Append(string.Format("{0} = {1}\n", droneColumn.Key, droneColumn.Value)); } builder.Append("\n"); } builder.Append("Here are the drones available: \n"); foreach (var d in availableDrones) { foreach (var droneColumn in d) { builder.Append(string.Format("{0} = {1}\n", droneColumn.Key, droneColumn.Value)); } builder.Append("\n"); } return(builder.ToString()); }
private double CheckClientForOutStandingFines(Booking booking) { var clientBookings = string.Format("SELECT * FROM {0}.booking WHERE clientId={1} AND droneId={2}", connection.database, booking.clientId, booking.droneId); var bookings = connection.Select(clientBookings); if (bookings == null || bookings[0].Count == 0) { return(0); } if (!String.IsNullOrEmpty(bookings[0]["finesDue"])) { return(Double.Parse(bookings[0]["finesDue"])); } return(0); }