private void handle()
{
using (MySqlConnection conn = new MySqlConnection("server=120.24.210.35;user id=hrsdata;password=abcd0000;database=hrsdata;port=3306;charset=utf8"))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT a.*, b.`id` card_id FROM ct_race a
INNER JOIN ct_card b ON b.`tournament_id` = a.`tournament_id` AND b.`tote_type` = 'HK'
WHERE a.time_text IS NOT NULL
AND a.race_loc = 3
LIMIT 10;
", conn))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
using (DataTable table = new DataTable())
{
da.Fill(table);
List <RaceData> data = new List <RaceData>();
foreach (DataRow row in table.Rows)
{
string date_str = (string)row["race_date"];
string time_str = (string)row["time_text"];
Match md = Regex.Match(date_str, @"^(\d{2})-(\d{2})-(\d{4})$");
// Match mt = Regex.Match(time_str, @"^(\d{2})\:(\d{2})(pm|am)$");
if (md.Success)
{
RaceData race = new RaceData();
race.StartTime = DateTime.Parse(string.Format("{0}-{1}-{2} {3}",
md.Groups[3].Value,
md.Groups[2].Value,
md.Groups[1].Value,
//mt.Groups[3].Value == "pm" ? int.Parse(mt.Groups[1].Value) + 12 : int.Parse(mt.Groups[1].Value),
//mt.Groups[2].Value
time_str));
race.CardID = (ulong)row["card_id"];
race.RaceNo = (int)row["race_no"];
data.Add(race);
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_wp_tote where rc_id = ?rc_id and cd_id = ?cd_id", conn))
{
cmd2.Parameters.AddWithValue("?rc_id", row["id"]);
cmd2.Parameters.AddWithValue("?cd_id", row["card_id"]);
using (MySqlDataReader dr = cmd2.ExecuteReader())
{
while (dr.Read())
{
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
long time = (long)dr["record_time"];
RaceDataItem item = new RaceDataItem();
race.Add(time, item);
JArray ja = (JArray)JsonConvert.DeserializeObject((string)dr["raw_info"]);
foreach (JObject jo in ja)
{
item.Odds.Add(new Hrs(jo["horseNo"].ToString(), (double)jo["win"], (double)jo["plc"]));
}
}
}
}
}
if (race.Count == 0)
{
continue;
}
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_qn_tote where rc_id = ?rc_id and cd_id = ?cd_id and record_time between ?min_time and ?max_time", conn))
{
cmd2.Parameters.AddWithValue("?rc_id", row["id"]);
cmd2.Parameters.AddWithValue("?cd_id", row["card_id"]);
cmd2.Parameters.AddWithValue("?min_time", race.MinTime - 10000);
cmd2.Parameters.AddWithValue("?max_time", race.MaxTime + 10000);
using (MySqlDataReader dr = cmd2.ExecuteReader())
{
while (dr.Read())
{
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
long time = (long)dr["record_time"];
RaceDataItem item = race.GetNearestItem(time, 10000);
if (item != null && !item.Odds.HasSpQ && !item.Odds.HasSpQp)
{
JObject jo = (JObject)JsonConvert.DeserializeObject((string)dr["raw_info"]);
this.parseQnTote((string)jo["text_q"], item.Odds.SpQ);
this.parseQnTote((string)jo["text_qp"], item.Odds.SpQp);
}
}
}
}
}
}
}
foreach (ulong cardId in data.Select(x => x.CardID).Distinct().ToArray())
{
using (MySqlCommand cmd2 = new MySqlCommand("select id, record_time from ct_raw_wp_discount where raw_info is not null and cd_id = ?cd_id and record_time between ?min_time and ?max_time", conn))
{
cmd2.Parameters.AddWithValue("?cd_id", cardId);
cmd2.Parameters.AddWithValue("?min_time", data.Where(x => x.Count > 0).Select(x => x.MinTime).Min() - 10000);
cmd2.Parameters.AddWithValue("?max_time", data.Where(x => x.Count > 0).Select(x => x.MaxTime).Max() + 10000);
using (DataTable table_tmp = new DataTable())
{
using (MySqlDataAdapter da2 = new MySqlDataAdapter(cmd2))
{
da2.Fill(table_tmp);
foreach (DataRow row_tmp in table_tmp.Rows)
{
long time = (long)row_tmp["record_time"];
IEnumerable <RaceData> list = data.Where(x => x.CardID == cardId);
Dictionary <int, RaceDataItem> items = new Dictionary <int, RaceDataItem>();
foreach (RaceData race in list)
{
RaceDataItem item = race.GetNearestItem(time, 10000);
if (item != null)
{
items.Add(race.RaceNo, item);
}
}
if (items.Count == 0)
{
continue;
}
using (MySqlCommand cmd3 = new MySqlCommand("select * from ct_raw_wp_discount where id = ?id", conn))
{
cmd3.Parameters.AddWithValue("?id", row_tmp["id"]);
using (MySqlDataReader dr = cmd3.ExecuteReader())
{
while (dr.Read())
{
string text = (string)dr["raw_info"];
foreach (string line in text.Split('\n'))
{
if (line.Length > 0)
{
string[] elements = line.Split('\t');
int raceNo = int.Parse(elements[0]);
if (!items.ContainsKey(raceNo))
{
continue;
}
Match mLimit = Regex.Match(elements[5], @"^(!)?(\d+(?:\.\d+)?)\/(\d+(?:\.\d+)?)$");
string horseNo = elements[1];
WaterWP water;
if (dr["direction"].ToString() == "0")
{
water = items[raceNo].Waters.GetWpBetWater(horseNo);
}
else
{
water = items[raceNo].Waters.GetWpEatWater(horseNo);
}
water.Add(new WaterWPItem()
{
Percent = double.Parse(elements[4]),
WinAmount = double.Parse(elements[2]),
WinLimit = double.Parse(mLimit.Groups[2].Value),
PlcAmount = double.Parse(elements[3]),
PlcLimit = double.Parse(mLimit.Groups[3].Value)
});
}
}
}
}
}
}
}
}
}
using (MySqlCommand cmd2 = new MySqlCommand("select id, record_time from ct_raw_qn_discount where raw_info is not null and cd_id = ?cd_id and record_time between ?min_time and ?max_time", conn))
{
cmd2.Parameters.AddWithValue("?cd_id", cardId);
cmd2.Parameters.AddWithValue("?min_time", data.Where(x => x.Count > 0).Select(x => x.MinTime).Min() - 10000);
cmd2.Parameters.AddWithValue("?max_time", data.Where(x => x.Count > 0).Select(x => x.MaxTime).Max() + 10000);
using (DataTable table_tmp = new DataTable())
{
using (MySqlDataAdapter da2 = new MySqlDataAdapter(cmd2))
{
da2.Fill(table_tmp);
foreach (DataRow row_tmp in table_tmp.Rows)
{
long time = (long)row_tmp["record_time"];
IEnumerable <RaceData> list = data.Where(x => x.CardID == cardId);
Dictionary <int, RaceDataItem> items = new Dictionary <int, RaceDataItem>();
foreach (RaceData race in list)
{
RaceDataItem item = race.GetNearestItem(time, 10000);
if (item != null)
{
items.Add(race.RaceNo, item);
}
}
if (items.Count == 0)
{
continue;
}
using (MySqlCommand cmd3 = new MySqlCommand("select * from ct_raw_qn_discount where id = ?id", conn))
{
cmd3.Parameters.AddWithValue("?id", row_tmp["id"]);
using (MySqlDataReader dr = cmd3.ExecuteReader())
{
while (dr.Read())
{
string text = (string)dr["raw_info"];
foreach (string line in text.Split('\n'))
{
if (line.Length > 0)
{
string[] elements = line.Split('\t');
int raceNo = int.Parse(elements[0]);
if (!items.ContainsKey(raceNo))
{
continue;
}
string horseNo = Regex.Replace(elements[1], @"^\((\d+\-\d+)\)$", "$1");
WaterQn water;
if (dr["type"].ToString() == "Q")
{
if (dr["direction"].ToString() == "0")
{
water = items[raceNo].Waters.GetQnBetWater(horseNo);
}
else
{
water = items[raceNo].Waters.GetQnEatWater(horseNo);
}
}
else
{
if (dr["direction"].ToString() == "0")
{
water = items[raceNo].Waters.GetQpBetWater(horseNo);
}
else
{
water = items[raceNo].Waters.GetQpEatWater(horseNo);
}
}
water.Add(new WaterQnItem()
{
Percent = double.Parse(elements[3]),
Amount = double.Parse(elements[2]),
Limit = double.Parse(elements[4])
});
}
}
}
}
}
}
}
}
}
}
foreach (RaceData race in data.Where(x => x.Count > 0))
{
race.Save(string.Format("{0:yyyy-MM-dd}-{1}-{2}.dat", race.StartTime, race.CardID, race.RaceNo));
}
}
}
}
}
}
private void handle()
{
using (MySqlConnection conn = new MySqlConnection("server=120.24.210.35;user id=hrsdata;password=abcd0000;database=hrsdata;port=3306;charset=utf8"))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(@"
SELECT a.*, b.`id` card_id FROM ct_race a
INNER JOIN ct_card b ON b.`tournament_id` = a.`tournament_id` AND b.`tote_type` = 'HK'
WHERE a.time_text IS NOT NULL
AND a.race_loc = 3
and a.id >= 699540
LIMIT 10
", conn))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
using (DataTable table = new DataTable())
{
da.Fill(table);
List <RaceData> data = new List <RaceData>();
#region 建立时间点并获取Tote
foreach (DataRow row in table.Rows)
{
string date_str = (string)row["race_date"];
string time_str = (string)row["time_text"];
Match md = Regex.Match(date_str, @"^(\d{2})-(\d{2})-(\d{4})$");
// Match mt = Regex.Match(time_str, @"^(\d{2})\:(\d{2})(pm|am)$");
if (md.Success)
{
RaceData race = new RaceData();
race.StartTime = DateTime.Parse(string.Format("{0}-{1}-{2} {3}",
md.Groups[3].Value,
md.Groups[2].Value,
md.Groups[1].Value,
//mt.Groups[3].Value == "pm" ? int.Parse(mt.Groups[1].Value) + 12 : int.Parse(mt.Groups[1].Value),
//mt.Groups[2].Value
time_str));
race.CardID = (ulong)row["card_id"];
race.RaceNo = (int)row["race_no"];
data.Add(race);
// 从开始时间往前推，每5分钟一个点，直到开赛前55分钟共12个点
// 每个点前后2.5分钟区间，取最靠近的数据
// 如果WP_TOTE/QN_TOTE/WP_BET_DISCOUNT/WP_EAT_DISCOUNT/QN_BET_DISCOUNT/QN_EAT_DISCOUNT/QP_BET_DISCOUNT/QP_EAT_DISCOUNT任意一项没有数据，则丢弃该时间点
// 建立时间点
long start_time_key = ToUnixTime(race.StartTime);
for (int i = 0; i < 12; i++)
{
race.Add(start_time_key - i * 5 * 60 * 1000, new RaceDataItem());
}
foreach (long tp in race.Keys.ToArray())
{
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_wp_tote where rc_id = ?rc_id and cd_id = ?cd_id and record_time between ?tb and ?te order by abs(record_time-?tp) limit 1", conn))
{
cmd2.CommandTimeout = 120000;
cmd2.Parameters.AddWithValue("?rc_id", row["id"]);
cmd2.Parameters.AddWithValue("?cd_id", row["card_id"]);
cmd2.Parameters.AddWithValue("?tb", tp - 150 * 1000);
cmd2.Parameters.AddWithValue("?te", tp + 150 * 1000);
cmd2.Parameters.AddWithValue("?tp", tp);
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
RaceDataItem item = race[tp];
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_tote_wp_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
JArray ja = (JArray)JsonConvert.DeserializeObject(rawInfo);
foreach (JObject jo in ja)
{
double win = (double)jo["win"];
double plc = (double)jo["plc"];
if (win != 0 && plc != 0)
{
item.Odds.Add(new Hrs(jo["horseNo"].ToString(), win, plc));
}
}
}
else
{
race.Remove(tp);
continue;
}
}
else
{
race.Remove(tp);
continue;
}
}
}
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_qn_tote where rc_id = ?rc_id and cd_id = ?cd_id and record_time between ?tb and ?te order by abs(record_time-?tp) limit 1", conn))
{
cmd2.CommandTimeout = 120000;
cmd2.Parameters.AddWithValue("?rc_id", row["id"]);
cmd2.Parameters.AddWithValue("?cd_id", row["card_id"]);
cmd2.Parameters.AddWithValue("?tb", tp - 150 * 1000);
cmd2.Parameters.AddWithValue("?te", tp + 150 * 1000);
cmd2.Parameters.AddWithValue("?tp", tp);
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
RaceDataItem item = race[tp];
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_tote_qn_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
JObject jo = (JObject)JsonConvert.DeserializeObject(rawInfo);
this.parseQnTote((string)jo["text_q"], item.Odds.SpQ);
this.parseQnTote((string)jo["text_qp"], item.Odds.SpQp);
}
else
{
race.Remove(tp);
continue;
}
}
else
{
race.Remove(tp);
continue;
}
}
}
}
}
}
#endregion
#region 获取Discount
foreach (ulong cardId in data.Where(x => x.Count > 0).Select(x => x.CardID).Distinct().ToArray())
{
foreach (long tp in data.Where(x => x.CardID == cardId).SelectMany(x => x.Keys).Distinct().ToArray())
{
Dictionary <int, RaceDataItem> items = new Dictionary <int, RaceDataItem>();
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
if (race.ContainsKey(tp))
{
items.Add(race.RaceNo, race[tp]);
}
}
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_wp_discount where cd_id = ?cd_id and direction = ?d and record_time between ?tb and ?te order by abs(record_time-?tp) limit 1", conn))
{
cmd2.CommandTimeout = 120000;
cmd2.Parameters.AddWithValue("?cd_id", cardId);
cmd2.Parameters.AddWithValue("?tb", tp - 150 * 1000);
cmd2.Parameters.AddWithValue("?te", tp + 150 * 1000);
cmd2.Parameters.AddWithValue("?tp", tp);
MySqlParameter pD = cmd2.Parameters.Add("?d", MySqlDbType.Byte);
pD.Value = 0;
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_wp_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseWpDiscount(rawInfo, items, dr["direction"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
pD.Value = 1;
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_wp_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseWpDiscount(rawInfo, items, dr["direction"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
}
using (MySqlCommand cmd2 = new MySqlCommand("select * from ct_raw_qn_discount where cd_id = ?cd_id and direction = ?d and `type` = ?type and record_time between ?tb and ?te order by abs(record_time-?tp) limit 1", conn))
{
cmd2.CommandTimeout = 120000;
cmd2.Parameters.AddWithValue("?cd_id", cardId);
cmd2.Parameters.AddWithValue("?tb", tp - 150 * 1000);
cmd2.Parameters.AddWithValue("?te", tp + 150 * 1000);
cmd2.Parameters.AddWithValue("?tp", tp);
MySqlParameter pD = cmd2.Parameters.Add("?d", MySqlDbType.Byte);
MySqlParameter pT = cmd2.Parameters.Add("?type", MySqlDbType.VarChar, 20);
pD.Value = 0;
pT.Value = "Q";
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_qn_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseQnDiscount(rawInfo, items, dr["direction"].ToString(), dr["type"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
pD.Value = 1;
pT.Value = "Q";
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_qn_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseQnDiscount(rawInfo, items, dr["direction"].ToString(), dr["type"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
pD.Value = 0;
pT.Value = "QP";
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_qn_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseQnDiscount((string)rawInfo, items, dr["direction"].ToString(), dr["type"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
pD.Value = 1;
pT.Value = "QP";
using (MySqlDataReader dr = this.TryGetReader(cmd2))
{
if (this.TryRead(dr))
{
string rawInfo;
if (!object.Equals(dr["raw_info"], DBNull.Value))
{
rawInfo = (string)dr["raw_info"];
}
else
{
rawInfo = this.getRawInfoByAPI("get_discount_qn_raw_info", dr["id"].ToString());
}
if (rawInfo != null)
{
this.parseQnDiscount(rawInfo, items, dr["direction"].ToString(), dr["type"].ToString());
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
else
{
foreach (RaceData race in data.Where(x => x.CardID == cardId))
{
race.Remove(tp);
}
continue;
}
}
}
}
foreach (RaceData race in data.Where(x => x.CardID == cardId && x.Count > 0))
{
race.Save(string.Format("sp-{0:yyyy-MM-dd}-{1}-{2}.dat", race.StartTime, race.CardID, race.RaceNo));
}
}
#endregion
}
}
}
}
}