public IBkaData Get(long playerId)
{
IBkaData result = null;
string bkaJson = null;
using (var connection = new MySqlConnection(ConnectionString))
{
connection.Open();
using (var command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = @"SELECT datavalue FROM data WHERE datatypename = 'bka' and datarealm = 'ns2' AND datarecordid = @PlayerId;";
command.Prepare();
command.Parameters.AddWithValue("@PlayerId", playerId);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
bkaJson = reader.GetString("datavalue");
}
}
}
}
if (!string.IsNullOrWhiteSpace(bkaJson))
{
result = _bkaDataParser.Parse(bkaJson);
}
return(result);
}
protected override string GetResponseJson(string realmName, HttpRequest request, MySqlConnection connection)
{
var names = new List <string>();
using (var command = new MySqlCommand {
Connection = connection
})
{
command.CommandText = @"select x.PlayerId, d.DataValue
from (select COUNT(DISTINCT EXTRACT( YEAR_MONTH FROM RowCreated )) AS SupportingMemberMonths,
PlayerId
from games_players
where SupportingMember
group by 2) x
inner join data d on x.playerid = d.DataRecordId and d.datatypename = 'bka' and d.datarealm = 'ns2' and d.datavalue not like '%""BKA"":""""%' and d.datavalue not like '%""BKA"": """"%' and x.SupportingMemberMonths >= 12
order by SupportingMemberMonths DESC";
command.Prepare();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var bkaData = _bkaDataParser.Parse(reader.GetString("DataValue"));
if (!string.IsNullOrWhiteSpace(bkaData?.Bka))
{
names.Add(bkaData.Bka);
}
}
}
}
var result = JsonConvert.SerializeObject(new Dictionary <string, object> {
{ "success", true }, { "names", names }
});
return(result);
}
public IEnumerable <IPlayer> Get(int badgeId)
{
var result = new List <IPlayer>();
using (var connection = new MySqlConnection(ConnectionString))
{
connection.Open();
using (var command = new MySqlCommand {
Connection = connection
})
{
command.CommandText = "select x.playerid AS PlayerId, d.datavalue AS DataValue from (select distinct playerid from achievements_badges_players where badgeid = @BadgeId and achievementsrealm = 'ns2') x left outer join data d on x.playerid = d.datarecordid and d.datarealm = 'ns2' and d.datatypename = 'bka'";
command.Prepare();
command.Parameters.AddWithValue("@BadgeId", badgeId);
command.CommandTimeout = 120;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var playerId = reader.GetInt64("PlayerId");
var bkaJson = reader.GetString("DataValue");
var bkaData = _bkaDataParser.Parse(bkaJson);
result.Add(new Player(bkaData.Bka, playerId));
}
}
}
}
return(result);
}
protected override string GetResponseJson(string realmName, HttpRequest request, MySqlConnection connection)
{
var bkaDatas = new List <BkaData>();
using (var command = new MySqlCommand {
Connection = connection
})
{
command.CommandText = @"select DataValue from data where datatypename = 'bka' and datavalue like '%""BKA"":%' and datavalue not like '%""BKA"":""""%' and datavalue not like '%""BKA"": """"%' and datarealm = @BkaRealm";
command.Prepare();
command.Parameters.AddWithValue("@BkaRealm", realmName);
string query = command.Parameters.Cast <MySqlParameter>().Aggregate(command.CommandText, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var bkaData = _bkaDataParser.Parse(reader.GetString("DataValue"));
bkaDatas.Add(new BkaData(bkaData.PlayerId, bkaData.Bka, bkaData.PlayerSetGmtInSeconds));
}
}
}
var data = bkaDatas.Select(x => new { id = x.PlayerId, bka = x.Bka });
var result = JsonConvert.SerializeObject(new Dictionary <string, object> {
{ "success", true }, { "result", data }
});
return(result);
}
public IEnumerable <ISpecBetsData> Get()
{
using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["Data"].ConnectionString))
{
connection.Open();
using (var command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = @"select t.makerplayerid, t.Total, t.PayoutRate, t.BetCount, t.BetMax, t.BetAverage, t.PayoutCount, t.PayoutMax, t.PayoutAverage, d.datavalue as BkaJson
from (select makerplayerid, sum(amount) as Total, sum(case type when 'payout' then 1 else 0 end) / sum(case type when 'bet' then 1 else 0 end) As PayoutRate, sum(case type when 'bet' then 1 else 0 end) as BetCount, min(case type when 'bet' then amount else 0 end) as BetMax, sum(case type when 'bet' then amount else 0 end) / sum(case type when 'bet' then 1 else 0 end) as BetAverage, sum(case type when 'payout' then 1 else 0 end) as PayoutCount, max(case type when 'payout' then amount else 0 end) as PayoutMax, sum(case type when 'payout' then amount else 0 end) / sum(case type when 'payout' then 1 else 0 end) as PayoutAverage
from bet_transactions
where realm = 'ns2'
and type <> 'playcredit'
group by makerplayerid
order by total desc
limit 50) t
inner join data d on t.makerplayerid = d.datarecordid
where d.datatypename = 'bka' and datavalue not like '%""BKA"":""""%' and datavalue not like '%""BKA"": """"%' and d.datarealm = 'ns2'
order by total desc
limit 25";
command.Prepare();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var bkaJson = reader.GetString("BkaJson");
var total = reader.GetDouble("Total");
var payoutRate = reader.GetDouble("PayoutRate");
var betCount = reader.GetInt64("BetCount");
var betMax = reader.GetDouble("BetMax");
var betAverage = reader.GetDouble("BetAverage");
var payoutCount = reader.GetInt64("PayoutCount");
var payoutMax = reader.GetDouble("PayoutMax");
var payoutAverage = reader.GetDouble("PayoutAverage");
var bkaData = _bkaDataParser.Parse(bkaJson);
var result = new SpecBetsData(new Player(bkaData.Bka, bkaData.PlayerId), total, payoutRate, betCount, betMax, betAverage, payoutCount, payoutMax, payoutAverage);
yield return(result);
}
}
}
}
}