public LotteryDrawResult GetLatestDrawResult(int gameCd)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " SELECT TOP 1 * " +
" FROM `draw_results` " +
" WHERE `game_cd`= @game_cd " +
" ORDER BY `draw_date` DESC";
command.Parameters.AddWithValue("@game_cd", gameCd.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetLotteryDrawResultSetup(reader,
ClassReflectionUtil.FindGameMode(int.Parse(reader["game_cd"].ToString()))));
}
}
}
return(null);;
}
public List <int> GetTopDrawnDigitFromDateRange(GameMode gameMode, DateTime dateFrom, DateTime dateTo)
{
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 draw_date BETWEEN CDATE(@dateFrom) AND CDATE(@dateTo)";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Parameters.AddWithValue("@dateFrom", dateFrom.Date.ToString());
command.Parameters.AddWithValue("@dateTo", dateTo.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 static User loginUser(String userName, String password)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "Select * from [User] where username= @User_name and password= @pass";
cmd.Parameters.Add("User_name", System.Data.SqlDbType.VarChar, 128).Value = userName;
cmd.Parameters.Add("pass", System.Data.SqlDbType.VarChar, 128).Value = password;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
ELearn.User usr = null;
if (dr["userType"].ToString().Equals("student"))
{
usr = new Student(Convert.ToInt32(dr["id"]), userName, dr["password"].ToString(), dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, Convert.ToInt32(dr["classID"]));
}
else if (dr["userType"].ToString().Equals("teacher"))
{
usr = new Teacher(Convert.ToInt32(dr["id"]), userName, dr["password"].ToString(), dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now);
}
else if (dr["userType"].ToString().Equals("staff"))
{
usr = new Staff(Convert.ToInt32(dr["id"]), userName, dr["password"].ToString(), dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, true);
}
dr.Close();
SqlCommand newCMD = new SqlCommand();
newCMD.Connection = con;
newCMD.CommandText = "update [User] set lastSeen = '" + DateTime.Now.ToString() + "' where userName= '******'";
newCMD.ExecuteNonQuery();
return(usr);
}
return(null);
}
public Models.TransferControl GetTransferControl(int transferControlId)
{
var whereClause = " WHERE TransferControlId = " + transferControlId;
string selectTransferControlFileSql = @"SELECT [TransferControlFileId]
,[TransferControlId]
,[FileLocation]
FROM TransferControlFile" +
whereClause;
using (var connection = DatabaseConnectionFactory.GetWarehouseManagementConnection())
{
connection.Open();
var transferControl = connection.Query <Models.TransferControl>(SelectTransferControlSql + whereClause).FirstOrDefault();
if (transferControl != null)
{
transferControl.Files = connection.Query <TransferControlFile>(selectTransferControlFileSql).ToList();
}
return(transferControl);
}
}
public void Create(CustomerDTO t)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseInsertQuery = @"INSERT INTO [NORTHWNDSDP-162].[dbo].[Customers] " +
"(CustomerID, CompanyName, ContactName, ContactTitle, " +
"Address, City, Region, PostalCode, Country, Phone, Fax) " +
"VALUES (" +
"'{0}', '{1}','{2}','{3},'{4}'," +
"'{5}','{6}','{7}','{8}','{9}'," +
"'{10}')";
string realInsertQuery = String.Format(baseInsertQuery,
t.CustomerId,
t.CompanyName,
t.ContactName,
t.ContactTitle,
t.Address,
t.City,
t.Region,
t.PostalCode,
t.Country,
t.Phone,
t.Fax);
sqlCommand.CommandText = realInsertQuery;
sqlCommand.CommandType = CommandType.Text;
int result = sqlCommand.ExecuteNonQuery();
Console.WriteLine(result);
}
sqlConnection.Close();
}
}
public IList <DatabaseKioskOrderExport> GetDatabaseKioskOrderExports()
{
IList <DatabaseKioskOrderExport> databaseKioskOrderExports;
using (var connection = DatabaseConnectionFactory.GetDwhExportConnection())
{
databaseKioskOrderExports = connection.Query <DatabaseKioskOrderExport>("SELECT * FROM [kiosk_orders]").ToList();
}
if (databaseKioskOrderExports.Count == 0)
{
_log.Info("No orders found in kiosk_orders from export table... will read directly from dw_orders stored procedure.");
using (var connection = DatabaseConnectionFactory.GetDeckisKioskonnection())
{
var now = DateTime.Now;
var parameters = new DynamicParameters();
parameters.Add("@start_date", new DateTime(now.Year, now.Month, now.Day, 0, 0, 0).AddDays(-45));
parameters.Add("@end_date", now.AddDays(1));
databaseKioskOrderExports = connection.Query <DatabaseKioskOrderExport>("dw_Orders", commandType: CommandType.StoredProcedure, param: parameters).ToList();
}
}
return(databaseKioskOrderExports);
}
public void UpdateClaimStatus(LotteryWinningBet winBet)
{
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = " UPDATE lottery_winning_bet SET claim_status = @claim_status " +
" WHERE ID = @id AND active = true";
command.Parameters.AddWithValue("@claim_status", winBet.IsClaimed());
command.Parameters.AddWithValue("@id", winBet.GetID());
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_msg12"), winBet.GetID()));
}
transaction.Commit();
}
}
public void InsertShipmentInventoryAdjustmentProcessing(IList <ShipmentInventoryAdjustment> shipmentInventoryAdjustments)
{
using (var connection = DatabaseConnectionFactory.GetWarehouseManagementTransactionConnection())
{
var inventoryShipmentProcessingTable = new DataTable();
inventoryShipmentProcessingTable.Columns.Add("ManhattanShipmentLineItemId");
inventoryShipmentProcessingTable.Columns.Add("ManhattanDateCreated");
inventoryShipmentProcessingTable.Columns.Add("ManhattanTimeCreated");
foreach (var adj in shipmentInventoryAdjustments)
{
inventoryShipmentProcessingTable.Rows.Add(adj.ManhattanShipmentLineItemId, adj.ManhattanDateCreated, adj.ManhattanTimeCreated);
}
var parameter = new
{
InventoryShipmentProcessingTable = inventoryShipmentProcessingTable.AsTableValuedParameter("[dbo].[InventoryShipmentProcessingTable]")
};
connection.Open();
connection.Execute("sp_InsertInventoryShipmentProcessing", parameter, commandType: CommandType.StoredProcedure);
}
}
internal ProdutoComposicaoRepositorio(DatabaseConnectionFactory factory)
{
_connection = factory.Create();
}
protected Dao()
{
Connection = DatabaseConnectionFactory.Create();
}
public ActionResult Index()
{
Homepage homepage = new Homepage();
homepage.Title = "Fit After Fourty-Five";
DatabaseConnectionFactory factory = DatabaseConnectionFactory.Instance;
IDbConnection connection = factory.GetConnection("DefaultConnection");
IRepository <WelcomeModel> repo = new WelcomeRepository(connection);
WelcomeModel model = repo.Get(1);
homepage.Welcome = new WelcomeSection();
homepage.Welcome.Heading = model.Title;
homepage.Welcome.Blurb = model.Content;
homepage.Welcome.ImageUrl = model.ImageUrl;
IRepository <MeetMeModel> repoMeetMe = new MeetMeRepository(connection);
MeetMeModel meetMe = repoMeetMe.Get(1);
homepage.MeetMe = new MeetMeSection();
homepage.MeetMe.Heading = meetMe.Title;
homepage.MeetMe.ImageUrl = meetMe.ImageUrl;
homepage.MeetMe.Blurb = meetMe.Content;
IRepository <ServicesModel> repoServices = new MyServicesRepository(connection);
ServicesModel services = repoServices.Get(1);
homepage.MyServices = new MyServicesSection();
homepage.MyServices.Heading = services.Title;
homepage.MyServices.ImageUrl = services.ImageUrl;
homepage.MyServices.Blurb = services.Content;
List <IHomePageTemplate> listOfServices = new List <IHomePageTemplate>();
foreach (var svc in services.Services.Services)
{
listOfServices.Add(new Models.ViewModel.Service()
{
Heading = svc.Title,
Blurb = svc.Content,
ImageUrl = svc.ImageUrl,
SubHeading = svc.SubTitle
});
}
homepage.MyServices.Services = listOfServices;
TestimonialRepository tRepo = new TestimonialRepository(connection);
TestimonialModel testimonials = tRepo.Get(1);
homepage.Testimonials = new MyTestimonialsSection();
homepage.Testimonials.Testimonials = new List <IHomePageTemplate>();
homepage.Testimonials.Heading = "Client Testimonials";
homepage.Testimonials.Testimonials = new List <IHomePageTemplate>();
List <IHomePageTemplate> listOfTestimonials = new List <IHomePageTemplate>();
foreach (var t in testimonials.Testimonials.Testimonials)
{
listOfTestimonials.Add(new Models.ViewModel.Testimonal()
{
Heading = t.Title,
Blurb = t.Content + "<br/><br/>~" + t.Name,
ImageUrl = t.ImageUrl
});
}
homepage.Testimonials.Testimonials = listOfTestimonials;
return(View(homepage));
}
public ProdutoDoisRepositorio(DatabaseConnectionFactory factory)
{
_connection = factory.Create();
_InsumoRepositorio = new InsumoRepositorio(factory);
_CustoReposicaoProdutoRepositorio = new CustoReposicaoProdutoRepositorio(factory);
}
// This method gets called by the runtime. Use this method to add services to the container.
/// <summary>
///
/// </summary>
/// <param name="services"></param>
/// <exception cref="ArgumentNullException"></exception>
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
//
// Log level
LogEventLevel logEventLevel;
try
{
logEventLevel = (LogEventLevel)Enum.Parse(typeof(LogEventLevel),
Configuration["LoggerSettings:LogLevel"], true);
}
catch (Exception)
{
logEventLevel = LogEventLevel.Information;
}
//
// Serilog configuration
var loggerConfiguration = new LoggerConfiguration()
.MinimumLevel.Is(logEventLevel)
.Enrich.FromLogContext();
if (bool.Parse(Configuration["LoggerSettings:LogToConsole"]))
{
loggerConfiguration = loggerConfiguration.WriteTo.Console();
}
if (bool.Parse(Configuration["LoggerSettings:LogToFile"]))
{
loggerConfiguration = loggerConfiguration.WriteTo.File(
Configuration["LoggerSettings:LogFile"],
fileSizeLimitBytes: 1_000_000,
rollOnFileSizeLimit: true,
shared: true,
flushToDiskInterval: TimeSpan.FromSeconds(1));
}
Log.Logger = loggerConfiguration.CreateLogger();
// Register the Swagger generator, defining 1 or more Swagger documents
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new Info
{
Version = "v1",
Title = "XYZ Widget API",
Description = "A API for working with the XYZ Widget backend",
TermsOfService = "None",
Contact = new Contact
{
Name = "Mike Melusky",
Email = "*****@*****.**",
Url = "https://twitter.com/mrjavascript"
},
License = new License
{
Name = "Use under Apache License",
Url = "https://www.apache.org/licenses/LICENSE-2.0"
}
});
// Set the comments path for the Swagger JSON and UI.
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
c.IncludeXmlComments(xmlPath);
});
//
// Dependency Injection for services and repositories
IDatabaseConnectionFactory databaseConnectionFactory =
new DatabaseConnectionFactory(Configuration.GetConnectionString("XYZ_WIDGET"));
services.AddSingleton <IWidgetRepository>(s =>
{
if (s == null)
{
throw new ArgumentNullException(nameof(s));
}
return(new WidgetRepository(databaseConnectionFactory));
});
services.AddSingleton <IWidgetService, WidgetService>();
//
// Dapper config
DefaultTypeMap.MatchNamesWithUnderscores = true;
}
internal InsumoRepositorio(DatabaseConnectionFactory factory)
{
_connection = factory.Create();
}
public SalarioRepositorio(DatabaseConnectionFactory factory)
{
_connection = factory.Create();
}
/// <summary>
/// Scripts a database to disk for use in creating a brand new db. This
/// method scripts the entire object, i.e. no differntial or comparisons
/// are done.
/// </summary>
/// <param name="connectionInfo">The db connection parameters.</param>
/// <param name="scriptingOptions">The export scripting settings.</param>
public virtual void Script(IConnectionSettings connectionInfo, IScriptingOptions scriptingOptions)
{
Throw.If(connectionInfo, "connectionInfo").IsNull();
Throw.If(scriptingOptions, "scriptingOptions").IsNull();
messageManager.OnScriptMessage("Starting database scripting.");
IDatabase db = DatabaseConnectionFactory.CreateDbConnection(connectionInfo);
List <ITable> tablesToScript = new List <ITable>();
foreach (DbObjectName tableName in scriptingOptions.TablesToScript)
{
ITable table = db.Tables[tableName.ShortName];
if (table == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the table {0} in database {1} on server.",
tableName, connectionInfo.Database));
}
tablesToScript.Add(table);
}
IScriptWriter writer = CreateScriptWriter(scriptingOptions, connectionInfo);
int totalObjects = CalculateScriptObjectCount(scriptingOptions);
int exportCount = 0;
if (scriptingOptions.ScriptSchema)
{
foreach (ITable table in tablesToScript)
{
ScriptTableSchema(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptIndexes)
{
foreach (ITable table in tablesToScript)
{
ScriptTableIndexes(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptData || scriptingOptions.ScriptDataAsXml)
{
IScriptWriter dataWriter = writer;
IDataMigrator dataMigrator = new DataMigrator();
if (scriptingOptions.ScriptDataAsXml)
{
dataMigrator = new XmlDataMigrator();
dataWriter = new XmlDataWriter(scriptingOptions.ExportDirectory, messageManager);
}
foreach (ITable table in tablesToScript)
{
ScriptTableData(dataMigrator, table, dataWriter);
OnProgressEvent(++exportCount, totalObjects);
}
}
if (scriptingOptions.ScriptForeignKeys)
{
foreach (ITable table in tablesToScript)
{
ScriptTableForeignKeys(table, writer);
OnProgressEvent(++exportCount, totalObjects);
}
}
foreach (DbObjectName sprocName in scriptingOptions.SprocsToScript)
{
IProcedure sproc = db.Procedures[sprocName.ShortName];
if (sproc == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the procedure {0} in database {1} on server.",
sprocName, connectionInfo.Database));
}
ScriptSproc(sproc, writer);
OnProgressEvent(++exportCount, totalObjects);
}
foreach (DbObjectName viewName in scriptingOptions.ViewsToScript)
{
IView view = db.Views[viewName.ShortName];
if (view == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the view {0} in database {1} on server.",
viewName, connectionInfo.Database));
}
ScriptView(view, writer);
OnProgressEvent(++exportCount, totalObjects);
}
messageManager.OnScriptMessage("Finished database scripting.");
}
public ProdutoRepositorio(DatabaseConnectionFactory factory)
{
_connection = factory.Create();
_produtoComposicaoRepositorio = new ProdutoComposicaoRepositorio(factory);
}
public TransactionsRepository(DatabaseConnectionFactory databaseConnectionFactory)
{
_databaseConnectionFactory = databaseConnectionFactory;
// new DatabaseConnectionFactory(
// "Server=localhost;Port=5432;Database=silverspy;User ID=postgres;Password=postgres");
}
public static IGlobalConfiguration UseMediatR(this IGlobalConfiguration config, IMediator mediator, DatabaseConnectionFactory getDbConnection)
{
config.UseActivator(new MediatRJobActivator(mediator, getDbConnection));
JobHelper.SetSerializerSettings(new JsonSerializerSettings
{
TypeNameHandling = TypeNameHandling.Objects,
});
GetDatabaseConnection = getDbConnection;
CreateMediatRTable();
return(config);
}
/// <summary>
/// Returns an alter script to upgrade a database from target version
/// to source version.
/// </summary>
/// <remarks>
/// <para>This method is not thread safe.</para>
/// <para>Operations are scripted in the following order:
/// <list type="number">
/// <item>create new tables</item>
/// <item>add new columns to existing tables</item>
/// <item>drop removed foreign keys</item>
/// <item>drop removed columns</item>
/// <item>drop removed tables</item>
/// <item>alter any changed columns (like if you added a new default to a column)</item>
/// <item>add new foreign keys</item>
/// <item>add new indexes</item>
/// <item>functions</item>
/// <item>views</item>
/// <item>sprocs</item>
/// </list>
/// </para>
/// </remarks>
/// <param name="connectionInfoSourceDb">The newer, source db.</param>
/// <param name="connectionInfoTargetDb">The older, target db to upgrade.</param>
/// <param name="scriptingOptions">The export scripting settings.</param>
public virtual void ScriptDifferences(IConnectionSettings connectionInfoSourceDb,
IConnectionSettings connectionInfoTargetDb,
IScriptingOptions scriptingOptions)
{
Throw.If(connectionInfoSourceDb, "connectionInfoSourceDb").IsNull();
Throw.If(connectionInfoTargetDb, "connectionInfoTargetDb").IsNull();
Throw.If(scriptingOptions, "scriptingOptions").IsNull();
messageManager.OnScriptMessage("Starting scripting database differences.");
IDatabase srcDb = DatabaseConnectionFactory.CreateDbConnection(connectionInfoSourceDb);
IDatabase targetDb = DatabaseConnectionFactory.CreateDbConnection(connectionInfoTargetDb);
IScriptWriter writer = CreateScriptWriter(scriptingOptions, connectionInfoTargetDb);
int totalObjects = CalculateScriptObjectCount(scriptingOptions);
int exportCount = 0;
foreach (DbObjectName tableName in scriptingOptions.TablesToScript)
{
// TODO: Need to split this up like the Script() method to order things correctly?
ITable table = srcDb.Tables[tableName.ShortName];
if (table == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the source table {0} in database {1} on server.",
tableName, connectionInfoSourceDb.Database));
}
ITable targetTable = targetDb.Tables[tableName.ShortName];
if (targetTable == null)
{
throw new SqlMigrationException(
string.Format("Unable to find the target table {0} in database {1} on server.",
tableName, connectionInfoTargetDb.Database));
}
if (scriptingOptions.ScriptData)
{
ScriptTableDataDifferences(table, targetTable, writer);
OnProgressEvent(++exportCount, totalObjects);
}
// TODO: constraints, Schema, Indexes?
}
messageManager.OnScriptMessage("Finished scripting database differences.");
// sourceDB = source;
// targetDB = target;
//
// messageManager.OnScriptMessage("Starting database differencing.");
//
// script = new SqlScript();
//
// ScriptNewTablesAndColumns();
// ScriptRemovedForeignKeys();
// ScriptRemovedIndexes();
// ScriptRemovedTablesAndColumns();
// ScriptAlteredColumns();
// ScriptNewForeignKeys();
// ScriptNewIndexes();
//
// ScriptNewAndAlteredSprocs();
// ScriptRemovedSprocs();
//
// messageManager.OnScriptMessage("Finished database differencing.");
}
public SqlQueueFactory()
{
scriptProvider = ScriptProvider.Default();
databaseConnectionFactory = DatabaseConnectionFactory.Default();
databaseGateway = DatabaseGateway.Default();
}
public CollectibleRepository()
{
connection = DatabaseConnectionFactory.create();
}
public List <String[]> GetWinningBetDigitTally(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 NUM,SUM(HIT) AS [HIT2] " +
" FROM ( " +
" SELECT a.num1 AS [NUM],COUNT(a.num1) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num1,2 "+
" "+
" UNION "+
" "+
" SELECT a.num2 AS [NUM],COUNT(a.num2) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num2,2 "+
" "+
" UNION "+
" "+
" SELECT a.num3 AS [NUM],COUNT(a.num3) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num3,2 "+
" "+
" UNION "+
" "+
" SELECT a.num4 AS [NUM],COUNT(a.num4) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num4,2 "+
" "+
" UNION "+
" "+
" SELECT a.num5 AS [NUM],COUNT(a.num5) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num5,2 "+
" "+
" UNION "+
" "+
" SELECT a.num6 AS [NUM],COUNT(a.num6) AS [HIT] "+
" FROM lottery_winning_bet a "+
" LEFT OUTER JOIN lottery_bet b ON a.bet_id = b.ID "+
" WHERE "+ GetMultipleGameCodeSQLPredicate(gameCodes) +
" AND a.active = true "+
" AND b.active = true "+
" GROUP BY a.num6,2 "+
" ) "+
" WHERE NUM > 0 " +
" GROUP BY NUM " +
" ORDER BY 2 DESC,1 ASC ";
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!String.IsNullOrWhiteSpace(reader["NUM"].ToString()))
{
String[] result = new String[2] {
"0", "0"
};
result[0] = reader["NUM"].ToString();
result[1] = reader["HIT2"].ToString();
resultList.Add(result);
}
}
}
}
}
return(resultList);
}
/// <summary>
/// Ctor.
/// </summary>
/// <param name="databaseConnectionFactory">connection factory</param>
/// <param name="sql">statement sql</param>
/// <param name="contextAttributes">The context attributes.</param>
public ConnectionCacheImpl(DatabaseConnectionFactory databaseConnectionFactory, String sql, IEnumerable <Attribute> contextAttributes)
: base(databaseConnectionFactory, sql, contextAttributes)
{
}
public List <LotteryWinningBet> GetLotteryWinningBet(GameMode gameMode, DateTime sinceWhen)
{
List <LotteryWinningBet> lotteryWinningBetArr = new List <LotteryWinningBet>();
using (OleDbConnection conn = DatabaseConnectionFactory.GetDataSource())
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT a.*, " +
" b.ID as [b_ID], " +
" b.bet_id as [b_bet_id], " +
" b.winning_amt as [b_winning_amt], " +
" b.active as [b_active], " +
" b.num1 as [b_num1], " +
" b.num2 as [b_num2], " +
" b.num3 as [b_num3], " +
" b.num4 as [b_num4], " +
" b.num5 as [b_num5], " +
" b.num6 as [b_num6], " +
" b.claim_status as [b_claim_status], " +
" c.ID as [c_ID], " +
" c.outlet_cd as [c_outlet_cd], " +
" c.description as [c_description] " +
" FROM ((lottery_bet a " +
" LEFT OUTER JOIN lottery_winning_bet b " +
" ON a.ID = b.bet_Id) " +
" LEFT OUTER JOIN lottery_outlet c " +
" ON a.outlet_cd = c.outlet_cd) " +
" WHERE a.game_cd = @game_cd " +
" AND a.target_draw_date >= CDATE(@sinceWhen) " +
" AND a.active = true " +
" AND b.active = true " +
//" AND c.active = true " +
" AND b.winning_amt > 0";
command.Parameters.AddWithValue("@game_cd", (int)gameMode);
command.Parameters.AddWithValue("@sinceWhen", sinceWhen.Date.ToString());
command.Connection = conn;
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
LotteryWinningBetSetup lotteryWinningBet = new LotteryWinningBetSetup();
lotteryWinningBet.ID = long.Parse(reader["b_ID"].ToString());
lotteryWinningBet.TargetDrawDate = DateTime.Parse(reader["target_draw_date"].ToString());
lotteryWinningBet.LotteryBetId = long.Parse(reader["b_bet_id"].ToString());
lotteryWinningBet.WinningAmount = double.Parse(reader["b_winning_amt"].ToString());
lotteryWinningBet.Num1 = int.Parse(reader["num1"].ToString());
lotteryWinningBet.Num2 = int.Parse(reader["num2"].ToString());
lotteryWinningBet.Num3 = int.Parse(reader["num3"].ToString());
lotteryWinningBet.Num4 = int.Parse(reader["num4"].ToString());
lotteryWinningBet.Num5 = int.Parse(reader["num5"].ToString());
lotteryWinningBet.Num6 = int.Parse(reader["num6"].ToString());
lotteryWinningBet.ClaimStatus = bool.Parse(reader["b_claim_status"].ToString());
lotteryWinningBet.OutletCd = int.Parse(reader["c_outlet_cd"].ToString());
lotteryWinningBet.OutletDesc = reader["c_description"].ToString();
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num1"].ToString()));
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num2"].ToString()));
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num3"].ToString()));
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num4"].ToString()));
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num5"].ToString()));
lotteryWinningBet.AddWinningNumber(int.Parse(reader["b_num6"].ToString()));
lotteryWinningBetArr.Add(lotteryWinningBet);
}
}
}
return(lotteryWinningBetArr);
}
