public frmRptOdds(Game game,DataTable dtGameDetail)
{
InitializeComponent();
GameForRpt = game;
DtGameDetail = dtGameDetail;
reportViewer1.LocalReport.ReportPath = "rptOdds.rdlc";
}
public int InsertGameInfo(Game game, DataTable dtGameDetail)
{
SQLiteDBHelper sqliteHelper = new SQLiteDBHelper();
StringBuilder strSql = new StringBuilder();
//Hashtable sqlStringList = new Hashtable();
Dictionary<StringBuilder, SQLiteParameter[]> sqlStringList = new Dictionary<StringBuilder, SQLiteParameter[]>();
strSql = new StringBuilder();
strSql.Append("insert into game (");
strSql.Append("sn,league,home,visitor,gametime,score,result,finalSP)");
strSql.Append(" values (");
strSql.Append("@sn,@league,@home,@visitor,@gametime,@score,@result,@finalSP)");
SQLiteParameter[] parameters = {
sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32,game.SN),
sqliteHelper.MakeSQLiteParameter("@league", DbType.String,16,game.League),
sqliteHelper.MakeSQLiteParameter("@home", DbType.String,32,game.Home),
sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String,32,game.Visitor),
sqliteHelper.MakeSQLiteParameter("@gametime", DbType.DateTime,game.GameTime),
sqliteHelper.MakeSQLiteParameter("@score", DbType.String,game.Score),
sqliteHelper.MakeSQLiteParameter("@result", DbType.String,game.Result),
sqliteHelper.MakeSQLiteParameter("@finalSP", DbType.String,game.FinalSP)
};
sqlStringList.Add(strSql, parameters);
for (int i = 0; i <= dtGameDetail.Rows.Count - 1; i++)
{
strSql = new StringBuilder();
strSql.Append("insert into gamedetail (");
strSql.Append("gamesn,changetime,win,draw,lose,changeminute)");
strSql.Append(" values (");
strSql.Append("@gamesn,@changetime,@win,@draw,@lose,@changeminute)");
SQLiteParameter[] para = {
sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32,game.SN),
sqliteHelper.MakeSQLiteParameter("@changetime", DbType.DateTime,dtGameDetail.Rows[i][0]),
sqliteHelper.MakeSQLiteParameter("@win", DbType.Decimal,dtGameDetail.Rows[i][4]),
sqliteHelper.MakeSQLiteParameter("@draw", DbType.Decimal,dtGameDetail.Rows[i][5]),
sqliteHelper.MakeSQLiteParameter("@lose", DbType.Decimal,dtGameDetail.Rows[i][6]),
sqliteHelper.MakeSQLiteParameter("@changeminute", DbType.Int32,dtGameDetail.Rows[i][7])
};
sqlStringList.Add(strSql, para);
}
int result = sqliteHelper.ExecuteSqlTran(sqlStringList);
return result;
}
private void btnImport_Click(object sender, EventArgs e)
{
if (txtFilePath.Text.Equals(string.Empty))
{
MessageBox.Show("请先选择文件。");
return;
}
int sheetsCount = ExcelUtils.GetSheetsCount(txtFilePath.Text);
DataTable dtGame = new DataTable(); ;
DataTable dtGameDetail = new DataTable();
string errorMsg = string.Empty;
DAGame daGame = new DAGame();
Game myGame = new Game();
DataTable dtGameList = new DataTable();
dtGameList.Columns.Add("ID");
dtGameList.Columns.Add("NAME");
DataRow dr = dtGameList.NewRow();
for (int i = 0; i <= sheetsCount - 1; i++)
{
//比赛信息
dtGame = new DataTable();
dtGame.Columns.Add("League");
dtGame.Columns.Add("Home");
dtGame.Columns.Add("Visitor");
dtGame.Columns.Add("Gametime");
dtGame.Columns.Add("Score");
dtGame.Columns.Add("Result");
dtGame.Columns.Add("FinalSP");
//赔率信息
dtGameDetail = new DataTable();
dtGameDetail.Columns.Add("Changetime");
dtGameDetail.Columns.Add("B");
dtGameDetail.Columns.Add("C");
dtGameDetail.Columns.Add("D");
dtGameDetail.Columns.Add("Win");
dtGameDetail.Columns.Add("Draw");
dtGameDetail.Columns.Add("Lose");
dtGameDetail.Columns.Add("Changeminute");
//读取比赛信息
try
{
ExcelUtils.headRowCount = 1;
ExcelUtils.dataRowCount = 1;
ExcelUtils.dataColCount = 7;
ExcelUtils.dtExcel = dtGame;
ExcelUtils.indexDatatimeCol = "3,4";
dtGame = ExcelUtils.TranslateToTable(txtFilePath.Text, i);
}
catch
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
//读取赔率信息
try
{
ExcelUtils.headRowCount = 4;
ExcelUtils.dataRowCount = 0;
ExcelUtils.dataColCount = 8;
ExcelUtils.dtExcel = dtGameDetail;
ExcelUtils.indexDatatimeCol = "0";
dtGameDetail = ExcelUtils.TranslateToTable(txtFilePath.Text, i);
}
catch
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
myGame = new Game();
int result = daGame.GetMaxGameSN();
if (result == -1)
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
myGame.SN = result + 1;
myGame.League = dtGame.Rows[0]["League"].ToString();
myGame.Home = dtGame.Rows[0]["Home"].ToString();
myGame.Visitor = dtGame.Rows[0]["Visitor"].ToString();
myGame.GameTime = DateTime.Parse(dtGame.Rows[0]["Gametime"].ToString());
myGame.Score = dtGame.Rows[0]["Score"].ToString();
myGame.Result = dtGame.Rows[0]["Result"].ToString();
myGame.FinalSP = dtGame.Rows[0]["FinalSP"].ToString();
result = daGame.InsertGameInfo(myGame, dtGameDetail);
if (result == -1)
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
dr = dtGameList.NewRow();
dr[0] = myGame.SN;
dr[1] = (i + 1).ToString() + "," + myGame.League + "---" + myGame.Home + " VS " + myGame.Visitor;
dtGameList.Rows.Add(dr);
}
SetCmbGame(dtGameList);
if (!string.IsNullOrEmpty(errorMsg))
{
MessageBox.Show("第 "+errorMsg.TrimEnd('，')+" 个Sheet导入失败。");
}
}
private void Init()
{
try
{
DataTable dt = new DataTable();
DAGame daGame = new DAGame();
Game myGame = new Game();
int result = daGame.GetGameBySearch(dtpStart.Value, dtpEnd.Value, txtTeam.Text.Trim(), ref dt);
if (result == -1)
{
MessageBox.Show("查询失败，请重新运行本程序再试一次。");
return;
}
dgvGame.DataSource = dt;
dgvGame.Columns[0].HeaderText = "比赛时间";
dgvGame.Columns[0].Width = 150;
dgvGame.Columns[1].Width = 90;
dgvGame.Columns[2].Width = 110;
dgvGame.Columns[3].Width = 110;
dgvGame.Columns[1].HeaderText = "联赛";
dgvGame.Columns[2].HeaderText = "主队";
dgvGame.Columns[3].HeaderText = "客队";
dgvGame.Columns[4].Visible = false; ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
