public void top20Algorithm()
{
using ( SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
int i = 0;
foreach (var id in idExc)
{
i++;
ProgressBarDelegate.DoSendPMessage(i);
Application.DoEvents();
RowNumberLimit r = new RowNumberLimit(id);
// match_analysis_result mar = new match_analysis_result();
MatchAnalysisResult mar = new MatchAnalysisResult();
mar.LiveTableLibID= r.id;
mar.PreAlgorithm = "top20";
mar.PreMatchCount = r.Top20Count;
mar.HomeGoals =(float) r.HomeGoals;
mar.AwayGoals = (float)r.AwayGoals;
mar.HomeW = r.hWin;
mar.HomeD = r.hDraw;
mar.HomeL = r.hLose;
mar.FitWinLoss = (float)r.CureFitWinLoss;
mar.FitGoals = (float) r.CureFitGoals;
mar.FitOddEven = (float)r.CureFitOddEven;
matches.MatchAnalysisResult.InsertOnSubmit(mar);
}
matches.SubmitChanges();
}
}
public decimal updateLiveAibo()
{
DataTable dt = AiboHtml.GetTableOutHtml();
using (SoccerScoreSqlite match = new SoccerScoreSqlite(Conn.cnn))
{
var ll = match.LiveAibo.Where(e => e.LiveAiboID> 0);
match.LiveAibo.DeleteAllOnSubmit(ll);//更新后删除
match.SubmitChanges();
foreach (DataRow aa in dt.Rows)
{
if (HtmlTextToStr(aa[3].ToString()) != null) // && HtmlTextToStr(aa[11].ToString()) == null
{
LiveAibo lo = new LiveAibo();
lo.Value = HtmlTextToStr(aa[1].ToString());
lo.LeagueName = HtmlTextToStr(aa[2].ToString());
lo.MatchTime = HtmlTextToStr(aa[3].ToString());
lo.MatchOrder1hOmeName = AiboTeamName(aa[5].ToString());
lo.MatchOrder1hAndicapNumber= HtmlTextToStr(aa[6].ToString());
lo.MatchOrder1aWayName = AiboTeamName(aa[7].ToString());
match.LiveAibo.InsertOnSubmit(lo);
}
}
match.SubmitChanges();
return match.LiveAibo.Select(e => e.LiveAiboID).Max();
}
}
public decimal updateLiveOkoo()
{
DataTable dt = OkooHtml.GetTableOutHtml();
using (SoccerScoreSqlite match = new SoccerScoreSqlite(Conn.cnn))
//using (DataClassesMatchDataContext match = new DataClassesMatchDataContext())
{
var ll = match.LiveOkOO.Where(e => e.LiveOkOOID > 0);
match.LiveOkOO.DeleteAllOnSubmit(ll);//更新后删除
match.SubmitChanges();
foreach (DataRow aa in dt.Rows)
{
if (HtmlTextToStr(aa[15].ToString()) != null)
{
LiveOkOO lo = new LiveOkOO();
lo.Value = HtmlValueToInt(aa[0].ToString());
lo.LeagueName = HtmlTextToStr(aa[1].ToString());
lo.MatchTime = HtmlTextToStr(aa[2].ToString());
lo.MatchOrder1hOmeName = TeamName(aa[3].ToString());
lo.MatchOrder1hAndicapNumber = HtmlTextToStr(aa[4].ToString());
lo.MatchOrder1aWayName = TeamName(aa[5].ToString());
lo.Ok10 = HtmlTextToStr(aa[6].ToString());
lo.Ok11 = HtmlTextToStr(aa[7].ToString());
lo.Ok12 = HtmlTextToStr(aa[8].ToString());
lo.MatchInfo = HtmlTextToStr(aa[11].ToString());
lo.Match1wIn = HtmlTextToStr(aa[12].ToString());
lo.Match1dRawn = HtmlTextToStr(aa[13].ToString());
lo.Match1lOst = HtmlTextToStr(aa[14].ToString());
match.LiveOkOO.InsertOnSubmit(lo);
match.SubmitChanges();
}
}
return match.LiveOkOO.Select(e => e.LiveOkOOID).Max();
}
}
public ForecastAlgorithm()
{
using ( SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
IEnumerable<int> idLive = matches.LiveTableLib.Select(e => e.LiveTableLibID);
IEnumerable<int> idAnalysis = matches.MatchAnalysisResult.Select(e => (int)e.LiveTableLibID);
idExc = idLive.Except(idAnalysis).ToList(); //except序列A有的元素序列B没有
}
}
//private DateTime? todaytime;
public AuditForecastAlgorithm(int daysDiff)
{
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
//todaytime = DateTime.Now;
IEnumerable<int> idLive = matches.LiveTableLib
.Where(e => e.MatchTime.Value.Date >= DateTime.Now.AddDays(daysDiff).Date)
.Select(e => e.LiveTableLibID);
idExc = idLive.ToList();
}
}
public void initTreeNode(int daysDiff)
{
//这个连接不能放到class中，不然取的还是缓存的数据？？？？？？？？？？？
//对象和数据库之间会存在不能更新的问题？？？？？？？？？？？
//DataClassesMatchDataContext matches = new DataClassesMatchDataContext();
DateTime dt = DateTime.Now.AddDays(daysDiff).Date;
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
ltlAll = matches.LiveTableLib.Where(m => m.MatchTime.Value.Date >= dt).OrderBy(m => m.MatchTime).ToList();
rtlAll = matches.ResultTBLib.Where(m => m.MatchTime.Value.Date >= dt).ToList();
marAll = matches.MatchAnalysisResult.Where(e => e.LiveTableLibID > 0).ToList();
loAll = matches.LiveAibo .Where(e => e.LiveAiboID > 0).ToList();
}
}
private void init_table_id_value()
{
using (SoccerScoreSqlite match = new SoccerScoreSqlite(Conn.cnn))
{
var uri = match.MatchTableXPath.Where(e => e.UriHost == uri_host).FirstOrDefault();
if (uri.MaXTableIDValue.Length > 1)
table_id_value = uri.MaXTableIDValue;
else
{
if (uri.SecondTableIDValue.Length > 1)
table_id_value = uri.SecondTableIDValue;
else
table_id_value = uri.MaXTableXPath;
}
}
}
public decimal InsertLastHtmlTableToDB()
{
DataTable dt = SevenmResult.GetHtmlTable();
//DataClassesMatchDataContext match = new DataClassesMatchDataContext();
SoccerScoreSqlite match = new SoccerScoreSqlite(Conn.cnn);
foreach (DataRow aa in dt.Rows)
{
if (HtmlTextToStr(aa[3].ToString()) != null)
{
ResultTB rt = new ResultTB();
rt.HTMLPosition = aa[0].ToString();
rt.MatchType = HtmlTextToStr(aa[1].ToString());
rt.STime= HtmlTextToStr(aa[2].ToString());
rt.HomeTeam = HtmlTextToStr(aa[3].ToString());
rt.FullTimeScore = HtmlTextToStr(aa[4].ToString());
rt.AwayTeam = HtmlTextToStr(aa[5].ToString());
rt.HalfTimeScore = HtmlTextToStr(aa[6].ToString());
rt.Odds = HtmlTextToStr(aa[7].ToString());
rt.WinLossBig = HtmlTextToStr(aa[8].ToString());
rt.SDate = HtmlDateToStrResult(aa[2].ToString());
rt.HomeTeamBig = HtmlHrefToStr(aa[3].ToString());
rt.AwayTeamBig = HtmlHrefToStr(aa[5].ToString());
match.ResultTB.InsertOnSubmit(rt);
match.SubmitChanges();
}
}
return match.ResultTB.Select(e => e.ResultTBID).Max();
}
private void Form1_Load(object sender, EventArgs ee)
{
DataContext dc = new DataContext(Conn.cnn);
dataGridView5.Visible = false;
toolStripStatusLabel2.Text = dateTimePicker2.Value.ToString("yyyy-MM-dd");//日历组建日期字符串格式化方法
//using (DataContext matches = new DataContext(Conn.cnn))
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
DateTime? resultmaxtime = matches.ResultTBLib.Select(e => e.MatchTime).Max();
toolStripStatusLabel3.Text = resultmaxtime.Value.ToString();
DateTime? maxtime = matches.LiveTableLib.Select(e => e.MatchTime).Max();
toolStripStatusLabel4.Text = maxtime.HasValue ? maxtime.Value.ToString() : null;
}
treeView5.Nodes.Clear();
loaddatatree.TreeViewMatch(treeView5, "type");
dateTimePicker1.Value = DateTime.Parse(toolStripStatusLabel3.Text);
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
string sql = @"
if exists (select 1
from sysobjects
where id = object_id('match_analysis_result')
and type = 'U')
drop table match_analysis_result
;
CREATE TABLE [dbo].[match_analysis_result](
[analysis_result_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[live_table_lib_id] [decimal] NOT NULL,
[result_tb_lib_id] [decimal] NULL,
[pre_algorithm] [nvarchar](20) NULL,
[pre_match_count] [int] NULL,
[home_w] [int] NULL,
[home_d] [int] NULL,
[home_l] [int] NULL,
[home_goals] [float] NULL,
[away_goals] [float] NULL,
[fit_win_loss] [float] NULL,
[fit_goals] [float] NULL,
[fit_odd_even] [float] NULL,
CONSTRAINT [PK_MATCH_ANALYSIS_RESULT] PRIMARY KEY NONCLUSTERED
(
[analysis_result_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]";
//DataClassesMatchDataContext matches = new DataClassesMatchDataContext();
SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn);
DialogResult result; //Messagebox所属于的类
result = MessageBox.Show(this, "YesOrNo", "你确定要删除分析库？", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)//Messagebox返回的值
{
matches.ExecuteCommand(sql);
MessageBox.Show("OK");
}
}
private void button9_Click_1(object sender, EventArgs c)
{
Uri uri = new Uri(textBox3.Text);
string host = uri.Host;
using (SoccerScoreSqlite match = new SoccerScoreSqlite(Conn.cnn))
{
var eUri = match.MatchTableXPath.Where(e => e.UriHost == host).FirstOrDefault();
if (eUri == null)
{
MatchTableXPath nUri = new MatchTableXPath();
nUri.UriHost = host.ToString();
nUri.MaXTableXPath = textBox4.Text;
nUri.SecondTableXPath = textBox6.Text;
nUri.MaXTableIDValue = textBox7.Text;
nUri.SecondTableIDValue = textBox8.Text;
match.MatchTableXPath.InsertOnSubmit(nUri);
}
else
{
eUri.UriHost = host.ToString();
eUri.MaXTableXPath = textBox4.Text;
eUri.SecondTableXPath = textBox6.Text;
eUri.SecondTableIDValue = textBox7.Text;
eUri.SecondTableIDValue = textBox8.Text;
//match.match_table_xpath.InsertOnSubmit(eUri);
}
match.SubmitChanges();
}
MessageBox.Show("OK");
}
private void treeView5_AfterSelect(object sender, TreeViewEventArgs c)
{
if (c.Node.Level == 1)
{
dataGridView5.Visible = true;
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
var mar = from a in matches.MatchAnalysisResult
join b in matches.LiveTableLib on a.LiveTableLibID equals b.LiveTableLibID
select new
{
a.ResultWDL,
a.ResultFit,
a.ResultGoals,
b.MatchType
};
var winrate = from p in mar
where p.MatchType == c.Node.Text
group p by p.MatchType into q
select new
{
q.Key,
fitW = q.Where(e => e.ResultFit == "W").Count(),
fitL = q.Where(e => e.ResultFit == "L").Count(),
goalsW = q.Where(e => e.ResultGoals == "W").Count(),
goalsL = q.Where(e => e.ResultGoals == "L").Count(),
wdlW = q.Where(e => e.ResultWDL == "W").Count(),
wdlL = q.Where(e => e.ResultWDL == "L").Count(),
};
dataGridView5.DataSource = winrate;
var maxwin = winrate.FirstOrDefault();
int[] maxw = { maxwin.fitW, maxwin.fitL, maxwin.goalsW, maxwin.goalsL, maxwin.wdlW, maxwin.wdlL };
label3.Text = maxw.Max().ToString();
}
}
if (c.Node.Level != 2) { return; }
dataGridView5.Visible = false;
string selectMatch = c.Node.Text.ToString();
string[] ar = selectMatch.Split(Convert.ToChar(','));
int id = Int32.Parse(ar[0].ToString());
label3.Text = LoadDataToChart.ForeCast(chart1, id, selectMatch);
LoadDataToChart.LabelMatchDetail(chart1, PointLabelsList.GetItemText(PointLabelsList.SelectedItem));
}
private void button30_Click(object sender, EventArgs e)
{
SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn);
//DataClassesMatchDataContext matches = new DataClassesMatchDataContext();
dataGridView1.DataSource = matches.ResultTBLib;
}
private void button29_Click(object sender, EventArgs e)
{
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
dataGridView1.DataSource = matches.LiveTableLib;
}
}
private void toolStripButton2_Click(object sender, EventArgs ee)
{
MessageBox.Show(ViewMatchOverDays.ToString());
AuditForecastAlgorithm f = new AuditForecastAlgorithm(ViewMatchOverDays);
int pb = f.idExc.Count();
MessageBox.Show(pb.ToString());
if (pb != 0)
{
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
dMatch.dHome = matches.ResultTBLib.ToLookup(e => e.HomeTeamBig);
dMatch.dAway = matches.ResultTBLib.ToLookup(e => e.AwayTeamBig);
}
toolStripProgressBar1.Maximum = pb;
f.top20Algorithm();
}
}
private void toolStripButton_iniLast_Click(object sender, EventArgs e)
{
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
int pb = matches.ResultTB.Count();
toolStripProgressBar1.Maximum = pb;
MessageBox.Show(pb.ToString());
}
SevenmResultToSql sevenm = new SevenmResultToSql();
sevenm.UpdateLastMatch();
}
private void button3_Click(object sender, EventArgs c)
{
using (SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn))
{
var mar = from a in matches.MatchAnalysisResult
join b in matches.LiveTableLib on a.LiveTableLibID equals b.LiveTableLibID
select new
{
a.ResultWDL,
a.ResultFit,
a.ResultGoals,
b.MatchType
};
var winrate = from p in mar
group p by p.MatchType into q
select new
{
q.Key,
fitW = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultFit == "W").Count(),
fitL = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultFit == "L").Count(),
goalsW = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultGoals == "W").Count(),
goalsL = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultGoals == "L").Count(),
wdlW = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultWDL == "W").Count(),
wdlL = q.Where(e => e.MatchType == q.Key).Where(e => e.ResultWDL == "L").Count(),
};
dataGridView1.DataSource = winrate;
}
}
public void UpdateLastMatch()
{
int i = 0;
// DataClassesMatchDataContext matches = new DataClassesMatchDataContext();
SoccerScoreSqlite matches = new SoccerScoreSqlite(Conn.cnn);
IEnumerable<ResultTB> rt = matches.ResultTB.OrderBy(o => o.SDate).ThenBy(p => p.STime);//用lambda表达式简洁
//取临时变量监视
DateTime lib_max_match_time = matches.ResultTBLib.Select(p => p.MatchTime).Max().Value.AddDays(-2);
foreach (var m in rt)
{
i++;
ProgressBarDelegate.DoSendPMessage(i);
Application.DoEvents();
if (m.HomeTeamBig != null)
{
ResultTBLib rtl = new ResultTBLib();
rtl.HTMLPosition = Int32.Parse(m.HTMLPosition);
rtl.HomeTeamBig= Int32.Parse(GetNumber(m.HomeTeamBig));
rtl.AwayTeamBig = Int32.Parse(GetNumber(m.AwayTeamBig));
rtl.MatchType= m.MatchType.Trim();
last_line = m.STime.LastIndexOf("\n");
temp_time=m.STime.Substring(last_line,m.STime.Length-last_line-1);
rtl.MatchTime = DateTime.Parse(m.SDate.Substring(0, 10) + " " + temp_time);
rtl.Odds= m.Odds.Trim();
rtl.WinLossBig = m.WinLossBig.Trim();
rtl.HomeTeam = m.HomeTeam.Trim();
rtl.AwayTeam= m.AwayTeam.Trim();
rtl.HomeRedCard= StringCount(m.HomeTeam, " ", 0);
rtl.AwayRedCard = StringCount(m.AwayTeam, " ", 0);
string bf = m.FullTimeScore.Replace(" ", "").Replace(" ", "");
if (m.FullTimeScore.IndexOf("-") > 0)
{
rtl.FullHomeGoals= Int32.Parse(bf.Substring(0, bf.IndexOf("-")));
rtl.FullAwayGoals= Int32.Parse(bf.Substring(bf.IndexOf("-") + 1, bf.Length - bf.IndexOf("-") - 1));
}
if (m.HalfTimeScore.IndexOf("-") > 0)
{
rtl.HalfHomeGoals= Int32.Parse(m.HalfTimeScore.Substring(0, m.HalfTimeScore.IndexOf("-")));
rtl.HalfAwayGoals = Int32.Parse(m.HalfTimeScore.Substring(m.HalfTimeScore.IndexOf("-") + 1, m.HalfTimeScore.Length - m.HalfTimeScore.IndexOf("-") - 1));
}
//如果库中文件的日期太小，直接删除
if (rtl.MatchTime > lib_max_match_time)
{
//数据分区，层次化查询
IEnumerable<ResultTBLib> rtExist = from p in matches.ResultTBLib
where p.MatchTime== rtl.MatchTime
where p.HomeTeamBig== rtl.HomeTeamBig
where p.AwayTeamBig== rtl.AwayTeamBig
select p;
//库中没有记录直接插入
if (!rtExist.Any())
{
matches.ResultTBLib.InsertOnSubmit(rtl);
matches.SubmitChanges();
}
}
//更新后删除
matches.ResultTB.DeleteOnSubmit(m);
matches.SubmitChanges();
}
}
MessageBox.Show("OK");
//dataGridView1.DataSource = matches.result_tb_lib;
}