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(string filename)
{
RaceData race = RaceData.Load(filename);
if (race.Count < 2)
{
return;
}
long tp = race.First().Key;
if (tp - race.Skip(1).First().Key != 300000)
{
return;
}
RaceDataItem item = race.First().Value;
RaceDataItem item2 = race.Skip(1).First().Value;
if (item.Odds.E == 0 || item2.Odds.E == 0)
{
return;
}
double[] p1, p3, pq_win, pq_plc;
Fitting.calcProbility(item2.Odds, out p1, out p3, out pq_win, out pq_plc);
// 计算预计概率与当前赔率下下注比例的交叉熵
double[] q1, qq;
double r1, rq;
q1 = Fitting.calcBetRateForWin(item.Odds, out r1);
qq = Fitting.calcBetRateForQn(item.Odds, out rq);
double E = cross_entropy(p1, q1) + cross_entropy(pq_win, qq);
// 当前赔率下交叉熵过大则退出，不下单
if (E > item2.Odds.E * E_THRESHOLD_SCALE)
{
this.Invoke(new MethodInvoker(delegate
{
this.txtLog.AppendText(string.Format("{0:HH:mm:ss} > file {1} 变化过于剧烈{2}/{3}\r\n", DateTime.Now, filename, E, item2.Odds.E));
}));
return;
}
List <InvestRecordWp> wp_records = new List <InvestRecordWp>();
for (int i = 0; i < item.Odds.Count; i++)
{
Hrs h = item.Odds[i];
double sp_w_min = Math.Min(h.Win, item2.Odds[i].Win);
double sp_w_max = Math.Max(h.Win, item2.Odds[i].Win);
double sp_p_min = Math.Min(h.Plc, item2.Odds[i].Plc);
double sp_p_max = Math.Max(h.Plc, item2.Odds[i].Plc);
// For Bet
{
WaterWPList vlist = item.Waters.GetWpEatWater(h.No).GetValuableWater(MIN_R, sp_w_min, p1[i], sp_p_min, p3[i]);
double bet_amount_win = 0, bet_amount_plc = 0;
bool full_win = false, full_plc = false;
foreach (WaterWPItem w in vlist.OrderBy(x => x.Percent))
{
if (w.WinAmount > 0 && full_win)
{
continue;
}
if (w.PlcAmount > 0 && full_plc)
{
continue;
}
double bet_amount = -1;
if (w.WinAmount > 0)
{
double O = Math.Min(w.WinLimit / LIMIT_SCALE, sp_w_min) * 100 / w.Percent;
double max_bet = (T * Math.Pow(O * p1[i] - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * p1[i] * (1 - p1[i]));
if (bet_amount_win >= max_bet)
{
full_win = true;
continue;
}
else
{
bet_amount = Math.Min(max_bet - bet_amount_win, w.WinAmount);
}
}
if (w.PlcAmount > 0)
{
double O = Math.Min(w.PlcLimit / LIMIT_SCALE, sp_p_min) * 100 / w.Percent;
double max_bet = (T * Math.Pow(O * p3[i] - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * p3[i] * (1 - p3[i]));
if (bet_amount_plc >= max_bet)
{
full_plc = true;
continue;
}
else
{
if (bet_amount == -1)
{
bet_amount = Math.Min(max_bet - bet_amount_plc, w.PlcAmount);
}
else
{
bet_amount = Math.Min(bet_amount, Math.Min(max_bet - bet_amount_plc, w.PlcAmount)); // 有Win也有Plc, 那么Plc肯定和Win一样
}
}
}
if (bet_amount > 0)
{
bet_amount = Math.Round(bet_amount / WP_STEP) * WP_STEP;
if (bet_amount > 0)
{
InvestRecordWp ir = new InvestRecordWp()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "BET",
HorseNo = h.No,
Percent = w.Percent,
WinLimit = w.WinLimit,
PlcLimit = w.PlcLimit,
FittingLoss = item.Odds.E
};
if (w.WinLimit > 0)
{
ir.WinAmount = bet_amount;
ir.WinOdds = sp_w_min;
ir.WinProbility = p1[i];
}
if (w.PlcLimit > 0)
{
ir.PlcAmount = bet_amount;
ir.PlcOdds = sp_p_min;
ir.PlcProbility = p3[i];
}
wp_records.Add(ir);
bet_amount_win += ir.WinAmount;
bet_amount_plc += ir.PlcAmount;
}
}
}
}
// For Eat
{
WaterWPList vlist = item.Waters.GetWpBetWater(h.No).GetValuableWater(MIN_R, sp_w_max, p1[i], sp_p_max, p3[i]);
double eat_amount_win = 0, eat_amount_plc = 0;
bool full_win = false, full_plc = false;
foreach (WaterWPItem w in vlist.OrderByDescending(x => x.Percent))
{
if (w.WinAmount > 0 && full_win)
{
continue;
}
if (w.PlcAmount > 0 && full_plc)
{
continue;
}
double eat_amount = -1;
if (w.WinAmount > 0)
{
double O = 1 + w.Percent / 100 / Math.Min(w.WinLimit / LIMIT_SCALE, sp_w_max);
double max_eat = (T * Math.Pow(O * (1 - p1[i]) - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * (1 - p1[i]) * p1[i]);
max_eat = max_eat / Math.Min(w.WinLimit / LIMIT_SCALE, sp_w_max);
if (eat_amount_win >= max_eat)
{
full_win = true;
continue;
}
else
{
eat_amount = Math.Min(max_eat - eat_amount_win, w.WinAmount);
}
}
if (w.PlcAmount > 0)
{
double O = 1 + w.Percent / 100 / Math.Min(w.PlcLimit / LIMIT_SCALE, sp_p_max);
double max_eat = (T * Math.Pow(O * (1 - p3[i]) - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * (1 - p3[i]) * p3[i]);
max_eat = max_eat / Math.Min(w.PlcLimit / LIMIT_SCALE, sp_p_max);
if (eat_amount_plc >= max_eat)
{
full_plc = true;
continue;
}
else
{
if (eat_amount == -1)
{
eat_amount = Math.Min(max_eat - eat_amount_plc, w.PlcAmount);
}
else
{
eat_amount = Math.Min(eat_amount, Math.Min(max_eat - eat_amount_plc, w.PlcAmount));
}
}
}
if (eat_amount > 0)
{
eat_amount = Math.Round(eat_amount / WP_STEP) * WP_STEP;
if (eat_amount > 0)
{
InvestRecordWp ir = new InvestRecordWp()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "EAT",
HorseNo = h.No,
Percent = w.Percent,
WinLimit = w.WinLimit,
PlcLimit = w.PlcLimit,
FittingLoss = item.Odds.E
};
if (w.WinLimit > 0)
{
ir.WinAmount = eat_amount;
ir.WinOdds = sp_w_max;
ir.WinProbility = p1[i];
}
if (w.PlcLimit > 0)
{
ir.PlcAmount = eat_amount;
ir.PlcOdds = sp_p_max;
ir.PlcProbility = p3[i];
}
wp_records.Add(ir);
eat_amount_win += ir.WinAmount;
eat_amount_plc += ir.PlcAmount;
}
}
}
}
}
List <InvestRecordQn> qn_records = new List <InvestRecordQn>();
common.Math.Combination comb2 = new common.Math.Combination(item.Odds.Count, 2);
int[][] combinations = comb2.GetCombinations();
for (int i = 0; i < combinations.Length; i++)
{
int[] c = combinations[i];
string horseNo = string.Format("{0}-{1}", item.Odds[c[0]].No, item.Odds[c[1]].No);
if (pq_win != null)
{
double sp_min = Math.Min(item.Odds.SpQ[horseNo], item2.Odds.SpQ[horseNo]);
double sp_max = Math.Max(item.Odds.SpQ[horseNo], item2.Odds.SpQ[horseNo]);
// For Bet
{
WaterQnList vlist = item.Waters.GetQnEatWater(horseNo).GetValuableWater(MIN_R, sp_min, pq_win[i]);
double bet_amount = 0;
foreach (WaterQnItem w in vlist.OrderBy(x => x.Percent))
{
double O = Math.Min(w.Limit / LIMIT_SCALE, sp_min) * 100 / w.Percent;
double max_bet = (T * Math.Pow(O * pq_win[i] - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * pq_win[i] * (1 - pq_win[i]));
if (bet_amount >= max_bet)
{
break;
}
double current_amount = Math.Min(max_bet - bet_amount, w.Amount);
current_amount = Math.Round(current_amount / QN_STEP) * QN_STEP;
if (current_amount > 0)
{
InvestRecordQn ir = new InvestRecordQn()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "BET",
Type = "Q",
HorseNo = horseNo,
Percent = w.Percent,
Amount = current_amount,
Limit = w.Limit,
Odds = sp_min,
Probility = pq_win[i],
FittingLoss = item.Odds.E
};
qn_records.Add(ir);
bet_amount += ir.Amount;
}
}
}
// For Eat
{
WaterQnList vlist = item.Waters.GetQnBetWater(horseNo).GetValuableWater(MIN_R, sp_max, pq_win[i]);
double eat_amount = 0;
foreach (WaterQnItem w in vlist.OrderByDescending(x => x.Percent))
{
double O = 1 + w.Percent / 100 / Math.Min(w.Limit / LIMIT_SCALE, sp_max);
double max_eat = (T * Math.Pow(O * (1 - pq_win[i]) - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * (1 - pq_win[i]) * pq_win[i]);
max_eat = max_eat / Math.Min(w.Limit / LIMIT_SCALE, sp_max);
if (eat_amount >= max_eat)
{
break;
}
double current_amount = Math.Min(max_eat - eat_amount, w.Amount);
current_amount = Math.Round(current_amount / QN_STEP) * QN_STEP;
if (current_amount > 0)
{
InvestRecordQn ir = new InvestRecordQn()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "EAT",
Type = "Q",
HorseNo = horseNo,
Percent = w.Percent,
Amount = current_amount,
Limit = w.Limit,
Odds = sp_max,
Probility = pq_win[i],
FittingLoss = item.Odds.E
};
qn_records.Add(ir);
eat_amount += ir.Amount;
}
}
}
}
if (pq_plc != null)
{
double sp_min = Math.Min(item.Odds.SpQp[horseNo], item2.Odds.SpQp[horseNo]);
double sp_max = Math.Max(item.Odds.SpQp[horseNo], item2.Odds.SpQp[horseNo]);
// For Bet
{
WaterQnList vlist = item.Waters.GetQpEatWater(horseNo).GetValuableWater(MIN_R, sp_min, pq_plc[i]);
double bet_amount = 0;
foreach (WaterQnItem w in vlist.OrderBy(x => x.Percent))
{
double O = Math.Min(w.Limit / LIMIT_SCALE, sp_min) * 100 / w.Percent;
double max_bet = (T * Math.Pow(O * pq_plc[i] - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * pq_plc[i] * (1 - pq_plc[i]));
if (bet_amount >= max_bet)
{
break;
}
double current_amount = Math.Min(max_bet - bet_amount, w.Amount);
current_amount = Math.Round(current_amount / QN_STEP) * QN_STEP;
if (current_amount > 0)
{
InvestRecordQn ir = new InvestRecordQn()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "BET",
Type = "QP",
HorseNo = horseNo,
Percent = w.Percent,
Amount = current_amount,
Limit = w.Limit,
Odds = sp_min,
Probility = pq_plc[i],
FittingLoss = item.Odds.E
};
qn_records.Add(ir);
bet_amount += ir.Amount;
}
}
}
// For Eat
{
WaterQnList vlist = item.Waters.GetQpBetWater(horseNo).GetValuableWater(MIN_R, sp_max, pq_win[i]);
double eat_amount = 0;
foreach (WaterQnItem w in vlist.OrderByDescending(x => x.Percent))
{
double O = 1 + w.Percent / 100 / Math.Min(w.Limit / LIMIT_SCALE, sp_max);
double max_eat = (T * Math.Pow(O * (1 - pq_plc[i]) - 1, 2)) / (LOSS_RATE_COEFFICIENT * Math.Pow(O, 2) * (1 - pq_plc[i]) * pq_plc[i]);
max_eat = max_eat / Math.Min(w.Limit / LIMIT_SCALE, sp_max);
if (eat_amount >= max_eat)
{
break;
}
double current_amount = Math.Min(max_eat - eat_amount, w.Amount);
current_amount = Math.Round(current_amount / QN_STEP) * QN_STEP;
if (current_amount > 0)
{
InvestRecordQn ir = new InvestRecordQn()
{
TimeKey = tp,
Model = MODEL,
CardID = race.CardID,
RaceNo = race.RaceNo,
Direction = "EAT",
Type = "QP",
HorseNo = horseNo,
Percent = w.Percent,
Amount = current_amount,
Limit = w.Limit,
Odds = sp_max,
Probility = pq_plc[i],
FittingLoss = item.Odds.E
};
qn_records.Add(ir);
eat_amount += ir.Amount;
}
}
}
}
}
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(@"
insert into sl_invest_wp (time_key,model,cd_id,rc_no,direction,hs_no,percent,w_limit,p_limit,rc_time,fitting_loss,w_amt,w_od,w_prob,p_amt,p_od,p_prob)
values (?time_key,?model,?cd_id,?rc_no,?direction,?hs_no,?percent,?w_limit,?p_limit,?rc_time,?fitting_loss,?w_amt,?w_od,?w_prob,?p_amt,?p_od,?p_prob)
on duplicate key update rc_time=?rc_time,fitting_loss=?fitting_loss,w_amt=?w_amt,w_od=?w_od,w_prob=?w_prob,p_amt=?p_amt,p_od=?p_od,p_prob=?p_prob,lmt=CURRENT_TIMESTAMP()
", conn))
{
cmd.Parameters.Add("?time_key", MySqlDbType.Int64);
cmd.Parameters.Add("?model", MySqlDbType.Int32);
cmd.Parameters.Add("?cd_id", MySqlDbType.UInt64);
cmd.Parameters.Add("?rc_no", MySqlDbType.Int32);
cmd.Parameters.Add("?direction", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("?hs_no", MySqlDbType.Int32);
cmd.Parameters.Add("?percent", MySqlDbType.Decimal);
cmd.Parameters.Add("?w_limit", MySqlDbType.Decimal);
cmd.Parameters.Add("?p_limit", MySqlDbType.Decimal);
cmd.Parameters.Add("?rc_time", MySqlDbType.DateTime);
cmd.Parameters.Add("?fitting_loss", MySqlDbType.Decimal);
cmd.Parameters.Add("?w_amt", MySqlDbType.Decimal);
cmd.Parameters.Add("?w_od", MySqlDbType.Decimal);
cmd.Parameters.Add("?w_prob", MySqlDbType.Decimal);
cmd.Parameters.Add("?p_amt", MySqlDbType.Decimal);
cmd.Parameters.Add("?p_od", MySqlDbType.Decimal);
cmd.Parameters.Add("?p_prob", MySqlDbType.Decimal);
foreach (InvestRecordWp ir in wp_records)
{
cmd.Parameters["?time_key"].Value = ir.TimeKey;
cmd.Parameters["?model"].Value = ir.Model;
cmd.Parameters["?cd_id"].Value = ir.CardID;
cmd.Parameters["?rc_no"].Value = ir.RaceNo;
cmd.Parameters["?direction"].Value = ir.Direction;
cmd.Parameters["?hs_no"].Value = int.Parse(ir.HorseNo);
cmd.Parameters["?percent"].Value = ir.Percent;
cmd.Parameters["?w_limit"].Value = ir.WinLimit;
cmd.Parameters["?p_limit"].Value = ir.PlcLimit;
cmd.Parameters["?rc_time"].Value = race.StartTime;
cmd.Parameters["?fitting_loss"].Value = ir.FittingLoss;
cmd.Parameters["?w_amt"].Value = ir.WinAmount;
cmd.Parameters["?w_od"].Value = ir.WinOdds;
cmd.Parameters["?w_prob"].Value = ir.WinProbility;
cmd.Parameters["?p_amt"].Value = ir.PlcAmount;
cmd.Parameters["?p_od"].Value = ir.PlcOdds;
cmd.Parameters["?p_prob"].Value = ir.PlcProbility;
cmd.ExecuteNonQuery();
}
}
using (MySqlCommand cmd = new MySqlCommand(@"
insert into sl_invest_qn(time_key,model,cd_id,rc_no,direction,q_type,hs_no,percent,q_limit,rc_time,fitting_loss,amt,od,prob)
values (?time_key,?model,?cd_id,?rc_no,?direction,?q_type,?hs_no,?percent,?q_limit,?rc_time,?fitting_loss,?amt,?od,?prob)
on duplicate key update rc_time=?rc_time,fitting_loss=?fitting_loss,amt=?amt,od=?od,prob=?prob,lmt=CURRENT_TIMESTAMP()
", conn))
{
cmd.Parameters.Add("?time_key", MySqlDbType.Int64);
cmd.Parameters.Add("?model", MySqlDbType.Int32);
cmd.Parameters.Add("?cd_id", MySqlDbType.UInt64);
cmd.Parameters.Add("?rc_no", MySqlDbType.Int32);
cmd.Parameters.Add("?direction", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("?q_type", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("?hs_no", MySqlDbType.VarChar, 20);
cmd.Parameters.Add("?percent", MySqlDbType.Decimal);
cmd.Parameters.Add("?q_limit", MySqlDbType.Decimal);
cmd.Parameters.Add("?rc_time", MySqlDbType.DateTime);
cmd.Parameters.Add("?fitting_loss", MySqlDbType.Decimal);
cmd.Parameters.Add("?amt", MySqlDbType.Decimal);
cmd.Parameters.Add("?od", MySqlDbType.Decimal);
cmd.Parameters.Add("?prob", MySqlDbType.Decimal);
foreach (InvestRecordQn ir in qn_records)
{
cmd.Parameters["?time_key"].Value = ir.TimeKey;
cmd.Parameters["?model"].Value = ir.Model;
cmd.Parameters["?cd_id"].Value = ir.CardID;
cmd.Parameters["?rc_no"].Value = ir.RaceNo;
cmd.Parameters["?direction"].Value = ir.Direction;
cmd.Parameters["?q_type"].Value = ir.Type;
cmd.Parameters["?hs_no"].Value = ir.HorseNo;
cmd.Parameters["?percent"].Value = ir.Percent;
cmd.Parameters["?q_limit"].Value = ir.Limit;
cmd.Parameters["?rc_time"].Value = race.StartTime;
cmd.Parameters["?fitting_loss"].Value = ir.FittingLoss;
cmd.Parameters["?amt"].Value = ir.Amount;
cmd.Parameters["?od"].Value = ir.Odds;
cmd.Parameters["?prob"].Value = ir.Probility;
cmd.ExecuteNonQuery();
}
}
}
}
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
}
}
}
}
}