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 } } } } }