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