/// <summary>
/// Updates an Offence record inside the Event-Guardian database, using the ID and values provided.
/// </summary>
/// <param name="id">ID indicating the Offence record to be updated</param>
/// <param name="isDeleted">New value for the IsDeleted column</param>
/// <param name="isVisible">New value for the IsVisible column</param>
/// <param name="name">New value for the Name column</param>
/// <param name="offenceCategoryID">The ID of the corresponding offence category.</param>
public void UpdateOffence(int id, bool isDeleted, bool isVisible, string name, int?offenceCategoryID)
{
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
SqlMapper.Execute(sqlConnection, CrimeSpUpdateOffence, new { ID = id, IsDeleted = isDeleted, IsVisible = isVisible, Name = name, OffenceCategoryID = offenceCategoryID }, commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Gets all crime information from the database.
/// </summary>
/// <returns>Returns a list of Crime objects representing the result of the operation.</returns>
public List <CrimeOffenceLocalGovernmentAreaState> GetCrimeOffenceLocalGovernmentAreaStates(int skip, int take, string orderBy, string sortDirection)
{
List <CrimeOffenceLocalGovernmentAreaState> crimeOffenceLocalGovernmentAreaStates = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
crimeOffenceLocalGovernmentAreaStates = SqlMapper
.Query(sqlConnection, CrimeSpGetCrimeOffenceLocalGovernmentAreaState, new { Skip = skip, Take = take, OrderBy = orderBy, SortDirection = sortDirection }, commandType: CommandType.StoredProcedure)
.Select(m => new CrimeOffenceLocalGovernmentAreaState
(
m.Count,
m.DateCreatedUtc,
m.DateUpdatedUtc,
m.ID,
m.IsDeleted,
m.IsVisible,
m.LocalGovernmentAreaID,
m.LocalGovernmentAreaName,
m.Month,
m.OffenceID,
m.OffenceName,
m.StateID,
m.StateName,
m.Year
))
.ToList();
}
return(crimeOffenceLocalGovernmentAreaStates);
}
/// <summary>
/// Selects crime information from the underlying data source.
/// </summary>
/// <param name="id">The ID of the associated crime information.</param>
/// <returns>Returns a Crime object representing the result of the operation.</returns>
public CrimeOffenceLocalGovernmentAreaState GetCrimeOffenceLocalGovernmentAreaStateByID(int id)
{
CrimeOffenceLocalGovernmentAreaState crimeOffenceLocalGovernmentAreaState = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
crimeOffenceLocalGovernmentAreaState = SqlMapper
.Query(sqlConnection, CrimeSpGetCrimeOffenceLocalGovernmentAreaStateByID, new { ID = id }, commandType: CommandType.StoredProcedure)
.Select(m => new CrimeOffenceLocalGovernmentAreaState
(
m.Count,
m.DateCreatedUtc,
m.DateUpdatedUtc,
m.ID,
m.IsDeleted,
m.IsVisible,
m.LocalGovernmentAreaID,
m.LocalGovernmentAreaName,
m.Month,
m.OffenceID,
m.OffenceName,
m.StateID,
m.StateName,
m.Year
))
.FirstOrDefault();
}
return(crimeOffenceLocalGovernmentAreaState);
}
/// <summary>
/// Updates local government area information in the underlying data source.
/// </summary>
/// <param name="id">The ID of the associated local government area.</param>
/// <param name="isDeleted">Specifies whether the local government area is flagged as deleted.</param>
/// <param name="isVisible">Specifies whether the local government area is flagged as visible.</param>
/// <param name="name">Specifies the name of the local government area.</param>
/// <param name="stateID">Specifies the ID of the associated state.</param>
public void UpdateLocalGovernmentArea(int id, bool isDeleted, bool isVisible, string name, int stateID)
{
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
SqlMapper.Execute(sqlConnection, LocationSpUpdateLocalGovernmentArea, new { ID = id, IsDeleted = isDeleted, IsVisible = isVisible, Name = name, StateID = stateID }, commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Updates state or territory information in the underlying data source.
/// </summary>
/// <param name="id">The ID of the associated state or territory.</param>
/// <param name="abbreviatedName">The state or territory name in abbreviated form.</param>
/// <param name="isDeleted">Specifies whether the offence category is flagged as deleted.</param>
/// <param name="isVisible">Specifies whether the offence category is flagged as visible.</param>
/// <param name="name">The name of the state or territory.</param>
public void UpdateState(int id, string abbreviatedName, bool isDeleted, bool isVisible, string name)
{
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
SqlMapper.Execute(sqlConnection, "Location.spUpdateState", new { ID = id, AbbreviatedName = abbreviatedName, IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// Executes SQL against the underlying data source.
/// </summary>
/// <param name="sql">The SQL to execute.</param>
/// <param name="commandTimeout">Specifies the amount of time in seconds the command is permitted to wait before throwing an exception.</param>
/// <returns>Returns the number of rows affected</returns>
public int ExecuteScript(string sql, int commandTimeout)
{
int executeScript = 0;
if (String.IsNullOrEmpty(sql) == false)
{
try
{
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
SqlMapper.Execute(sqlConnection, sql, commandType: CommandType.Text, commandTimeout: commandTimeout);
}
}
catch (Exception exception)
{
OutputStreams.WriteLine();
OutputStreams.WriteLine(exception.Message);
OutputStreams.WriteLine();
OutputStreams.WriteLine(sql);
OutputStreams.WriteLine();
}
}
return(executeScript);
}
/// <summary>
/// Selects crime information from the underlying data source.
/// </summary>
/// <param name="latitude">Specifies the latitude used to look up the corresponding local government area.</param>
/// <param name="longitude">Specifies the longitude used to look up the corresponding local government area.</param>
/// <returns>Returns a list of Crime objects representing the result of the operation.</returns>
public List <CrimeByCoordinate> GetCrimesByCoordinate(double latitude, double longitude)
{
List <CrimeByCoordinate> crimeByCoordinates = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
crimeByCoordinates = SqlMapper
.Query(sqlConnection, CrimeSpGetCrimesByCoordinate, commandType: CommandType.StoredProcedure, param: new { Latitude = latitude, Longitude = longitude })
.Select(m => new CrimeByCoordinate(m.BeginYear, m.EndYear, m.Name, m.OffenceCount, m.OffenceID, m.Offence))
.ToList();
}
return(crimeByCoordinates);
}
/// <summary>
/// Selects all users from the underlying data source.
/// </summary>
/// <returns>Returns a list of User objects representing the result of the operation.</returns>
public List <User> GetUsers()
{
List <User> users = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
users = SqlMapper
.Query(sqlConnection, RegistrationSpGetUser, commandType: CommandType.StoredProcedure)
.Select(m => new User(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsActive, m.IsDeleted, m.Password, m.Username))
.ToList();
}
return(users);
}
/// <summary>
/// Selects a user from the underlying data souce.
/// </summary>
/// <param name="username">The username of theassociated user.</param>
/// <returns>Returns a User object representing the result of the operation.</returns>
public User GetUserByUsername(string username)
{
User user = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
user = SqlMapper
.Query(sqlConnection, RegistrationSpGetUserByUsername, new { Username = username }, commandType: CommandType.StoredProcedure)
.Select(m => new User(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsActive, m.IsDeleted, m.Password, m.Username))
.FirstOrDefault();
}
return(user);
}
/// <summary>
/// Retreives a full list of Offenses inside the Event-Guardian database.
/// </summary>
/// <returns>A List of Offence records</returns>
public List <Offence> GetOffences()
{
List <Offence> offences = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
offences = SqlMapper
.Query(sqlConnection, CrimeSpGetOffence, commandType: CommandType.StoredProcedure)
.Select(m => new Offence(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.OffenceCategoryID))
.ToList();
}
return(offences);
}
/// <summary>
/// Searches the Event-Guardian database for an Offence record with a specified Offence ID.
/// </summary>
/// <param name="id">Identification number for the Offence record</param>
/// <returns>An Offence record with the specified ID, or null if no matches were found</returns>
public Offence GetOffenceByID(int id)
{
Offence offence = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
offence = SqlMapper
.Query(sqlConnection, "Crime.spGetOffenceByID", new { ID = id }, commandType: CommandType.StoredProcedure)
.Select(m => new Offence(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.OffenceCategoryID))
.FirstOrDefault();
}
return(offence);
}
/// <summary>
/// Selects state or territory information from the underlying data source.
/// </summary>
/// <param name="id">The ID of the corresponding state or territory information.</param>
/// <returns>Returns a State object representing the result of the operation.</returns>
public State GetStateByID(int id)
{
State state = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
state = SqlMapper
.Query(sqlConnection, "Location.spGetStateByID", new { ID = id }, commandType: CommandType.StoredProcedure)
.Select(m => new State(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name))
.FirstOrDefault();
}
return(state);
}
/// <summary>
/// Selects all local government area and state or territory information from the underlying data source.
/// </summary>
/// <returns>Returns a list of LocalGovernmentAreaState objects representing the result of the operation.</returns>
public List <LocalGovernmentAreaState> GetLocalGovernmentAreaStates()
{
List <LocalGovernmentAreaState> localGovernmentAreaStates = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
localGovernmentAreaStates = SqlMapper
.Query(sqlConnection, LocationSpGetLocalGovernmentAreaState, commandType: CommandType.StoredProcedure)
.Select(m => new LocalGovernmentAreaState(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID, m.StateName))
.ToList();
}
return(localGovernmentAreaStates);
}
/// <summary>
/// Selects local government area information from the underlying data source.
/// </summary>
/// <param name="stateID">The ID of the associated state that the local government area resides in.</param>
/// <returns>Returns a list of LocalGovernmentArea objects representing the result of the operation.</returns>
public List <LocalGovernmentArea> GetLocalGovernmentAreasByStateID(int stateID)
{
List <LocalGovernmentArea> localGovernmentAreas = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
localGovernmentAreas = SqlMapper
.Query(sqlConnection, LocationSpGetLocalGovernmentAreasByStateID, new { StateID = stateID }, commandType: CommandType.StoredProcedure)
.Select(m => new LocalGovernmentArea(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID))
.ToList();
}
return(localGovernmentAreas);
}
/// <summary>
/// Selects local government area information from the underlying data source.
/// </summary>
/// <param name="id">The ID of the associated local government area.</param>
/// <returns>Returns a LocalGovernmentArea object representing the result of the operation.</returns>
public LocalGovernmentArea GetLocalGovernmentAreaByID(int id)
{
LocalGovernmentArea localGovernmentArea = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
localGovernmentArea = SqlMapper
.Query(sqlConnection, LocationSpGetLocalGovernmentAreaByID, new { ID = id }, commandType: CommandType.StoredProcedure)
.Select(m => new LocalGovernmentArea(m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name, m.StateID))
.FirstOrDefault();
}
return(localGovernmentArea);
}
/// <summary>
/// Inserts local government area information into the underlying data source.
/// </summary>
/// <param name="isDeleted">Specifies whether the local government area is flagged as deleted.</param>
/// <param name="isVisible">Specifies whether the local government area is flagged as visible.</param>
/// <param name="name">Specifies the name of the local government area.</param>
/// <param name="stateID">Specifies the ID of the associated state.</param>
/// <returns>Returns the newly created ID for a successful operation, otherwise returns 0.</returns>
public int AddLocalGovernmentArea(bool isDeleted, bool isVisible, string name, int stateID)
{
int id = 0;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
id = (int)SqlMapper
.Query(sqlConnection, LocationSpAddLocalGovernmentArea, new { IsDeleted = isDeleted, IsVisible = isVisible, Name = name, StateID = stateID }, commandType: CommandType.StoredProcedure)
.Select(m => m.NewID)
.Single();
}
return(id);
}
/// <summary>
/// Inserts crime information into the underlying data source.
/// </summary>
/// <param name="count">Specifies the count of the offences.</param>
/// <param name="localGovernmentAreaID">Specified the ID of the associated local government area.</param>
/// <param name="month">Specifies the month of the crime.</param>
/// <param name="offenceID">Specifies the ID of the associated offence.</param>
/// <param name="year">Specifies the year of the crime.</param>
/// <returns></returns>
public int AddCrime(int count, int localGovernmentAreaID, int month, int offenceID, int year)
{
int id = 0;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
id = (int)SqlMapper
.Query(sqlConnection, CrimeSpAddCrime, new { Count = count, LocalGovernmentAreaID = localGovernmentAreaID, Month = month, OffenceID = offenceID, Year = year }, commandType: CommandType.StoredProcedure)
.Select(m => m.NewID)
.Single();
}
return(id);
}
/// <summary>
/// Inserts a new record inside the Offence table of the Event-Guardian database.
/// </summary>
/// <param name="isDeleted">Indicates whether or not the record is deleted</param>
/// <param name="isVisible">Indicates whether or not the record should be shown in the user interfaces</param>
/// <param name="name">The name of the offense</param>
/// <returns>Offence ID number</returns>
public int AddOffence(bool isDeleted, bool isVisible, string name)
{
int id = 0;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
id = (int)SqlMapper
.Query(sqlConnection, CrimeSpAddOffence, new { IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure)
.Select(m => m.NewID)
.Single();
}
return(id);
}
/// <summary>
/// Selects all state or territory information from the underlying data source.
/// </summary>
/// <returns>Returns a list of State objects representing the result of the operation.</returns>
public List <State> GetStates()
{
List <State> states = null;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
states = SqlMapper
.Query(sqlConnection, "Location.spGetState", commandType: CommandType.StoredProcedure)
.Select(m => new State(m.AbbreviatedName, m.DateCreatedUtc, m.DateUpdatedUtc, m.ID, m.IsDeleted, m.IsVisible, m.Name))
.ToList();
}
return(states);
}
/// <summary>
/// Inserts state or territory information into the underlying data source.
/// </summary>
/// <param name="abbreviatedName">The state or territory name in abbreviated form.</param>
/// <param name="isDeleted">Specifies whether the offence category is flagged as deleted.</param>
/// <param name="isVisible">Specifies whether the offence category is flagged as visible.</param>
/// <param name="name">The name of the state or territory.</param>
/// <returns></returns>
public int AddState(string abbreviatedName, bool isDeleted, bool isVisible, string name)
{
int id = 0;
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
id = (int)SqlMapper
.Query(sqlConnection, LocationSpAddState, new { AbbreviatedName = abbreviatedName, IsDeleted = isDeleted, IsVisible = isVisible, Name = name }, commandType: CommandType.StoredProcedure)
.Select(m => m.NewID)
.Single();
}
return(id);
}
/// <summary>
/// Updates crime information in the underlying data source.
/// </summary>
/// <param name="id">The ID of the associated crime.</param>
/// <param name="localGovernmentAreaID">Specified the ID of the associated local government area.</param>
/// <param name="offenceID">Specifies the ID of the associated offence.</param>
/// <param name="count">Specifies the count of the offences.</param>
/// <param name="month">Specifies the month of the crime.</param>
/// <param name="year">Specifies the year of the crime.</param>
/// <param name="isDeleted">Specifies whether the crime information is flagged as deleted.</param>
/// <param name="isVisible">Specifies whether the crime information is flagged as visible.</param>
public void UpdateCrime(int id, int localGovernmentAreaID, int offenceID, int count, int month, int year, bool isDeleted, bool isVisible)
{
using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion())
{
SqlMapper.Execute(sqlConnection, CrimeSpUpdateCrime, new
{
ID = id,
LocalGovernmentAreaID = localGovernmentAreaID,
OffenceID = offenceID,
Count = count,
Month = month,
Year = year,
IsDeleted = isDeleted,
IsVisible = isVisible,
},
commandType: CommandType.StoredProcedure);
}
}
