// 获取多轮参加团队赛的运动员预赛成绩
public static List <AthleteSocreModel> GetTeamMatchAthletesScore(string matchId, string groupName, string sexName, int round)
{
var roundField = string.Empty;
if (round == 1)
{
roundField = "PRELIMINARIES_RESULTS_ONE";
}
else if (round == 2)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO";
}
else if (round == 3)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE";
}
else if (round == 4)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE, PRELIMINARIES_RESULTS_FOUR";
}
else if (round == 5)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE, PRELIMINARIES_RESULTS_FOUR, PRELIMINARIES_RESULTS_FIVE";
}
var result = new List <AthleteSocreModel>();
var sql = $"select name,team_name,team_short_name, {roundField} from athletes where " +
$"match_id = '{matchId}' and group_name='{groupName}' and sex = '{sexName}' and bar_GROUP_GAMES_STATUS ='参加'";
var dataSet = SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sql, CommandType.Text);
var rows = dataSet.Tables[0].Rows;
for (var i = 0; i < rows.Count; i++)
{
var score = new AthleteSocreModel();
score.Name = rows[i].ItemArray[0] != DBNull.Value ? (string)rows[i].ItemArray[0] : "";
score.TeamName = rows[i].ItemArray[1] != DBNull.Value ? (string)rows[i].ItemArray[1] : "";
score.TeamShortName = rows[i].ItemArray[2] != DBNull.Value ? (string)rows[i].ItemArray[2] : "";
score.AllScores = new List <double>();
score.ScoresWithRound = new Dictionary <int, List <double> >();
if (round == 1)
{
var oS = rows[i].ItemArray[3] != DBNull.Value ? (string)rows[i].ItemArray[3] : "";
var oneScores = oS.Split(';');
var key = 1;
var oneValue = new List <double>();
if (oneScores.Length > 1)
{
for (var j = 0; j < oneScores.Length; j++)
{
var s = 0.0;
double.TryParse(oneScores[j], out s);
oneValue.Add(s);
score.AllScores.Add(s);
score.SumSocre += s;
}
score.ScoresWithRound.Add(key, oneValue);
}
}
else if (round == 2)
{
var oS = rows[i].ItemArray[3] != DBNull.Value ? (string)rows[i].ItemArray[3] : "";
var twoS = rows[i].ItemArray[4] != DBNull.Value ? (string)rows[i].ItemArray[4] : "";
var oneScores = oS.Split(';');
var key1 = 1;
var oneValue = new List <double>();
if (oneScores.Length > 1)
{
for (var j = 0; j < oneScores.Length; j++)
{
var s = 0.0;
double.TryParse(oneScores[j], out s);
oneValue.Add(s);
score.AllScores.Add(s);
score.SumSocre += s;
}
score.ScoresWithRound.Add(key1, oneValue);
}
var twoScores = twoS.Split(';');
var key2 = 2;
var twoValue = new List <double>();
if (twoScores.Length > 1)
{
for (var j = 0; j < twoScores.Length; j++)
{
var s = 0.0;
double.TryParse(twoScores[j], out s);
twoValue.Add(s);
score.AllScores.Add(s);
score.SumSocre += s;
}
score.ScoresWithRound.Add(key2, twoValue);
}
}
else if (round == 3)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE";
}
else if (round == 4)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE, PRELIMINARIES_RESULTS_FOUR";
}
else if (round == 5)
{
roundField = "PRELIMINARIES_RESULTS_ONE, PRELIMINARIES_RESULTS_TWO, PRELIMINARIES_RESULTS_THREE, PRELIMINARIES_RESULTS_FOUR, PRELIMINARIES_RESULTS_FIVE";
}
result.Add(score);
}
return(result);
}
private static int SetAthleteScore(IWorkbook workbook, ISheet sheet, int startRow, AthleteSocreModel athlete, bool isQ)
{
var row = startRow;
var athRow = CreateRow(sheet, startRow);
SetCellValue(athRow, 0, athlete.TeamShortName);
SetCellValue(athRow, 1, athlete.Name);
for (var i = 2; i < 14; i++)
{
if (athlete.ScoresWithRound.Count < 1)
{
break;
}
if (athlete.ScoresWithRound.ContainsKey(1))
{
var value = athlete.ScoresWithRound[1].Skip(i - 2).Take(1).First().ToString().Trim();
var cell = SetCellValue(athRow, i, value, true);
if (value == "1") // 设置样式
{
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
}
}
for (var i = 15; i < 27; i++)
{
if (athlete.ScoresWithRound.ContainsKey(2))
{
var value = athlete.ScoresWithRound[2].Skip(i - 15).Take(1).First().ToString().Trim();
var cell = SetCellValue(athRow, i, value, true);
if (value == "1") // 设置样式
{
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
}
}
if (isQ)
{
SetCellValue(athRow, 29, "Q");
}
return(++row);
}
