/**
* @brief Class constructor
* @param User user The user that is identified by the Session
**/
public GreyhoundSession(String session_id, User user)
{
this.session_id = session_id;
this.user_id = user.User_Id;
this.role_id = (int)user.Role_Id;
this.validity = DateTime.Now.AddMinutes(30);
}
/**
* @brief Returns all race details from a race identified by an ID
* @param string races_race_id The race id index to return the details from the database
* @param string sortBy The sort field to apply to the query
* @param string sortType The sort type to apply to the query
* @return RaceDetail[] An array with all races.
**/
public static RaceDetail[] GetRaceDetailsByRaceID(User user, string races_race_id, string sortBy, string sortType)
{
//Setup the command to run
string strCommand = GET_RACES_DETAILS_BY_RACE_ID_SQL;
strCommand = strCommand.Replace("@races_race_id", races_race_id);
strCommand = strCommand.Replace("@sortBy", sortBy);
strCommand = strCommand.Replace("@sortType", sortType);
Tools.printDebug(null, strCommand, null);
List<RaceDetail> resultList = executeQuery(user, strCommand);
return resultList.ToArray<RaceDetail>();
}
/**
* @brief Returns the details of all races that occur(ed) in a particular date
* @param string date_time The date string that identifies the date to retrieve from the database
* @param string sortBy The sort field to apply to the query
* @param string sortType The sort type to apply to the query
* @return RaceDetail[] An array with all races.
**/
public static RaceDetail[] GetRaceDetailsByDate(User user, string date_time, string sortBy, string sortType)
{
//Setup the command to run
string strCommand = GET_RACES_DETAILS_BY_DATE_SQL;
strCommand = strCommand.Replace("@date_time", date_time);
strCommand = strCommand.Replace("@sortBy", sortBy);
strCommand = strCommand.Replace("@sortType", sortType);
Tools.printDebug(null, strCommand, null);
List<RaceDetail> resultList = executeQuery(user, strCommand);
return resultList.ToArray<RaceDetail>();
}
/**
* @brief Returns an array of races_details by races_id query from Greyhound database system
* @return An array of races_details
*/
public static RaceDetail[] GetRaceDetailsByRaceID(User user, string races_race_id, string sortBy, string sortType)
{
return RaceDetailDAL.GetRaceDetailsByRaceID(user, races_race_id, sortBy, sortType);
}
/**
* @brief Returns an array of races_details by date query from Greyhound database system
* @return An array of races_details
*/
public static RaceDetail[] GetRaceDetailsByDate(User user, string date, string sortBy, string sortType)
{
return RaceDetailDAL.GetRaceDetailsByDate(user, date, sortBy, sortType);
}
/**
* @brief Adds a user to the database
* @param User user The user to add
**/
public static int UpdateUserSettings(User user)
{
int result = -1;
bool error = false;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleTransaction transaction = null;
try
{
//Open the connection
connection.Open();
//Start a local transaction
transaction = connection.BeginTransaction();
//First we add the user to the database
//Setup the command to run
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.Transaction = transaction;
if (user.User_Id > 0)
{
command.Parameters.Clear();
command.BindByName = true;
if (user.Password != null && user.Password.Length > 0)
{
command.CommandText = UPDATE_USER_PASSWORD;
command.Parameters.Add(":password", user.Password);
}
else
{
command.CommandText = UPDATE_USER_NO_PASSWORD;
}
//First we set the parameters
command.Parameters.Add(":user_id", user.User_Id);
command.Parameters.Add(":name", user.Name);
command.Parameters.Add(":mobile", user.Mobile);
command.Parameters.Add(":address", user.Address);
command.Parameters.Add(":paypal_id", user.Paypal_Id);
command.Parameters.Add(":betfair_id", user.Betfair_Id);
Tools.printDebug(command.CommandText.ToString());
Tools.printDebug(command.Parameters.ToString());
//Run the command
result = command.ExecuteNonQuery();
Tools.printDebug("UPDATE USER RESULT: " + result);
}
}
catch (Exception exception)
{
error = true;
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
if (transaction != null)
{
if (error)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
//Closes the datareader and connection to the database
if (connection != null)
{
connection.Close();
}
}
return result;
}
private static List<Greyhound> executeQuery(User user, String strCommand)
{
List<Greyhound> resultList = new List<Greyhound>();
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
//Fetches all rows
while (sqlDataReader.Read())
{
int grey_id = Convert.ToInt32(sqlDataReader[GREYHOUNDS_GREYHOUND_ID_FIELD].ToString());
string name = sqlDataReader[GREYHOUNDS_NAME_FIELD].ToString();
string trainer = sqlDataReader[GREYHOUNDS_TRAINER_FIELD].ToString();
string birth_date = sqlDataReader[GREYHOUNDS_BIRTH_DATE_FIELD].ToString();
string score = sqlDataReader[GREYHOUNDS_SCORE_FIELD].ToString();
Greyhound greyhound = new Greyhound(
grey_id,
name,
trainer,
birth_date,
score
);
resultList.Add(greyhound);
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
return resultList;
}
/**
* @brief Registers a new user in Greyhound database system
* @return The user_id of the registered user
*/
public static int RegisterNewUser(User user)
{
return UserDAL.RegisterNewUser(user);
}
/**
* @brief Returns all race events at a given date from Greyhound database system
* @return An array with all race events at the given date.
*/
public static RaceEvent[] GetRaceEventsAtDate(User user, string date_time)
{
return RaceEventDAL.GetRaceEventsAtDate(user, date_time);
}
/**
* @brief Returns all races from DB
* @param string date_time The date_time to apply to the query
* @return RaceEvent[] An array with all stadiums, races and races details (RaceEvent array) at the date provided
**/
public static RaceEvent[] GetRaceEventsAtDate(User user, string m_date_time)
{
List<RaceEvent> resultList = new List<RaceEvent>();
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
//Setup the command to run
string strCommand = GET_ALL_RACE_EVENTS_AT_DATE_SQL;
strCommand = strCommand.Replace("@date_time", m_date_time.ToString());
Tools.printDebug(null, strCommand, null);
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
//Fetches all rows
int previous_std_id = -1;
int previous_race_id = -1;
Stadium stadium = null;
Race race = null;
while (sqlDataReader.Read())
{
if (stadium != null && race != null)
{
RaceEvent raceEvent = new RaceEvent(stadium, race);
resultList.Add(raceEvent);
previous_std_id = -1;
previous_race_id = -1;
stadium = null;
race = null;
}
int std_id = Convert.ToInt32(sqlDataReader[STADIUMS_STADIUM_ID_FIELD].ToString());
if( previous_std_id != std_id ) {
previous_std_id = std_id;
string stadium_name = sqlDataReader[STADIUMS_NAME_FIELD].ToString();
string num_tracks = sqlDataReader[STADIUMS_NUM_TRACKS_FIELD].ToString();
string city = sqlDataReader[STADIUMS_CITY_FIELD].ToString();
string country = sqlDataReader[STADIUMS_COUNTRY_FIELD].ToString();
stadium = new Stadium(
std_id,
stadium_name,
num_tracks,
city,
country
);
}
int race_id = Convert.ToInt32(sqlDataReader[RACES_ID_FIELD].ToString());
if( previous_race_id != race_id ) {
previous_race_id = race_id;
int stadium_std_id = Convert.ToInt32(sqlDataReader[RACES_STADIUM_ID_FIELD].ToString());
string date_time = sqlDataReader[RACES_DATE_TIME_FIELD].ToString();
int race_number = Convert.ToInt32(sqlDataReader[RACES_RACE_NUMBER_FIELD].ToString());
Decimal track_length = Tools.StringToDecimal(sqlDataReader[RACES_TRACK_LENGTH_FIELD].ToString());
string grade = sqlDataReader[RACES_GRADE_FIELD].ToString();
string race_type = sqlDataReader[RACES_RACE_TYPE_FIELD].ToString();
race = new Race(
race_id,
stadium_std_id,
date_time,
race_number,
track_length,
grade,
race_type);
}
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
//Returns the list of fetched users in the form of an array
return resultList.ToArray();
}
/**
* @brief Returns all users from Greyhound database system
* @return An array with all users.
*/
public static Greyhound[] GetGreyhoundsRunningAtDate(User user, string date_time)
{
return GreyhoundDAL.GetGreyhoundsRunningAtDate(user, date_time);
}
/**
* @brief Try to insert a bet into the database
* @return the bet_id if sucessful, -1 otherwise
*/
public static long InsertBet(User user, Bet bet)
{
return BetsDAL.InsertBet(user, bet);
}
/**
* @brief Returns all user's bets from Greyhound database system
* @return An array with all user's bets
*/
public static Bet[] GetBetsByUserId(User user)
{
return BetsDAL.GetBetsByUserId(user);
}
public void RegisterNewUser(string user, string token,
string name, string email, string password, string mobile,
string address, string paypal_id, string betfair_id )
{
if (!AuthenticateUser(user, token))
{
String result = "{error:\"not authenticated\", items:[]}";
SendResponse(result);
return;
}
User newuser = new User(-1, Role.ROLES.REGISTERED_USER_ROLE, password, name, email, mobile, address, paypal_id, betfair_id);
int res = UserBLL.RegisterNewUser(newuser);
bool email_sent = Tools.sendMail(email, "" + res);
if (res > 0 && email_sent)
{
String result = "{result: \"success\"}";
SendResponse(result);
}
else
{
String errormsg = "'Unknown error!'";
if( res > 0 )
errormsg = "'Failed to add the user account to the system'";
else if( !email_sent )
errormsg = "'Failed to send the email address'";
String result = "{result: 'error', errormsg: " + errormsg + "}";
SendResponse(result);
}
}
private static List<RaceDetail> executeQuery(User user, String strCommand)
{
List<RaceDetail> resultList = new List<RaceDetail>();
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
//Fetches all rows
while (sqlDataReader.Read())
{
int races_race_id = Convert.ToInt32(sqlDataReader[V_RACES_DETAILS_RACES_ID_FIELD].ToString());
int greyhounds_grey_id = Convert.ToInt32(sqlDataReader[GREYHOUNDS_GREYHOUND_ID_FIELD].ToString());
string greyhound_name = sqlDataReader[GREYHOUNDS_NAME_FIELD].ToString();
string trainer = sqlDataReader[GREYHOUNDS_TRAINER_FIELD].ToString();
string birth_date = sqlDataReader[GREYHOUNDS_BIRTH_DATE_FIELD].ToString();
string score = sqlDataReader[GREYHOUNDS_SCORE_FIELD].ToString();
score = score.Replace(",", ".");
int track_number = Convert.ToInt32(sqlDataReader[RACES_DETAILS_TRACK_NUMBER_FIELD].ToString());
bool race_completed = Tools.StringToBool(sqlDataReader[RACES_DETAILS_RACE_COMPLETED_FIELD].ToString());
string position = sqlDataReader[RACES_DETAILS_POSITION_FIELD].ToString();
string odd = sqlDataReader[ODDS_VALUE_FIELD].ToString();
string prediction = "";
if( user != null && user.Role_Id > Role.ROLES.ANONYMOUS_USER_ROLE ) {
prediction = sqlDataReader[PREDICTIONS_ADVANCED_VALUE_FIELD].ToString();
}
else {
prediction = sqlDataReader[PREDICTIONS_SIMPLE_VALUE_FIELD].ToString();
}
prediction = prediction.Replace(",", ".");
Decimal time = Tools.StringToDecimal(sqlDataReader[RACES_DETAILS_TIME_FIELD].ToString());
RaceDetail race_detail = new RaceDetail(
races_race_id,
greyhounds_grey_id,
greyhound_name,
trainer,
birth_date,
score,
track_number,
race_completed,
position,
odd,
prediction,
time);
resultList.Add(race_detail);
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
return resultList;
}
/**
* @brief Try to insert a bet into the database
* @return the bet_id if sucessful, -1 otherwise
*/
public static long InsertBet(User user, Bet bet)
{
long result = -1;
try
{
using (var connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id)))
{
using (var Command = new OracleCommand())
{
Command.Connection = connection;
Command.CommandText = "INSERT_BET";
Command.CommandType = CommandType.StoredProcedure;
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Long;
param.Direction = ParameterDirection.Input;
param.ParameterName = "userId";
param.Value = bet.User_Id;
Command.Parameters.Add(param);
}
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Long;
param.Direction = ParameterDirection.Input;
param.ParameterName = "betTypeId";
param.Value = bet.Bet_Type_Id;
Command.Parameters.Add(param);
}
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Long;
param.Direction = ParameterDirection.Input;
param.ParameterName = "raceDetailId";
param.Value = bet.Race_Id;
Command.Parameters.Add(param);
}
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Long;
param.Direction = ParameterDirection.Input;
param.ParameterName = "raceGreyId";
param.Value = bet.Grey_Id;
Command.Parameters.Add(param);
}
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Decimal;
param.Direction = ParameterDirection.Input;
param.ParameterName = "betValue";
param.Value = bet.Bet_Value;
Command.Parameters.Add(param);
}
using (var param = new OracleParameter())
{
param.OracleDbType = OracleDbType.Int32;
param.Direction = ParameterDirection.Output;
param.ParameterName = "out_betId";
Command.Parameters.Add(param);
}
connection.Open();
int res = Command.ExecuteNonQuery();
if (Command.Parameters["out_betId"].DbType == System.Data.DbType.Int32)
{
result = Int32.Parse((Command.Parameters["out_betId"].Value).ToString());
Tools.printDebug("Insert Bet Result: " + result);
}
else
{
Tools.printDebug("Failed to Insert the Bet");
}
}
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (Exception exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
}
return result;
}
/**
* @brief Returns all races from DB
* @param string date_time The date_time to apply to the query
* @return RaceEvent[] An array with all stadiums, races and races details (RaceEvent array) at the date provided
**/
public static Bet[] GetBetsByUserId(User user)
{
List<Bet> betsList = new List<Bet>();
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
//Setup the command to run
string strCommand = GET_BETS_BY_USER_ID_SQL;
strCommand = strCommand.Replace("@user_id", user.User_Id.ToString());
Tools.printDebug(null, strCommand, null);
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
while (sqlDataReader.Read())
{
long bet_id = Convert.ToInt64(sqlDataReader[BETS_BET_ID].ToString());
long user_id = Convert.ToInt64(sqlDataReader[BETS_USER_ID].ToString());
long bet_type_id = Convert.ToInt64(sqlDataReader[BETS_BET_TYPE_ID].ToString());
long race_id = Convert.ToInt64(sqlDataReader[BETS_RACE_ID].ToString());
long grey_id = Convert.ToInt64(sqlDataReader[BETS_GREY_ID].ToString());
string date_time = sqlDataReader[BETS_DATE_TIME].ToString();
Decimal bet_value = Tools.StringToDecimal(sqlDataReader[BETS_BET_VALUE].ToString());
int bet_result = (!sqlDataReader[BETS_BET_RESULT].ToString().Equals("")?
Convert.ToInt32(sqlDataReader[BETS_BET_RESULT].ToString()) : -1);
Decimal return_value = Tools.StringToDecimal(sqlDataReader[BETS_RETURN_VALUE].ToString());
Bet bet = new Bet(
bet_id,
user_id,
bet_type_id,
race_id,
grey_id,
date_time,
bet_value,
bet_result,
return_value
);
betsList.Add(bet);
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
//Returns the list of fetched bets in the form of an array
return betsList.ToArray();
}
/**
* @brief Returns the user with the identified id from DB
* @param string user_id The user_id filter to apply to the query
* @return User A User object array with the id defined in the query
**/
public static User GetUserById(string user_id)
{
User user = null;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
//Setup the command to run
string strCommand = GET_USER_BY_USER_ID_SQL;
strCommand = strCommand.Replace("@user_id", user_id);
Tools.printDebug(null, strCommand, null);
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
//Fetches all rows
while (sqlDataReader.Read())
{
int userid = Convert.ToInt32(sqlDataReader[USERS_USER_ID_FIELD].ToString());
Role.ROLES role_id = (Role.ROLES)Convert.ToInt32(sqlDataReader[USERS_ROLE_ID_FIELD].ToString());
string name = sqlDataReader[USERS_NAME_FIELD].ToString();
string email = sqlDataReader[USERS_EMAIL_FIELD].ToString();
string mobile = sqlDataReader[USERS_MOBILE_FIELD].ToString();
string address = sqlDataReader[USERS_ADDRESS_FIELD].ToString();
string paypal_id = sqlDataReader[USERS_PAYPAL_ID_FIELD].ToString();
string betfair_id = sqlDataReader[USERS_BETFAIR_ID_FIELD].ToString();
user = new User(
userid,
role_id,
name,
email,
mobile,
address,
paypal_id,
betfair_id
);
break;
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
return user;
}
/**
* @brief Updates an existent user account in Greyhound database system
* @return The user_id of the registered user
*/
public static int UpdateUserSettings(User user)
{
return UserDAL.UpdateUserSettings(user);
}
/**
* @brief Returns all users from the DB
* @param string filter The filter to apply to the query
* @param int startRec The start index to return from the database
* @param int endRec The end index to return from the database
* @param string sortBy The sort field to apply to the query
* @param string sortType The sort type to apply to the query
* @return User[] An array with all users.
**/
public static User[] GetUsersPaged(string filter, int startRec, int endRec, string sortBy, string sortType)
{
List<User> resultList = new List<User>();
OracleConnection connection = new OracleConnection(ConfigurationBLL.DBConnectionString);
OracleDataReader sqlDataReader = null;
try
{
//Open the connection
connection.Open();
//Setup the command to run
//Setup the command to run
string strCommand = GET_ALL_USERS_PAGED_SQL;
strCommand = strCommand.Replace("@start_rec", startRec.ToString());
strCommand = strCommand.Replace("@end_rec", endRec.ToString());
strCommand = strCommand.Replace("@filter", filter);
strCommand = strCommand.Replace("@sortBy", sortBy);
strCommand = strCommand.Replace("@sortType", sortType);
Tools.printDebug(null, strCommand, null);
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
//Runs the command
sqlDataReader = command.ExecuteReader();
//Fetches all rows
while (sqlDataReader.Read())
{
int user_id = Convert.ToInt32(sqlDataReader[USERS_USER_ID_FIELD].ToString());
Role.ROLES role_id = (Role.ROLES) Convert.ToInt32(sqlDataReader[USERS_ROLE_ID_FIELD].ToString());
string name = sqlDataReader[USERS_NAME_FIELD].ToString();
string email = sqlDataReader[USERS_EMAIL_FIELD].ToString();
string mobile = sqlDataReader[USERS_MOBILE_FIELD].ToString();
string address = sqlDataReader[USERS_ADDRESS_FIELD].ToString();
string paypal_id = sqlDataReader[USERS_PAYPAL_ID_FIELD].ToString();
string betfair_id = sqlDataReader[USERS_BETFAIR_ID_FIELD].ToString();
User user = new User(
user_id,
role_id,
name,
email,
mobile,
address,
paypal_id,
betfair_id
);
resultList.Add(user);
}
}
catch (InvalidOperationException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (SqlException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (FormatException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
catch (OverflowException exception)
{
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
//Closes the datareader and connection to the database
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
//Returns the list of fetched users in the form of an array
return resultList.ToArray();
}
///**
// * @brief Returns all greyhounds from DB as pages
// * @return Greyhound[] An array with all greyhounds.
// **/
//public static Greyhound[] GetGreyhoundsPaged(string filter, int startRec, int endRec, string sortBy, string sortType)
//{
// //Setup the command to run
// string strCommand = GET_ALL_GREYHOUNDS_PAGED_SQL;
// strCommand = strCommand.Replace("@start_rec", startRec.ToString());
// strCommand = strCommand.Replace("@end_rec", endRec.ToString());
// strCommand = strCommand.Replace("@filter", filter);
// strCommand = strCommand.Replace("@sortBy", sortBy);
// strCommand = strCommand.Replace("@sortType", sortType);
// List<Greyhound> resultList = executeQuery(strCommand);
// Tools.printDebug(null, strCommand, null);
// return resultList.ToArray();
//}
/**
* @brief Returns all greyhounds from DB
* @return Greyhound[] An array with all greyhounds.
**/
public static Greyhound[] GetGreyhoundsRunningAtDate(User user, string date_time)
{
//Setup the command to run
string strCommand = GET_GREYHOUNDS_RACING_AT_DATE_SQL;
strCommand = strCommand.Replace("@date_time", date_time.ToString());
List<Greyhound> resultList = executeQuery(user, strCommand);
Tools.printDebug(null, strCommand, null);
return resultList.ToArray();
}
/**
* @brief Adds a user to the database
* @param User user The user to add
**/
public static int RegisterNewUser(User user)
{
int lastInsertedId = -1;
bool error = false;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleTransaction transaction = null;
try
{
//Open the connection
connection.Open();
//Start a local transaction
transaction = connection.BeginTransaction();
//First we add the user to the database
//Setup the command to run
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.Transaction = transaction;
//Get the next id
command.CommandText = DatabaseSchema.SELECT_USERS_NEXT_ID;
int user_id = Convert.ToInt32(command.ExecuteScalar());
if (user_id > 0)
{
command.Parameters.Clear();
command.CommandText = INSERT_USER;
//First we set the parameters
command.Parameters.Add("user_id", user_id);
command.Parameters.Add("password", user.Password);
command.Parameters.Add("role_id", (int)user.Role_Id);
command.Parameters.Add("name", user.Name);
command.Parameters.Add("email", user.Email);
command.Parameters.Add("registered", DatabaseSchema.USER_NOT_REGISTERED);
command.Parameters.Add("mobile", user.Mobile);
command.Parameters.Add("address", user.Address);
command.Parameters.Add("paypal_id", user.Paypal_Id);
command.Parameters.Add("betfair_id", user.Betfair_Id);
//Run the command
command.ExecuteNonQuery();
lastInsertedId = user_id;
}
}
catch (Exception exception)
{
error = true;
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
if (transaction != null)
{
if (error)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
//Closes the datareader and connection to the database
if (connection != null)
{
connection.Close();
}
}
return lastInsertedId;
}
