/// <summary>
/// 增加一条数据
/// </summary>
public void Add(SeoWebSite.Model.Odds1x2History model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into odds1x2history(");
strSql.Append("scheduleid,companyid,home,draw,away,homep,drawp,awayp,returnrate,time)");
strSql.Append(" values (");
strSql.Append("@scheduleid,@companyid,@home,@draw,@away,@homep,@drawp,@awayp,@returnrate,@time)");
SqlParameter[] parameters =
{
new SqlParameter("@scheduleid", SqlDbType.Int),
new SqlParameter("@companyid", SqlDbType.Int),
new SqlParameter("@home", SqlDbType.Float),
new SqlParameter("@draw", SqlDbType.Float),
new SqlParameter("@away", SqlDbType.Float),
new SqlParameter("@homep", SqlDbType.Float),
new SqlParameter("@drawp", SqlDbType.Float),
new SqlParameter("@awayp", SqlDbType.Float),
new SqlParameter("@returnrate", SqlDbType.Float),
new SqlParameter("@time", SqlDbType.DateTime)
};
parameters[0].Value = model.scheduleid;
parameters[1].Value = model.companyid;
parameters[2].Value = model.home;
parameters[3].Value = model.draw;
parameters[4].Value = model.away;
parameters[5].Value = model.homep;
parameters[6].Value = model.drawp;
parameters[7].Value = model.awayp;
parameters[8].Value = model.returnrate;
parameters[9].Value = model.time;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 增加一条数据
/// </summary>
public string Add(string scheduleID, string companyids, string historyids,DateTime time)
{
WebClientBLL bll = new WebClientBLL();
string[] companyidArr = companyids.Split(',');
string[] historyidArr = historyids.Split(',');
int count = 0;
if (companyidArr.Length == historyidArr.Length)
{
dal.Delete(scheduleID);
for (int i = 0; i < companyidArr.Length; i++)
{
string s = bll.GetOddsHistoryContent(historyidArr[i]);
Lexer lexer = new Lexer(s);
Parser parser = new Parser(lexer);
NodeList bodyNodes = parser.Parse(new TagNameFilter("HTML"))[0].Children.ExtractAllNodesThatMatch(new TagNameFilter("BODY"))[0].Children;
ITag table = bodyNodes.SearchFor(typeof(Winista.Text.HtmlParser.Tags.TableTag))[0] as ITag;
NodeList tableRows = table.Children.SearchFor(typeof(Winista.Text.HtmlParser.Tags.TableRow));
for (int f = 0; f < tableRows.Count; f++)
{
ITag row = tableRows[f] as ITag;
if (row.Attributes["ALIGN"].Equals("center") && row.Attributes["BGCOLOR"].Equals("#FFFFFF")){
Odds1x2History model = new Odds1x2History();
model.companyid = int.Parse(companyidArr[i]);
model.scheduleid = int.Parse(scheduleID);
model.home = float.Parse(row.Children[0].ToPlainTextString());
model.draw = float.Parse(row.Children[1].ToPlainTextString());
model.away = float.Parse(row.Children[2].ToPlainTextString());
this.FillOdds1x2History(model);
string[] t2 = row.Children[3].ToPlainTextString().Replace("showtime(", "").Replace(")", "").Split(',');
int yy = int.Parse(t2[0]);
int mm = int.Parse(t2[1].Remove(2));
int dd = int.Parse(t2[2]);
int hh = int.Parse(t2[3]);
int mi = int.Parse(t2[4]);
int ss = int.Parse(t2[5]);
model.time = new DateTime(yy, mm, dd, hh, mi, ss, DateTimeKind.Utc).AddHours(8d);
if (model.time > time)
{
continue;
}
dal.Add(model);
count++;
}
}
}
}
JSONHelper json = new JSONHelper();
json.success = true;
json.totlalCount = count;
return json.ToString();
}
/// <summary>
/// 返回某场比赛某公司的赔率变化列表
/// </summary>
public List<Odds1x2History> GetList(string scheduleid, string[] companyids)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT id, scheduleid, companyid, home, draw, away, homep, drawp, awayp, returnrate, time FROM odds1x2history");
strSql.Append(" WHERE (companyid in (" + companyids + ")) AND (scheduleid = @scheduleid) ORDER BY time DESC");
SqlParameter[] parameters = {
new SqlParameter("@scheduleid", SqlDbType.Int)};
parameters[0].Value = scheduleid;
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
List<Odds1x2History> list = new List<Odds1x2History>();
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Odds1x2History model = new Odds1x2History();
model.id = (int)dr["id"];
model.scheduleid = (int)dr["scheduleid"];
model.companyid = (int)dr["companyid"];
model.home = float.Parse(dr["home"].ToString());
model.draw = float.Parse(dr["draw"].ToString());
model.away = float.Parse(dr["away"].ToString());
model.homep = float.Parse(dr["homep"].ToString());
model.drawp = float.Parse(dr["drawp"].ToString());
model.awayp = float.Parse(dr["awayp"].ToString());
model.returnrate = float.Parse(dr["returnrate"].ToString());
model.time = (DateTime)dr["time"];
list.Add(model);
}
return list;
}
else
{
return null;
}
}
/// <summary>
/// 返回某场比赛某时间前所选公司的赔率
/// </summary>
/// <param name="schedule"></param>
/// <param name="time"></param>
/// <returns></returns>
public List<Odds1x2History> GetLastListByCompanys(int scheduleid,string companyids, DateTime time)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT a.id, a.scheduleid, a.companyid, a.home, a.draw, a.away, a.homep, a.drawp, a.awayp, a.returnrate, a.time FROM odds1x2history AS a INNER JOIN (SELECT companyid, MAX(time) AS time FROM odds1x2history");
strSql.Append(" WHERE (time <= @time) AND (scheduleid = @scheduleid) GROUP BY companyid) AS b ON a.companyid = b.companyid AND a.companyid IN (" + companyids + ") AND a.time = b.time AND a.scheduleid = @scheduleid");
SqlParameter[] parameters = {
new SqlParameter("@time", SqlDbType.DateTime),
new SqlParameter("@scheduleid", SqlDbType.Int)};
parameters[0].Value = time;
parameters[1].Value = scheduleid;
List<Odds1x2History> list = new List<Odds1x2History>();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Odds1x2History model = new Odds1x2History();
model.id = (int)dr["id"];
model.scheduleid = (int)dr["scheduleid"];
model.companyid = (int)dr["companyid"];
model.home = float.Parse(dr["home"].ToString());
model.draw = float.Parse(dr["draw"].ToString());
model.away = float.Parse(dr["away"].ToString());
model.homep = float.Parse(dr["homep"].ToString());
model.drawp = float.Parse(dr["drawp"].ToString());
model.awayp = float.Parse(dr["awayp"].ToString());
model.returnrate = float.Parse(dr["returnrate"].ToString());
model.time = (DateTime)dr["time"];
list.Add(model);
}
}
return list;
}
private void FillOdds1x2Kelly(Odds1x2History model, string tcompanyid)
{
if (string.IsNullOrEmpty(tcompanyid))
{
ds = dal.GetAvgNumber(model.scheduleid, model.time);
model.homek = model.home * (float.Parse(ds.Tables[0].Rows[0]["avghomep"].ToString()));
model.drawk = model.draw * (float.Parse(ds.Tables[0].Rows[0]["avgdrawp"].ToString()));
model.awayk = model.away * (float.Parse(ds.Tables[0].Rows[0]["avgawayp"].ToString()));
}
else
{
ds = dal.GetCompanyLastOdds(model.scheduleid, int.Parse(tcompanyid), model.time);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
model.homek = model.home * float.Parse(dr["homep"].ToString());
model.drawk = model.draw * float.Parse(dr["drawp"].ToString());
model.awayk = model.away * float.Parse(dr["awayp"].ToString());
}
else
{
ds = dal.GetAvgNumber(model.scheduleid, model.time);
model.homek = model.home * (float.Parse(ds.Tables[0].Rows[0]["avghomep"].ToString()));
model.drawk = model.draw * (float.Parse(ds.Tables[0].Rows[0]["avgdrawp"].ToString()));
model.awayk = model.away * (float.Parse(ds.Tables[0].Rows[0]["avgawayp"].ToString()));
}
}
}
/// <summary>
/// 填充概率和返回率
/// </summary>
/// <param name="model"></param>
private void FillOdds1x2History(Odds1x2History model)
{
model.returnrate = 1 / ((1 / model.home) + (1 / model.draw) + (1 / model.away));
model.homep = 100 / (model.home / model.returnrate);
model.drawp = 100 / (model.draw / model.returnrate);
model.awayp = 100 / (model.away / model.returnrate);
}
public string GetOddsPointChartsData(string scheduleID, string[] companyids)
{
JSONHelper json = new JSONHelper();
json.success = true;
DataSet dsAllTime = dal.GetChangeTimesByCompanys(scheduleID, string.Join(",", companyids));
List<float> avehomeklist = new List<float>();
List<float> avedrawklist = new List<float>();
List<float> aveawayklist = new List<float>();
List<float> avehomevlist = new List<float>();
List<float> avedrawvlist = new List<float>();
List<float> aveawayvlist = new List<float>();
List<float> avereratelist = new List<float>();
Odds1x2History lastmodel = new Odds1x2History();
foreach (DataRow dr in dsAllTime.Tables[0].Rows)
{
ds = dal.GetAvgNumber(int.Parse(scheduleID), DateTime.Parse(dr[0].ToString()));
List<Odds1x2History> oddsList = dal.GetLastListByCompanys(int.Parse(scheduleID), string.Join(",", companyids), DateTime.Parse(dr["time"].ToString()));
List<float> homeklist = new List<float>();
List<float> drawklist = new List<float>();
List<float> awayklist = new List<float>();
List<float> reratelist = new List<float>();
foreach (Odds1x2History item in oddsList)
{
homeklist.Add(item.home * float.Parse(ds.Tables[0].Rows[0]["avghomep"].ToString()));
drawklist.Add(item.draw * float.Parse(ds.Tables[0].Rows[0]["avgdrawp"].ToString()));
awayklist.Add(item.away * float.Parse(ds.Tables[0].Rows[0]["avgawayp"].ToString()));
reratelist.Add(item.returnrate * 100);
}
avehomeklist.Add(homeklist.Average());
avedrawklist.Add(drawklist.Average());
aveawayklist.Add(awayklist.Average());
avehomevlist.Add(this.CalculationVariance(homeklist));
avedrawvlist.Add(this.CalculationVariance(drawklist));
aveawayvlist.Add(this.CalculationVariance(awayklist));
avereratelist.Add(reratelist.Average());
}
int h = 0, d = 0, a = 0;
int hh = 0, dd = 0, aa = 0;
for (int i = 1; i < avehomeklist.Count; i++)
{
if (avehomeklist[i] < avehomeklist[i - 1])
h++;
else if (avehomeklist[i] > avehomeklist[i - 1])
h--;
if (avedrawklist[i] < avedrawklist[i - 1])
d++;
else if (avedrawklist[i] > avedrawklist[i - 1])
d--;
if (aveawayklist[i] < aveawayklist[i - 1])
a++;
else if (aveawayklist[i] > aveawayklist[i - 1])
a--;
if (avehomevlist[i] <= avehomevlist[i - 1])
{
if (avehomeklist[i] < avehomeklist[i - 1])
hh++;
else if (avehomeklist[i] > avehomeklist[i - 1])
hh--;
}
if (avedrawvlist[i] <= avedrawvlist[i - 1])
{
if (avedrawklist[i] < avedrawklist[i - 1])
dd++;
else if (avedrawklist[i] > avedrawklist[i - 1])
dd--;
}
if (aveawayvlist[i] <= aveawayvlist[i - 1])
{
if (aveawayklist[i] < aveawayklist[i - 1])
aa++;
else if (aveawayklist[i] > aveawayklist[i - 1])
aa--;
}
}
json.AddItem("name", "主胜");
json.AddItem("point", h.ToString());
json.AddItem("xpoint", hh.ToString());
json.ItemOk();
json.AddItem("name", "平局");
json.AddItem("point", d.ToString());
json.AddItem("xpoint", dd.ToString());
json.ItemOk();
json.AddItem("name", "客胜");
json.AddItem("point", a.ToString());
json.AddItem("xpoint", aa.ToString());
json.ItemOk();
json.totlalCount = 3;
return json.ToString();
}
public string GetOddsCompanyChartsData(string scheduleID, string[] companyids, double time)
{
JSONHelper json = new JSONHelper();
json.success = true;
DataSet dsStart = dal.GetCompanyStartPoint(scheduleID, companyids);
DataSet dsEnd = dal.GetCompanyEndPoint(scheduleID, companyids);
DateTime firsttime;
DateTime lasttime = DateTime.Parse(dsEnd.Tables[0].Rows[0][0].ToString());
if (time == 0)
{
firsttime = DateTime.Parse(dsStart.Tables[0].Rows[0][0].ToString());
}
else
{
firsttime = lasttime.AddSeconds(0 - time);
}
double timespace = (lasttime - firsttime).TotalSeconds / 60;
while (firsttime <= lasttime)
{
Odds1x2History model = new Odds1x2History();
ds = dal.GetAvgNumber(int.Parse(scheduleID), firsttime);
DataRow dr = ds.Tables[0].Rows[0];
List<Odds1x2History> oddsList = dal.GetLastListByCompanys(int.Parse(scheduleID), string.Join(",", companyids), firsttime);
int home = 0;
int draw = 0;
int away = 0;
int nhome = 0;
int ndraw = 0;
int naway = 0;
int count = 0;
foreach (Odds1x2History item in oddsList)
{
item.homek = item.home * float.Parse(dr["avghomep"].ToString());
item.drawk = item.draw * float.Parse(dr["avgdrawp"].ToString());
item.awayk = item.away * float.Parse(dr["avgawayp"].ToString());
if (item.homek < item.returnrate * 100 - 2)
{
home++;
}
else if (item.homek >= item.returnrate * 100 + 2)
{
nhome--;
}
if (item.drawk < item.returnrate * 100 - 2)
{
draw++;
}
else if (item.drawk >= item.returnrate * 100 + 2)
{
ndraw--;
}
if (item.awayk < item.returnrate * 100 - 2)
{
away++;
}
else if (item.awayk >= item.returnrate * 100 + 2)
{
naway--;
}
count++;
}
json.AddItem("home", home.ToString());
json.AddItem("draw", draw.ToString());
json.AddItem("away", away.ToString());
json.AddItem("nhome", nhome.ToString());
json.AddItem("ndraw", ndraw.ToString());
json.AddItem("naway", naway.ToString());
json.AddItem("count", count.ToString());
json.AddItem("time", firsttime.ToString());
json.ItemOk();
firsttime = firsttime.AddSeconds(timespace);
}
json.totlalCount = ds.Tables[0].Rows.Count;
return json.ToString();
}
public string GetOdds1x2ChartsData(string scheduleID, string[] companyids, double time)
{
DataSet dsStart = dal.GetCompanyStartPoint(scheduleID, companyids);
DataSet dsEnd = dal.GetCompanyEndPoint(scheduleID, companyids);
DateTime firsttime;
DateTime lasttime = DateTime.Parse(dsEnd.Tables[0].Rows[0][0].ToString());
if (time == 0)
{
firsttime = DateTime.Parse(dsStart.Tables[0].Rows[0][0].ToString());
}
else
{
firsttime = lasttime.AddSeconds(0 - time);
}
if (firsttime < DateTime.Parse(dsStart.Tables[0].Rows[0][0].ToString()))
{
firsttime = DateTime.Parse(dsStart.Tables[0].Rows[0][0].ToString());
}
double timespace = (lasttime - firsttime).TotalSeconds / 30;
int index = 0;
Odds1x2History firthmodel = new Odds1x2History();
JArray data = new JArray();
while (firsttime < lasttime)
{
ds = dal.GetAveNumByCompanys(scheduleID, string.Join(",", companyids), firsttime);
if (ds.Tables[0].Rows.Count == 1)
{
JObject item = new JObject();
DataRow dr = ds.Tables[0].Rows[0];
Odds1x2History model = new Odds1x2History();
model.home = float.Parse(dr["avghome"].ToString());
model.draw = float.Parse(dr["avgdraw"].ToString());
model.away = float.Parse(dr["avgaway"].ToString());
if (index == 0)
{
firthmodel = model;
}
item.Add("home", (model.home - firthmodel.home) * 100);
item.Add("draw", (model.draw - firthmodel.draw) * 100);
item.Add("away", (model.away - firthmodel.away) * 100);
item.Add("time", firsttime.ToString());
data.Add(item);
firsttime = firsttime.AddSeconds(timespace);
index++;
}
}
JObject result = new JObject();
result.Add(new JProperty("totlalCount", data.Count));
result.Add(new JProperty("data", data));
return result.ToString();
}
/// <summary>
/// 获得平均凯利线性图形
/// </summary>
/// <param name="scheduleID"></param>
/// <param name="companyids"></param>
/// <param name="time"></param>
/// <returns></returns>
public string GetKellyAverageLineChart(string scheduleID, string[] companyids, double time,bool isRefresh)
{
string jsonStr = "";
BetExpBLL bll = new BetExpBLL();
if (bll.Exists(int.Parse(scheduleID)) && !isRefresh)
{
BetExp betExp = bll.GetModel(int.Parse(scheduleID));
jsonStr = betExp.data;
}
else
{
if (companyids == null || companyids.Length == 0)
{
companyids = dal.getLastCompanyIDs(scheduleID, 1);
}
DataSet dsStart = dal.GetCompanyStartPoint(scheduleID, companyids);
DataSet dsEnd = dal.GetCompanyEndPoint(scheduleID, companyids);
DateTime firsttime = DateTime.Parse(dsStart.Tables[0].Rows[0][0].ToString());
DateTime lasttime = DateTime.Parse(dsEnd.Tables[0].Rows[0][0].ToString());
DateTime firsttime1 = lasttime.AddSeconds(-time);
if (firsttime1 > firsttime)
{
firsttime = firsttime1;
}
float inithome = 0;
float initdraw = 0;
float initaway = 0;
float initrate = 0;
JsonSerializer serializer = GetJsonSerializer();
double timespace = lasttime.Subtract(firsttime).TotalSeconds / 30;
Odds1x2History firthmodel = new Odds1x2History();
JArray data = new JArray();
while (firsttime <= lasttime)
{
ds = dal.GetAveNumByCompanys(scheduleID, string.Join(",", companyids), firsttime);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
List<float> homeKelly = new List<float>();
List<float> drawKelly = new List<float>();
List<float> awayKelly = new List<float>();
List<float> returnrateKelly = new List<float>();
List<Odds1x2History> oddsList = dal.GetLastListByCompanys(int.Parse(scheduleID), string.Join(",", companyids), firsttime);
foreach (Odds1x2History item in oddsList)
{
homeKelly.Add(item.home * float.Parse(dr["avghomep"].ToString()));
drawKelly.Add(item.draw * float.Parse(dr["avgdrawp"].ToString()));
awayKelly.Add(item.away * float.Parse(dr["avgawayp"].ToString()));
returnrateKelly.Add(item.returnrate * 100);
}
JObject result = new JObject();
if (data.Count <= 0)
{
inithome = homeKelly.Average();
initdraw = drawKelly.Average();
initaway = awayKelly.Average();
initrate = returnrateKelly.Average();
}
result.Add("avehome", (homeKelly.Average() - inithome) * 10);
result.Add("avedraw", (drawKelly.Average() - initdraw) * 10);
result.Add("aveaway", (awayKelly.Average() - initaway) * 10);
result.Add("returnrate", (returnrateKelly.Average() - initrate) * 10);
result.Add("varhome", this.CalculationVariance(homeKelly));
result.Add("vardraw", this.CalculationVariance(drawKelly));
result.Add("varaway", this.CalculationVariance(awayKelly));
result.Add("time", JProperty.FromObject(firsttime, serializer));
data.Add(result);
firsttime = firsttime.AddSeconds(timespace);
}
}
jsonStr = data.ToString();
bll.Delete(int.Parse(scheduleID));
string[] valueArr = new string[data.Count];
for (int i = 0; i < data.Count; i++)
{
valueArr[i] = bll.GetTrendsValue(JObject.Parse(data[data.Count - (data.Count - i)].ToString()),"");
}
bll.Add(new BetExp(int.Parse(scheduleID), jsonStr, string.Join(",", valueArr),"*" + bll.GetChangesValue(data,""), false));
}
return jsonStr;
}
/// <summary>
/// 返回某场比赛某时间前所选公司的赔率
/// </summary>
/// <param name="schedule"></param>
/// <param name="time"></param>
/// <returns></returns>
public List<Odds1x2History> GetLastListByCompanys(int scheduleid, string companyids, DateTime time)
{
StringBuilder strSql = new StringBuilder();
//select AVG(home) avghome, AVG(draw) avgdraw, AVG(away) avgaway,AVG(homep) avghomep, AVG(drawp) avgdrawp, AVG(awayp) avgawayp from odds1x2history a where ID=(select min(ID) from odds1x2history where companyid=a.companyid and (time <= '2010/9/12 14:58:00') AND (scheduleid = '376366') and companyid IN (474,499,517,18,545,81,659,676,660,594,601,582,531,456,466,436,124,64,658,32,33))
strSql.Append("SELECT a.id, a.scheduleid, a.companyid, a.home, a.draw, a.away, a.homep, a.drawp, a.awayp, a.returnrate, a.time");
strSql.Append(" FROM odds1x2history a where ID=(select min(ID) from odds1x2history");
strSql.Append(" WHERE (time <= @time) AND (scheduleid = @scheduleid) AND companyid IN (" + companyids + ") AND companyid=a.companyid)");
SqlParameter[] parameters = {
new SqlParameter("@time", SqlDbType.DateTime),
new SqlParameter("@scheduleid", SqlDbType.Int)};
parameters[0].Value = time;
parameters[1].Value = scheduleid;
List<Odds1x2History> list = new List<Odds1x2History>();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Odds1x2History model = new Odds1x2History();
model.id = (int)dr["id"];
model.scheduleid = (int)dr["scheduleid"];
model.companyid = (int)dr["companyid"];
model.home = float.Parse(dr["home"].ToString());
model.draw = float.Parse(dr["draw"].ToString());
model.away = float.Parse(dr["away"].ToString());
model.homep = float.Parse(dr["homep"].ToString());
model.drawp = float.Parse(dr["drawp"].ToString());
model.awayp = float.Parse(dr["awayp"].ToString());
model.returnrate = float.Parse(dr["returnrate"].ToString());
model.time = (DateTime)dr["time"];
list.Add(model);
}
}
return list;
}