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);
}