public List <LotteryDrawResult> GetJackpotDrawResults(GameMode gameMode)
{
List <LotteryDrawResult> results = new List <LotteryDrawResult>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM draw_results " +
" WHERE game_cd = @game_cd " +
" AND winners> 0 " +
" ORDER BY draw_date DESC";
command.Parameters.AddWithValue("@game_cd", gameMode);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
results.Add(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(results);
}
public void UpdateDescription(LotteryOutlet updatedModel)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " UPDATE lottery_outlet SET description = @description " +
" WHERE ID = @id AND outlet_cd = @outlet_cd AND active = true";
command.Parameters.AddWithValue("@description", StringUtils.Truncate(updatedModel.GetDescription(), MAX_LEN_DESCRIPTION));
command.Parameters.AddWithValue("@id", updatedModel.GetId());
command.Parameters.AddWithValue("@outlet_cd", updatedModel.GetOutletCode());
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg2"), updatedModel.GetDescription()));
}
transaction.Commit();
}
}
public int InsertSetting(String configName, String value)
{
int lastInsertID = 0;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO user_setting (config_name, `value`) VALUES (@configName, @value) ";
command.Parameters.AddWithValue("@configName", configName);
command.Parameters.AddWithValue("@value", value);
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("user_sett_dao_impl_msg2"), configName, value));
}
else
{
transaction.Commit();
lastInsertID = base.GetLastInsertedID(command);
}
}
return(lastInsertID);
}
public List <LotteryDrawResult> GetFastTreeMLDataSet(GameMode gameMode, DateTime startingDate)
{
List <LotteryDrawResult> results = new List <LotteryDrawResult>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 500 jackpot_amt,ID,draw_date,num1,num2,num3,num4,num5,num6,winners,game_cd, " +
" FORMAT(num1,'00') + FORMAT(num2,'00') + FORMAT(num3,'00') + "+
" FORMAT(num4,'00') + FORMAT(num5,'00') + FORMAT(num6,'00') AS ['result'] "+
" FROM draw_results " +
" WHERE game_cd = @game_cd " +
" AND `draw_date` > CDATE(@startingDate) "+
" ORDER BY `draw_date` ASC ";
command.Parameters.AddWithValue("@game_cd", gameMode);
command.Parameters.AddWithValue("@startingDate", startingDate.Date.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
results.Add(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(results);
}
public double GetTotalWinningsAmountThisMonth(GameMode gameMode)
{
LotteryWinningBetSetup lotteryWinningBet = new LotteryWinningBetSetup();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT SUM(winning_amt) AS [WINNING_AMOUNT] " +
" FROM lottery_winning_bet a " +
" INNER JOIN lottery_bet b on a.bet_id = b.id " +
" WHERE a.active = true " +
" AND b.game_cd = @game_cd " +
" AND MONTH(target_draw_date) = MONTH(NOW()) " +
" AND YEAR(target_draw_date) = YEAR(NOW()) " +
" AND a.claim_status = true";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!String.IsNullOrEmpty(reader["WINNING_AMOUNT"].ToString()))
{
return(double.Parse(reader["WINNING_AMOUNT"].ToString()));
}
}
}
}
}
return(0.00);
}
public List <LotteryDrawResult> GetDrawResultWinCountMLDataset(GameMode gameMode, DateTime startingDate)
{
List <LotteryDrawResult> results = new List <LotteryDrawResult>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 500 ID, jackpot_amt, `game_cd`, `draw_date`, " +
" `num1` ,`num2`, num3, num4, num5, num6, " +
" IIF(winners > 0, 1, 0) AS `winners` " +
" FROM `draw_results` " +
" WHERE game_cd = @game_cd " +
" AND `draw_date` > CDATE(@startingDate) " +
" ORDER BY `game_cd` ASC, `draw_date` ASC";
command.Parameters.AddWithValue("@game_cd", gameMode);
command.Parameters.AddWithValue("@startingDate", startingDate.Date.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
results.Add(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(results);
}
public LotteryDrawResult GetLotteryDrawResultByDrawDate(GameMode gameMode, DateTime drawDate)
{
LotteryDrawResultSetup lotteryDrawResult = null;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = GetStandardSelectQuery() + " AND draw_date = CDATE(@drawDate)";
command.Parameters.AddWithValue("@game_cd", OleDbType.Integer).Value = (int)gameMode;
command.Parameters.AddWithValue("@drawDate", OleDbType.DBDate).Value = drawDate.ToString();
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
return(lotteryDrawResult);
}
while (reader.Read())
{
return(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(lotteryDrawResult);
}
public void InsertWinningBet(LotteryWinningBet lotteryWinningBet)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO lottery_winning_bet " +
" (bet_id, winning_amt, active, claim_status, num1, num2, num3, num4, num5, num6)" +
" VALUES (@lotteryBetID, @winningAmt, true, @claimStatus, @num1, @num2, @num3, @num4, @num5, @num6)";
command.Parameters.AddWithValue("@lotteryBetID", lotteryWinningBet.GetLotteryBetId());
command.Parameters.AddWithValue("@winningAmt", lotteryWinningBet.GetWinningAmount());
command.Parameters.AddWithValue("@claimStatus", lotteryWinningBet.IsClaimed());
command.Parameters.AddWithValue("@num1", lotteryWinningBet.GetNum1());
command.Parameters.AddWithValue("@num2", lotteryWinningBet.GetNum2());
command.Parameters.AddWithValue("@num3", lotteryWinningBet.GetNum3());
command.Parameters.AddWithValue("@num4", lotteryWinningBet.GetNum4());
command.Parameters.AddWithValue("@num5", lotteryWinningBet.GetNum5());
command.Parameters.AddWithValue("@num6", lotteryWinningBet.GetNum6());
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg10"), lotteryWinningBet.GetLotteryBetId()));
}
transaction.Commit();
}
}
public int InsertSequenceGenerator(LotterySequenceGenerator seqGen)
{
int modified = 0;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO `lottery_seq_gen` (`seqgencd`, `description`, `active`) VALUES(@seqgencd, @desc, true); ";
command.Connection = conn;
command.Parameters.AddWithValue("@seqgencd", seqGen.GetSeqGenCode());
command.Parameters.AddWithValue("@desc", seqGen.GetDescription());
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(ResourcesUtils.GetMessage("lot_dao_impl_msg13"));
}
else
{
transaction.Commit();
modified = base.GetLastInsertedID(command);
}
}
return(modified);
}
public Lottery GetLottery(GameMode gameCode)
{
LotterySetup lotterySetup = new LotterySetup();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM lottery WHERE game_cd = @game_cd AND active = true;";
command.Parameters.AddWithValue("@game_cd", gameCode);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lotterySetup.GameCode = gameCode;
lotterySetup.Description = reader["description"].ToString();
lotterySetup.PricePerBet = double.Parse(reader["price_per_bet"].ToString());
lotterySetup.WebScrapeGameCode = int.Parse(reader["web_scrape_code"].ToString());
}
}
}
return(lotterySetup);
}
public int InsertAppVersioning(AppVersioning appVersion)
{
int modified = 0;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO `app_versioning` " +
" (`major`, `minor`, `patch`, `releaseversion`, `remarks`, `datetimeapplied`) " +
" VALUES(@major, @minor, @patch, @releaseversion, @remarks, @datetimeapplied); ";
command.Connection = conn;
command.Parameters.AddWithValue("@major", appVersion.GetMajor());
command.Parameters.AddWithValue("@minor", appVersion.GetMinor());
command.Parameters.AddWithValue("@patch", appVersion.GetPatch());
command.Parameters.AddWithValue("@releaseversion", appVersion.GetReleaseCycle());
command.Parameters.AddWithValue("@remarks", appVersion.GetRemarks());
command.Parameters.AddWithValue("@datetimeapplied", appVersion.GetDateTimeApplied().ToString());
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(ResourcesUtils.GetMessage("lot_dao_impl_msg14"));
}
else
{
transaction.Commit();
modified = base.GetLastInsertedID(command);
}
}
return(modified);
}
public List <Lottery> GetLotteries()
{
List <Lottery> lotterySetupArr = new List <Lottery>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM lottery WHERE active = true;";
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
LotterySetup lotterySetup = new LotterySetup();
lotterySetup.GameCode = ClassReflectionUtil.FindGameMode(int.Parse(reader["game_cd"].ToString()));
lotterySetup.Description = reader["description"].ToString();
lotterySetup.PricePerBet = double.Parse(reader["price_per_bet"].ToString());
lotterySetup.WebScrapeGameCode = int.Parse(reader["web_scrape_code"].ToString());
lotterySetupArr.Add(lotterySetup);
}
}
}
return(lotterySetupArr);
}
public List <int> GetTopDrawnDigitFromJackpotsResults(GameMode gameMode)
{
List <int> merge = new List <int>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT num1, num2, num3, num4, num5, num6 " +
" FROM draw_results " +
" WHERE game_cd = @game_cd " +
" AND winners > 0";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
merge.Add(int.Parse(reader["num1"].ToString()));
merge.Add(int.Parse(reader["num2"].ToString()));
merge.Add(int.Parse(reader["num3"].ToString()));
merge.Add(int.Parse(reader["num4"].ToString()));
merge.Add(int.Parse(reader["num5"].ToString()));
merge.Add(int.Parse(reader["num6"].ToString()));
}
}
}
}
return(merge);
}
public void InsertDrawDate(LotteryDrawResult lotteryDrawResult)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO draw_results (draw_date,jackpot_amt,winners,game_cd,num1,num2,num3,num4,num5,num6) " +
" VALUES (@draw_date,@jackpot_amt,@winners,@game_cd,@num1,@num2,@num3,@num4,@num5,@num6)";
command.Parameters.AddWithValue("@draw_date", lotteryDrawResult.GetDrawDate());
command.Parameters.AddWithValue("@jackpot_amt", lotteryDrawResult.GetJackpotAmt());
command.Parameters.AddWithValue("@winners", lotteryDrawResult.GetWinnersCount());
command.Parameters.AddWithValue("@game_cd", lotteryDrawResult.GetGameCode());
command.Parameters.AddWithValue("@num1", lotteryDrawResult.GetNum1());
command.Parameters.AddWithValue("@num2", lotteryDrawResult.GetNum2());
command.Parameters.AddWithValue("@num3", lotteryDrawResult.GetNum3());
command.Parameters.AddWithValue("@num4", lotteryDrawResult.GetNum4());
command.Parameters.AddWithValue("@num5", lotteryDrawResult.GetNum5());
command.Parameters.AddWithValue("@num6", lotteryDrawResult.GetNum6());
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(ResourcesUtils.GetMessage("lot_dao_impl_msg9"));
}
transaction.Commit();
}
}
public DateTime GetLatestDrawDate(GameMode gameMode)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 1 draw_date " +
" FROM draw_results " +
" WHERE game_cd = @game_cd" +
" ORDER BY draw_date DESC ";
command.Parameters.AddWithValue("@game_cd", OleDbType.Integer).Value = (int)gameMode;
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
return(DateTime.Parse(reader["draw_date"].ToString()));
}
}
}
}
return(DateTimeConverterUtils.GetYear2011());
}
public List <LotteryDrawResult> GetDrawResultsFromStartingDate(GameMode gameMode, DateTime startingDrawDate, DateTime endingDate)
{
List <LotteryDrawResult> results = new List <LotteryDrawResult>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = GetStandardSelectQuery() +
" AND draw_date BETWEEN CDATE(@startingDrawDate) AND CDATE(@endingDate) " +
" ORDER BY draw_date DESC";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Parameters.AddWithValue("@startingDrawDate", startingDrawDate.ToString());
command.Parameters.AddWithValue("@endingDate", endingDate.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
results.Add(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(results);
}
public void RemoveOutlet(LotteryOutlet modelToRemove)
{
if (IsLotteryOutletUsed(modelToRemove.GetOutletCode()))
{
throw new Exception(ResourcesUtils.GetMessage("lot_dao_impl_msg1"));
}
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " UPDATE lottery_outlet SET active = false " +
" WHERE ID = @id AND outlet_cd = @outlet_cd " +
" AND description = @outlet_desc AND active = true ";
command.Parameters.AddWithValue("@id", modelToRemove.GetId());
command.Parameters.AddWithValue("@outlet_cd", modelToRemove.GetOutletCode());
command.Parameters.AddWithValue("@outlet_desc", modelToRemove.GetDescription());
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg2"), modelToRemove.GetDescription()));
}
transaction.Commit();
}
}
public bool IsDescriptionExisting(String outletDescription)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT count(ID) AS [total] FROM lottery_outlet " +
" WHERE description = @outletDescription " +
" AND active = true ";
command.Parameters.AddWithValue("@outletDescription", outletDescription);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int total = int.Parse(reader["total"].ToString());
return(total > 0);
}
}
}
}
return(false);
}
public AppVersioning GetVersion(AppVersioning appVersion)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT * " +
" FROM `app_versioning` " +
" WHERE major = @major " +
" AND minor = @minor " +
" AND patch = @patch " +
" AND `releaseversion` = @rversion ";
command.Parameters.AddWithValue("@major", appVersion.GetMajor());
command.Parameters.AddWithValue("@minor", appVersion.GetMinor());
command.Parameters.AddWithValue("@patch", appVersion.GetPatch());
command.Parameters.AddWithValue("@rversion", appVersion.GetReleaseCycle());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetModel(reader));
}
}
}
return(null);
}
public List <LotteryDrawResult> GetSDCAMLDataSet(GameMode gameMode, DateTime startingDate)
{
List <LotteryDrawResult> results = new List <LotteryDrawResult>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 500 * " +
" FROM draw_results " +
" WHERE game_cd = @game_cd " +
" AND `draw_date` > CDATE(@startingDate) "+
" ORDER BY `draw_date` ASC ";
command.Parameters.AddWithValue("@game_cd", gameMode);
command.Parameters.AddWithValue("@startingDate", startingDate.Date.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
results.Add(GetLotteryDrawResultSetup(reader, gameMode));
}
}
}
return(results);
}
public List <String[]> GetOutletTally(List <int> gameCodes)
{
List <String[]> resultList = new List <string[]>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT a.outlet_cd, o.description, " +
" SUM(a.bet_amt) as [total_spending], "+
" SUM(IIF(b.num1 > 0 AND b.num2=0 AND b.num3=0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num1], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3=0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num2], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num3], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5=0 AND b.num6=0,1,0)) AS [num4], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5>0 AND b.num6=0,1,0)) AS [num5], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5>0 AND b.num6>0,1,0)) AS [num6] "+
" FROM ((lottery_bet a " +
" INNER JOIN lottery_winning_bet b " +
" ON a.ID = b.bet_ID) " +
" LEFT OUTER JOIN lottery_outlet o " +
" ON a.outlet_cd = o.outlet_cd) " +
" WHERE a." + GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true " +
" AND b.active = true " +
" GROUP BY a.outlet_cd, o.description " +
" ORDER BY 2 ASC ";
//command.Parameters.AddWithValue("@game_cd", GetMultipleGameCodeSQLPredicate(gameCodes));
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!String.IsNullOrWhiteSpace(reader["num1"].ToString()))
{
String[] result = new String[8] {
"", "0", "0", "0", "0", "0", "0", "0"
};
result[0] = reader["description"].ToString();
result[1] = reader["total_spending"].ToString();
result[2] = reader["num1"].ToString();
result[3] = reader["num2"].ToString();
result[4] = reader["num3"].ToString();
result[5] = reader["num4"].ToString();
result[6] = reader["num5"].ToString();
result[7] = reader["num6"].ToString();
resultList.Add(result);
}
}
}
}
}
return(resultList);
}
public List <int> GetTopDrawnPreviousSeasonDigitResults(GameMode gameMode)
{
List <int> merge = new List <int>();
LotteryDrawResult[] lotteryDrawResultArr = GetJackpotDrawResults(gameMode).ToArray();
if (lotteryDrawResultArr.Length < 2)
{
return(merge);
}
DateTime fromDate = lotteryDrawResultArr[1].GetDrawDate();
DateTime toDate = lotteryDrawResultArr[0].GetDrawDate();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT a.num1, a.num2, a.num3, a.num4, a.num5, a.num6 " +
" FROM draw_results a " +
" WHERE a.game_cd = @game_cd " +
" AND a.draw_date BETWEEN CDATE(@fromDate) " +
" AND CDATE(@toDate) " +
" AND a.draw_date<> CDATE(@fromDate) " +
" ORDER BY a.draw_date DESC";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Parameters.AddWithValue("@fromDate", fromDate.Date.ToString());
command.Parameters.AddWithValue("@toDate", toDate.Date.ToString());
command.Parameters.AddWithValue("@fromDate", fromDate.Date.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
merge.Add(int.Parse(reader["num1"].ToString()));
merge.Add(int.Parse(reader["num2"].ToString()));
merge.Add(int.Parse(reader["num3"].ToString()));
merge.Add(int.Parse(reader["num4"].ToString()));
merge.Add(int.Parse(reader["num5"].ToString()));
merge.Add(int.Parse(reader["num6"].ToString()));
}
}
}
}
return(merge);
}
public LotterySchedule GetLotterySchedule(GameMode gameMode)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand("SELECT * FROM lottery_schedule WHERE game_cd = ? AND active = true", conn)) {
command.Parameters.AddWithValue("game_cd", gameMode);
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetModel(reader));
}
}
}
return(null);
}
public DataTable GetLotteryDrawResult(GameMode gameMode, DateTime drawDate)
{
DataTable dt = new DataTable();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = GetDrawResultCommandByRange(gameMode, drawDate, drawDate))
{
command.Connection = conn;
conn.Open();
using (OleDbDataReader _dr = command.ExecuteReader())
{
dt.Load(_dr);
}
}
return(dt);
}
public int[] GetWinningBetNumbersTally(GameMode gameMode)
{
int[] result = new int[6] {
0, 0, 0, 0, 0, 0
};
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT SUM(IIF(b.num1 > 0 AND b.num2=0 AND b.num3=0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num1], " +
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3=0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num2], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4=0 AND b.num5=0 AND b.num6=0,1,0)) AS [num3], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5=0 AND b.num6=0,1,0)) AS [num4], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5>0 AND b.num6=0,1,0)) AS [num5], "+
" SUM(IIF(b.num1 > 0 AND b.num2>0 AND b.num3>0 AND b.num4>0 AND b.num5>0 AND b.num6>0,1,0)) AS [num6] "+
" FROM lottery_bet a " +
" INNER JOIN lottery_winning_bet b " +
" ON a.ID = b.bet_ID " +
" WHERE a.game_cd = @game_cd " +
" AND a.active = true " +
" AND b.active = true ";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!String.IsNullOrWhiteSpace(reader["num1"].ToString()))
{
result[0] = int.Parse(reader["num1"].ToString());
result[1] = int.Parse(reader["num2"].ToString());
result[2] = int.Parse(reader["num3"].ToString());
result[3] = int.Parse(reader["num4"].ToString());
result[4] = int.Parse(reader["num5"].ToString());
result[5] = int.Parse(reader["num6"].ToString());
}
}
}
}
}
return(result);
}
public List <LotterySequenceGenerator> GetAllSeqGenerators()
{
List <LotterySequenceGenerator> lotterySeqGenList = new List <LotterySequenceGenerator>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand("SELECT * FROM lottery_seq_gen WHERE active = true ORDER BY description ASC", conn))
{
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lotterySeqGenList.Add(GetModel(reader));
}
}
}
return(lotterySeqGenList);
}
public int InsertLotteryOutlet(String outletDescription)
{
if (IsDescriptionExisting(outletDescription.Trim()))
{
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg4"), outletDescription));
}
int modified = 0;
int nextOutletCode = this.GetNextOutletCode();
if (nextOutletCode <= 0)
{
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg5"), outletDescription));
}
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " INSERT INTO `lottery_outlet` (`outlet_cd`, `description`, `active`) " +
" VALUES(@nextOutletCode, @outletDescription, true)";
command.Parameters.AddWithValue("@nextOutletCode", nextOutletCode);
command.Parameters.AddWithValue("@outletDescription", StringUtils.Truncate(outletDescription, MAX_LEN_DESCRIPTION));
command.Connection = conn;
conn.Open();
OleDbTransaction transaction = conn.BeginTransaction();
command.Transaction = transaction;
int result = command.ExecuteNonQuery();
if (result < 0)
{
transaction.Rollback();
throw new Exception(String.Format(ResourcesUtils.GetMessage("lot_dao_impl_msg3"), outletDescription));
}
else
{
transaction.Commit();
modified = base.GetLastInsertedID(command);
}
}
return(modified);
}
public LotterySequenceGenerator GetSeqGeneratorByCode(int seqGenCode)
{
LotterySequenceGeneratorSetup lotterySeqGen = null;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand("SELECT * FROM lottery_seq_gen WHERE seqgencd = @seqGenCode AND active = true", conn))
{
command.Parameters.AddWithValue("@seqGenCode", seqGenCode);
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lotterySeqGen = GetModel(reader);
}
}
}
return(lotterySeqGen);
}
public LotteryWinningCombination GetLotteryWinningCombination(GameMode gameMode)
{
LotteryWinningCombinationSetup lotteryWinningCombinationSetup = null;
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand("SELECT * FROM lottery_winning_combination WHERE game_cd = ? AND active = true;", conn))
{
command.Parameters.AddWithValue("game_cd", gameMode);
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lotteryWinningCombinationSetup = GetModel(reader);
}
}
}
return(lotteryWinningCombinationSetup);
}
public AppVersioning GetLatestVersion()
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 1 * FROM `app_versioning` ORDER BY `datetimeapplied` DESC ";
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetModel(reader));
}
}
}
return(null);
}