private static TeamMemberCountModel GetTeamMemberCountByTeamNameAndMatchId(string matchId, string teamNameAndGroupName)
{
var result = new TeamMemberCountModel();
var teamName = teamNameAndGroupName.Split(';')[0];
var groupName = teamNameAndGroupName.Split(';')[1];
var teamShortName = teamNameAndGroupName.Split(';')[2];
result.TeamName = teamName;
result.GroupName = groupName;
result.TeamShortName = teamShortName;
var sql = $" select * from (select count(*) as MenAthletesCount from athletes where " +
$"match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}' and sex = '男') " +
$" union all " +
$"select * from (select count(*) as WomenAthletesCount from athletes where " +
$"match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}' and sex = '女')";
var dataSet = SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sql, CommandType.Text);
var rows = dataSet.Tables[0].Rows;
result.MenAthletesCount = rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(rows[0].ItemArray[0]) : 0;
result.WomenAthletesCount = rows[1].ItemArray[0] != DBNull.Value ? Convert.ToInt32(rows[1].ItemArray[0]) : 0;
var sqlSearchLeaderCoach = $"select count(*) from others where (title='教练' or title='领队') and match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}'";
var dataSetLeaderCoach = SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sqlSearchLeaderCoach, CommandType.Text);
var countRows = dataSetLeaderCoach.Tables[0].Rows;
var count = DBHelper.GetTableItemNumberValue(countRows, 0, 0);
result.LeaderCoachCount = count;
return(result);
}
private static int SetEveyTeamCountInfoFooter(IWorkbook workbook, ISheet sheet, int startRow, TeamMemberCountModel team, List <TeamMemberCountModel> teams)
{
var row = startRow;
sheet.AddMergedRegion(new CellRangeAddress(row, row, 1, 2));
// 设置小计
var countRow = sheet.CreateRow(row);
var lableCell = countRow.CreateCell(0);
lableCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
lableCell.SetCellValue("小计");
var teamCountCell = countRow.CreateCell(1);
teamCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
teamCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Count()}");
var manCountCell = countRow.CreateCell(3);
manCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
manCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum()}");
var womenCountCell = countRow.CreateCell(4);
womenCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
womenCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum()}");
var leaderCountCell = countRow.CreateCell(5);
leaderCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
leaderCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}");
row++;
// 设置总计
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(row, row, 3, 4));
var allcountRow = sheet.CreateRow(row);
var alllableCell = allcountRow.CreateCell(0);
alllableCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
alllableCell.SetCellValue("总计");
var allSexCountCell = allcountRow.CreateCell(3);
allSexCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
allSexCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum()}");
var allleaderCountCell = allcountRow.CreateCell(5);
allleaderCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
allleaderCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}");
var alllCountCell = allcountRow.CreateCell(6);
alllCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
alllCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}");
return(row + 3);
}
private static int SetEveyTeamCountInfo(IWorkbook workbook, ISheet sheet, int startRow, TeamMemberCountModel team, int num, string groupName = "")
{
var row = startRow;
if (groupName != "")
{
// 设置组别名
var groupNameRow = sheet.CreateRow(row);
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 6));
var groupNameCell = groupNameRow.CreateCell(0);
groupNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true, 480);
groupNameCell.SetCellValue($"{groupName}");
row++;
// 设置表头
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(row, row, 3, 4));
sheet.AddMergedRegion(new CellRangeAddress(row, row + 1, 5, 5));
sheet.AddMergedRegion(new CellRangeAddress(row, row + 1, 6, 6));
var headerRow = sheet.CreateRow(row);
var nameCell = headerRow.CreateCell(0);
nameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
nameCell.SetCellValue("代表队");
var athletesCell = headerRow.CreateCell(3);
athletesCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
athletesCell.SetCellValue("运动员人数");
var leaderCell = headerRow.CreateCell(5);
leaderCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
leaderCell.CellStyle.WrapText = true;
leaderCell.SetCellValue("领队教练人数");
var allCell = headerRow.CreateCell(6);
allCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
allCell.SetCellValue("总人数");
row++;
var sexRow = sheet.CreateRow(row);
var teamNameCell = sexRow.CreateCell(1);
teamNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
teamNameCell.SetCellValue("全称");
var teamShortNameCell = sexRow.CreateCell(2);
teamShortNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
teamShortNameCell.SetCellValue("简称");
var menCell = sexRow.CreateCell(3);
menCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
menCell.SetCellValue("男");
var womenCell = sexRow.CreateCell(4);
womenCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true);
womenCell.SetCellValue("女");
row++;
}
var TeamCountRow = sheet.CreateRow(row);
TeamCountRow.CreateCell(0).SetCellValue($"{num}");
TeamCountRow.CreateCell(1).SetCellValue($"{team.TeamName}");
TeamCountRow.CreateCell(2).SetCellValue($"{team.TeamShortName}");
TeamCountRow.CreateCell(3).SetCellValue($"{team.MenAthletesCount}");
TeamCountRow.CreateCell(4).SetCellValue($"{team.WomenAthletesCount}");
TeamCountRow.CreateCell(5).SetCellValue($"{team.LeaderCoachCount}");
TeamCountRow.CreateCell(6).SetCellValue($"{team.MenAthletesCount + team.WomenAthletesCount + team.LeaderCoachCount}");
TeamCountRow.GetCell(0).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(1).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(2).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(3).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(4).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(5).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
TeamCountRow.GetCell(6).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row);
return(++row);
}
