/// <summary>
/// Constructor
/// </summary>
/// <param name="code">The unique identification code for the resource status</param>
/// <param name="description">A description of the resource status</param>
/// <param name="isAvailable">A boolean value used to determine if the resource status
/// means that the resource is currently available to attend emergencies.</param>
/// <param name="isMobile">A boolean value used to determine if the resource status
/// means that the resource is at a base station (false) or not (true).
/// Used to determine the available mobilising methods for a resource</param>
/// <param name="incidentLogAction">An enumeration that details the effect the resource status has on the incident the
/// resource is currently attatched to (if any) - the incident updates the appliance
/// information section with the time that each resource attached to the incident is
/// changed to a status with each enumeration value. This helps the user to see at a
/// glance the state of each appliance that was assigned to the incident.</param>
public ResourceStatus(int code, string description, bool isAvailable, bool isMobile, ResourceLogTime incidentLogAction)
{
Code = code;
Description = description;
IsAvailable = isAvailable;
IsMobile = isMobile;
IncidentLogAction = incidentLogAction;
}
/// <summary>
/// Retrieves a list of all resource states.
/// </summary>
/// <returns>A list of objects, each representing a single resource state.</returns>
public ResourceStatus[] GetAllResourceStates()
{
//holds the resource states
List <ResourceStatus> data = new List <ResourceStatus>();
//the SELECT statement
string statement = "SELECT * FROM " + database + ".Status;";
//the object that will execute the query
MySqlCommand command = new MySqlCommand(statement, connection);
//execute query
MySqlDataReader myReader = null; //reads the database data
try
{
// open the connection only if it is currently closed
if (command.Connection.State == System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
//close the reader if it currently exists
if (myReader != null && !myReader.IsClosed)
{
myReader.Close();
}
myReader = command.ExecuteReader(); //execute the select statement
//read the data sent back from MySQL server
while (myReader.Read())
{
int code = myReader.GetInt32(0);
string description = myReader.GetString(1);
bool isAvailable = myReader.GetBoolean(2);
bool isMobile = myReader.GetBoolean(3);
ResourceLogTime log = ResourceLogTime.Ignore;
try
{
log = (ResourceLogTime)myReader.GetInt32(4);
}
catch (Exception) { }
data.Add(new ResourceStatus(code, description, isAvailable, isMobile, log));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show(statement);
}
finally
{
//close the connections
if (myReader != null)
{
myReader.Close();
}
command.Connection.Close();
}
return(data.ToArray());
}
/// <summary>
/// Retrieves a resource using its unique call sign
/// </summary>
/// <param name="callSign">The call sign to retrieve</param>
/// <returns>An object representing the resource.
/// Note that this is the abstract base class and the returned object should be cast to an Appliance or Officer object.</returns>
public Appliance GetAppliance(string callSign)
{
string statement = "SELECT * FROM resource, appliance, address_resource, address, resource_status, status, ApplianceType, Base" + Environment.NewLine +
"INNER JOIN address AS base_address ON base.Id = base_address.Id" + Environment.NewLine +
"WHERE resource.CallSign = appliance.CallSign" + Environment.NewLine +
"AND resource.CallSign = address_resource.ResourceCallSign" + Environment.NewLine +
"AND address_resource.DateTime = ((SELECT MAX(Address_Resource.DateTime) FROM Address_Resource WHERE Address_Resource.ResourceCallSign = resource.CallSign))" + Environment.NewLine +
"AND address_resource.AddressId = address.Id" + Environment.NewLine +
"AND resource.CallSign = resource_status.ResourceCallSign" + Environment.NewLine +
"AND resource_status.StatusCode = status.Code" + Environment.NewLine +
"AND resource_status.DateTime = (SELECT MAX(resource_status.DateTime) FROM resource_status WHERE resource_status.ResourceCallSign = resource.CallSign)" + Environment.NewLine +
"AND appliance.ApplianceTypeName = ApplianceType.Name" + Environment.NewLine +
"AND resource.BaseLocationId = base.Id" + Environment.NewLine +
"AND resource.CallSign = @callsign;";
MySqlCommand command = new MySqlCommand(statement, connection);
command.Parameters.AddWithValue("@callsign", callSign);
//execute the query
MySqlDataReader myReader = null;
try
{
// open the connection only if it is currently closed
if (command.Connection.State == System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
//close the reader if it currently exists
if (myReader != null && !myReader.IsClosed)
{
myReader.Close();
}
myReader = command.ExecuteReader();
//read the data sent back from MySQL server
while (myReader.Read())
{
string name = myReader.GetString(2);
string mobile = string.Empty;
if (!myReader.IsDBNull(3))
{
mobile = myReader.GetString(3);
}
int attachedIncident = -1;
if (!myReader.IsDBNull(4))
{
attachedIncident = myReader.GetInt32(4);
}
string oic = string.Empty;
if (!myReader.IsDBNull(7))
{
oic = myReader.GetString(7);
}
int crew = -1;
if (!myReader.IsDBNull(8))
{
crew = myReader.GetInt32(8);
}
int ba = -1;
if (!myReader.IsDBNull(9))
{
ba = myReader.GetInt32(9);
}
#region Address Data
int addressId = -1;
if (!myReader.IsDBNull(14))
{
addressId = myReader.GetInt32(14);
}
string building = string.Empty;
if (!myReader.IsDBNull(15))
{
building = myReader.GetString(15);
}
string number = string.Empty;
if (!myReader.IsDBNull(16))
{
number = myReader.GetString(16);
}
string street = string.Empty;
if (!myReader.IsDBNull(17))
{
street = myReader.GetString(17);
}
string town = string.Empty;
if (!myReader.IsDBNull(18))
{
town = myReader.GetString(18);
}
string postcode = string.Empty;
if (!myReader.IsDBNull(19))
{
postcode = myReader.GetString(19);
}
string county = string.Empty;
if (!myReader.IsDBNull(20))
{
county = myReader.GetString(20);
}
double latitude = myReader.GetDouble(21);
double longitude = myReader.GetDouble(22);
Address address = new Address(addressId, building, number, street, town, postcode, county, longitude, latitude);
#endregion
#region Status Data
int code = -1;
if (!myReader.IsDBNull(27))
{
code = myReader.GetInt32(27);
}
string codeDescription = string.Empty;
if (!myReader.IsDBNull(28))
{
codeDescription = myReader.GetString(28);
}
bool isAvail = false;
if (!myReader.IsDBNull(29))
{
isAvail = myReader.GetBoolean(29);
}
bool isMob = false;
if (!myReader.IsDBNull(30))
{
isMob = myReader.GetBoolean(30);
}
ResourceLogTime log = ResourceLogTime.Ignore;
if (!myReader.IsDBNull(31))
{
log = (ResourceLogTime)myReader.GetInt32(31);
}
ResourceStatus status = new ResourceStatus(code, codeDescription, isAvail, isMob, log);
#endregion
#region Appliance Type Data
string typeName = string.Empty;
if (!myReader.IsDBNull(32))
{
typeName = myReader.GetString(32);
}
string typeDescription = string.Empty;
if (!myReader.IsDBNull(33))
{
typeDescription = myReader.GetString(33);
}
ApplianceType type = new ApplianceType(typeName, typeDescription);
#endregion
#region Base Data
int baseId = -1;
if (!myReader.IsDBNull(34))
{
baseId = myReader.GetInt32(34);
}
int baseAddressId = -1;
if (!myReader.IsDBNull(35))
{
baseAddressId = myReader.GetInt32(35);
}
string office = string.Empty;
if (!myReader.IsDBNull(36))
{
office = myReader.GetString(36);
}
string baseName = string.Empty;
if (!myReader.IsDBNull(37))
{
name = myReader.GetString(37);
}
string baseBuilding = string.Empty;
if (!myReader.IsDBNull(39))
{
building = myReader.GetString(39);
}
string baseNumber = string.Empty;
if (!myReader.IsDBNull(40))
{
number = myReader.GetString(40);
}
string baseStreet = string.Empty;
if (!myReader.IsDBNull(41))
{
street = myReader.GetString(41);
}
string baseTown = string.Empty;
if (!myReader.IsDBNull(42))
{
town = myReader.GetString(42);
}
string basePostcode = string.Empty;
if (!myReader.IsDBNull(43))
{
postcode = myReader.GetString(43);
}
string baseCounty = string.Empty;
if (!myReader.IsDBNull(44))
{
county = myReader.GetString(44);
}
double baseLatitude = myReader.GetDouble(45);
double baseLongitude = myReader.GetDouble(46);
Address baseAddress = new Address(baseId, baseBuilding, baseNumber, baseStreet, baseTown, basePostcode, baseCounty, baseLongitude, baseLatitude);
Base baseLocation = new Base(baseId, office, baseAddress, baseName);
#endregion
ApplianceInfo info = new ApplianceInfo(name, mobile, baseLocation, address, status, oic, crew, ba, type, attachedIncident);
return(new Appliance(callSign, info));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (myReader != null)
{
myReader.Close();
}
command.Connection.Close();
}
return(null);
}
/// <summary>
/// Retrieves all appliance information from the database.
/// </summary>
/// <param name="callSign">The unique appliance call sign to retrieve the information for.</param>
/// <returns>An container class that stores all the information</returns>
public ApplianceInfo GetApplianceInfo(string callSign)
{
string statement = "SELECT resource.Name, resource.MobilePhoneNumber, " + Environment.NewLine +
"base.id, base.name, base.OfficePhoneNumber," + Environment.NewLine +
"base_address.id, base_address.building, base_address.number, base_address.street, base_address.town, base_address.postcode, base_address.county, base_address.longitude, base_address.latitude," + Environment.NewLine +
"current_address.id, current_address.building, current_address.number, current_address.street, current_address.town, current_address.postcode, current_address.county, current_address.longitude, current_address.latitude," + Environment.NewLine +
"status.code, status.Description, status.IsAvailable, status.IsMobile, status.ResourceLogEnumeration, resource_status.DateTime, resource_status.OperatorId," + Environment.NewLine +
"appliance.OfficerInCharge, appliance.CrewNumber, appliance.BaNumber," + Environment.NewLine +
"appliancetype.Name, appliancetype.Description, resource.AttachedIncidentNumber" + Environment.NewLine +
"FROM resource" + Environment.NewLine +
"INNER JOIN base ON resource.BaseLocationId = base.Id" + Environment.NewLine +
"INNER JOIN address AS base_address ON base.AddressId = base_address.Id" + Environment.NewLine +
"INNER JOIN Address_Resource ON Address_Resource.ResourceCallSign = resource.CallSign" + Environment.NewLine +
"INNER JOIN address AS current_address ON address_resource.AddressId = current_address.Id" + Environment.NewLine +
"INNER JOIN resource_status ON resource_status.ResourceCallSign = resource.CallSign" + Environment.NewLine +
"INNER JOIN status ON resource_status.StatusCode = status.Code" + Environment.NewLine +
"INNER JOIN appliance ON resource.CallSign = appliance.CallSign" + Environment.NewLine +
"INNER JOIN appliancetype ON appliance.ApplianceTypeName = appliancetype.Name" + Environment.NewLine +
"WHERE Address_Resource.DateTime = (SELECT MAX(DateTime) FROM Address_Resource WHERE ResourceCallSign = resource.CallSign)" + Environment.NewLine +
"AND resource_status.DateTime = (SELECT MAX(DateTime) FROM resource_status WHERE ResourceCallSign = resource.CallSign)" + Environment.NewLine +
"AND resource.CallSign = @callsign;";
//object that will execute the query
MySqlCommand command = new MySqlCommand(statement, connection);
command.Parameters.AddWithValue("@callsign", callSign);
//execute
MySqlDataReader myReader = null; //reads the database data
try
{
// open the connection only if it is currently closed
if (command.Connection.State == System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
//close the reader if it currently exists
if (myReader != null && !myReader.IsClosed)
{
myReader.Close();
}
myReader = command.ExecuteReader(); //execute the select statement
//read the data sent back from MySQL server and create the Appliance objects
while (myReader.Read())
{
//read all the data returned from the database - each information area is split into a different region below
string resourceName = myReader.GetString(0);
string mobileNo = myReader.GetString(1);
#region Base Address Data
int baseId = myReader.GetInt32(2);
string baseName = myReader.GetString(3);
string officeNo = myReader.GetString(4);
int baseAddressId = myReader.GetInt32(5);
string baseBuilding = string.Empty;
if (!myReader.IsDBNull(6))
{
baseBuilding = myReader.GetString(6);
}
string baseNumber = string.Empty;
if (!myReader.IsDBNull(7))
{
baseNumber = myReader.GetString(7);
}
string baseStreet = string.Empty;
if (!myReader.IsDBNull(8))
{
baseStreet = myReader.GetString(8);
}
string baseTown = string.Empty;
if (!myReader.IsDBNull(9))
{
baseTown = myReader.GetString(9);
}
string basePostcode = string.Empty;
if (!myReader.IsDBNull(10))
{
basePostcode = myReader.GetString(10);
}
string baseCounty = string.Empty;
if (!myReader.IsDBNull(11))
{
baseCounty = myReader.GetString(11);
}
double baseLong = myReader.GetDouble(12);
double baseLat = myReader.GetDouble(13);
Address baseAddress = new Address(baseAddressId, baseBuilding, baseNumber, baseStreet, baseTown, basePostcode, baseCounty, baseLong, baseLat);
Base baseLocation = new Base(baseId, officeNo, baseAddress, baseName);
#endregion
#region Current Address Data
int currentId = myReader.GetInt32(14);
string currentBuilding = string.Empty;
if (!myReader.IsDBNull(15))
{
currentBuilding = myReader.GetString(15);
}
string currentNumber = string.Empty;
if (!myReader.IsDBNull(16))
{
currentNumber = myReader.GetString(16);
}
string currentStreet = string.Empty;
if (!myReader.IsDBNull(17))
{
currentStreet = myReader.GetString(17);
}
string currentTown = string.Empty;
if (!myReader.IsDBNull(18))
{
currentTown = myReader.GetString(18);
}
string currentPostcode = string.Empty;
if (!myReader.IsDBNull(19))
{
currentPostcode = myReader.GetString(19);
}
string currentCounty = string.Empty;
if (!myReader.IsDBNull(20))
{
currentCounty = myReader.GetString(20);
}
double currentLong = myReader.GetDouble(21);
double currentLat = myReader.GetDouble(22);
Address currentAddress = new Address(currentId, currentBuilding, currentNumber, currentStreet, currentTown, currentPostcode, currentCounty, currentLong, currentLat);
#endregion
#region Status Code Data
int statusCode = -1;
if (!myReader.IsDBNull(23))
{
statusCode = myReader.GetInt32(23);
}
string statusDescription = string.Empty;
if (!myReader.IsDBNull(24))
{
statusDescription = myReader.GetString(24);
}
bool isAvail = false;
if (!myReader.IsDBNull(25))
{
isAvail = myReader.GetBoolean(25);
}
bool isMobile = false;
if (!myReader.IsDBNull(26))
{
isMobile = myReader.GetBoolean(26);
}
int resourceLogEnumeration = -1;
if (!myReader.IsDBNull(27))
{
resourceLogEnumeration = myReader.GetInt32(27);
}
ResourceLogTime incidentLogAction = ResourceLogTime.Ignore;
if (Enum.IsDefined(typeof(ResourceLogTime), resourceLogEnumeration))
{
incidentLogAction = (ResourceLogTime)resourceLogEnumeration;
}
ResourceStatus status = new ResourceStatus(statusCode, statusDescription, isAvail, isMobile, incidentLogAction);
#endregion
#region Appliance Data
string oic = string.Empty;
if (!myReader.IsDBNull(30))
{
oic = myReader.GetString(30);
}
int crew = -1;
if (!myReader.IsDBNull(31))
{
crew = myReader.GetInt32(31);
}
int ba = -1;
if (!myReader.IsDBNull(32))
{
ba = myReader.GetInt32(32);
}
string applianceTypeName = string.Empty;
if (!myReader.IsDBNull(33))
{
applianceTypeName = myReader.GetString(33);
}
string applianceTypeDescription = string.Empty;
if (!myReader.IsDBNull(34))
{
applianceTypeDescription = myReader.GetString(34);
}
int assignedIncident = -1;
if (!myReader.IsDBNull(35))
{
assignedIncident = myReader.GetInt32(35);
}
ApplianceType type = new ApplianceType(applianceTypeName, applianceTypeDescription);
#endregion
return(new ApplianceInfo(resourceName, mobileNo, baseLocation, currentAddress, status, oic, crew, ba, type, assignedIncident));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show(statement);
}
finally
{
//close the connections
if (myReader != null)
{
myReader.Close();
}
command.Connection.Close();
}
return(null);
}
