/// <summary>
///
/// </summary>
/// <param name="excelApp">
/// Приложение Excel, в котором открыты книги
/// <paramref name="wbkTarget"/> и <paramref name="wbkTemplates"/>
/// </param>
/// <param name="wbkTarget">
/// Книга, в которую добавляется отчёт. Добавление всегда происходит в конец
/// </param>
/// <param name="wbkTemplates">
/// Книга с шаблонами отчётов
/// </param>
/// <returns></returns>
public override bool CreateReport(MSExcel.Application excelApp,
MSExcel.Workbook wbkTarget,
MSExcel.Workbook wbkTemplates)
{
CPersonalResultsComparer Comparer = new CPersonalResultsComparer()
{
CompareProperty = CurTask.m_WinnerDetection,
CompareProperty1 = CurTask.m_PriorityCompetitionKind
};
foreach (CGroupItem GroupItem in CurTask.m_Groups)
{
CGroupItem LeadGroupItem = null;
MSExcel.Worksheet wsh = null;
lock (DBManagerApp.m_AppSettings.m_SettingsSyncObj)
{
wsh = wbkTemplates.Worksheets[DBManagerApp.m_AppSettings.m_Settings.ExcelSettings.dictReportTemplates[enReportTypes.Personal]];
}
wsh.Copy(After: wbkTarget.Worksheets[wbkTarget.Worksheets.Count]);
// Лист, в который нужно будет вставлять данные
wsh = wbkTarget.Worksheets[wbkTarget.Worksheets.Count];
wsh.Name = GroupItem.SheetName;
groups GroupInDB = CurTask.m_CompDesc.groups.First(arg => arg.id_group == GroupItem.id);
CCompSettings CompSettings = new CCompSettings(GroupInDB);
wsh.Range[RN_COMP_NAME].Value = CompSettings.CompName;
wsh.Range[RN_MAIN_JUDGE].Value = CompSettings.MainJudge;
wsh.Range[RN_MAIN_SECRETARY].Value = CompSettings.MainSecretary;
wsh.Range[RN_SECOND_COL_NAME].Value = CompSettings.SecondColName;
string ReportName = wsh.Range[RN_REPORT_NAME].Value;
int SelectedStartYear, SelectedEndYear;
wsh.Range[RN_REPORT_NAME].Value = ReportName.Replace(GlobalDefines.EXCEL_REPORT_NAME_TEXT_TO_REPLACE,
CreateGroupName(GroupItem,
CompSettings.AgeGroup,
out SelectedStartYear,
out SelectedEndYear));
// Формируем список спортсменов, принявших участие в соревновании в данной возрастной группе
List <CPersonalItem> lstResults = (from member in DBManagerApp.m_Entities.members
join part in DBManagerApp.m_Entities.participations on member.id_member equals part.member
where part.Group == GroupInDB.id_group &&
part.result_place.HasValue
orderby part.result_place
select new CPersonalItem
{
m_SurnameAndName = member.surname + " " + member.name,
m_Team = CompSettings.SecondColNameType == enSecondColNameType.Coach ? part.coach : part.team,
m_YearOfBirth = member.year_of_birth,
m_Grade = part.init_grade
}).ToList();
/* Расставляем места в скорости:
* при фильтрации по годам рождения места должны быть не общими, а в рамках выбранных годов рождения */
for (int i = 0; i < lstResults.Count;)
{
if (lstResults[i].m_YearOfBirth >= SelectedStartYear && lstResults[i].m_YearOfBirth <= SelectedEndYear)
{
lstResults[i].m_SpeedPlace = i + 1;
i++;
}
else
{
lstResults.RemoveAt(i);
}
}
bool HasGroupInLead = CurTask.m_LeadReportInfo.m_wbkLeadReport != null &&
CurTask.m_LeadReportInfo.m_dictLeadGroupInfos.TryGetValue(GroupItem.id, out LeadGroupItem) &&
LeadGroupItem.LeadSheetIndex >= 0 &&
LeadGroupItem.LeadSheetIndex < CurTask.m_LeadReportInfo.m_LeadSheets.Count;
DateTime?StartDate = CompSettings.StartDate.Date;
DateTime?EndDate = null;
if (CompSettings.EndDate != null)
{
EndDate = CompSettings.EndDate.Date;
}
if (HasGroupInLead)
{ // Такая группа есть в протоколе трудности
// Трудность есть => учитываем её при подсчёте дат
if (CompSettings.StartDate.Date > LeadGroupItem.StartDate)
{
StartDate = LeadGroupItem.StartDate;
}
if (EndDate == null)
{
if (LeadGroupItem.EndDate.HasValue)
{
EndDate = LeadGroupItem.EndDate;
}
}
else
{
if (LeadGroupItem.EndDate.HasValue && EndDate < LeadGroupItem.EndDate)
{
EndDate = LeadGroupItem.EndDate;
}
}
MSExcel.Worksheet wshLead = CurTask.m_LeadReportInfo.m_wbkLeadReport.Worksheets[LeadGroupItem.LeadSheetIndex + 1];
// Просматриваем всех участников трудности и ищем их в скорости
int skippedQ = 0;
for (int Row = 0;
wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value != null &&
!string.IsNullOrWhiteSpace(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value.ToString());
Row++)
{
int PlaceInLead;
if (!int.TryParse(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value.ToString(), out PlaceInLead))
{ // У участника какое-то неонятное место => пропускаем
skippedQ++;
continue;
}
// Ищем участника в скорости
string[] arrNameAndSurname;
string NameAndSurnameInLead = GlobalDefines.CorrectSurnameAndName(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PersonalDataColumnIndex].Value,
out arrNameAndSurname);
int YoBInLead;
string strYoBInLead = wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_YearOfBirthColumnIndex].Value.ToString();
CPersonalItem item = null;
if (!int.TryParse(strYoBInLead, out YoBInLead))
{ // Какой-то неверный год рождения => пропускаем
skippedQ++;
MessageBox.Show(string.Format(Properties.Resources.resfmtInvalidYoBInLead,
NameAndSurnameInLead,
strYoBInLead),
DBManagerApp.MainWnd.Title,
MessageBoxButton.OK,
MessageBoxImage.Error);
continue;
}
item = lstResults.Find(arg => arg.m_SurnameAndName == NameAndSurnameInLead && arg.m_YearOfBirth == YoBInLead);
if (item == null)
{ // Не нашли => он не может участвовать в многоборье
if (!(YoBInLead >= SelectedStartYear && YoBInLead <= SelectedEndYear))
{
skippedQ++;
}
continue;
}
item.m_LeadPlace = PlaceInLead - skippedQ;
// Определяем m_ResultPlace
switch (CurTask.m_PlaceAggregationMethod)
{
case enPersRepPlacesAggregationMethod.Sum:
item.m_TotalPlace = item.m_LeadPlace + item.m_SpeedPlace;
break;
case enPersRepPlacesAggregationMethod.Mul:
item.m_TotalPlace = item.m_LeadPlace * item.m_SpeedPlace;
break;
}
}
// Оставляем только тех, кто участвовал и в трудности и скорости
lstResults.RemoveAll(arg => arg.m_TotalPlace == -1);
lstResults.Sort((lhs, rhs) => (lhs.m_TotalPlace.CompareTo(rhs.m_TotalPlace)));
lstResults.Add(new CPersonalItem()); // Добавляем барьерный элемент
// Определяем места участников в двоеборье
int TrainStartIndex = 0;
List <CPersonalItem> Train = new List <CPersonalItem>(); // Участники, входящие в паровоз
for (int i = 0; i < lstResults.Count - 1; i++)
{
Train.Add(lstResults[i]);
if (lstResults[i].m_TotalPlace != lstResults[i + 1].m_TotalPlace)
{ // Паровоз закончился => определяем места у всех его членов
if (Train.Count > 1)
{
Train.Sort(Comparer); // Сортируем паровоз по дополнительному критерию
int CurPlace = TrainStartIndex + 1;
CPersonalItem PrevResult = null;
foreach (CPersonalItem TrainItem in Train)
{
if (Comparer.Compare(TrainItem, PrevResult) != 0)
{
CurPlace = TrainStartIndex + 1;
}
TrainItem.m_Place = CurPlace;
PrevResult = TrainItem;
TrainStartIndex++;
}
}
else
{ // паровоз состоит из 1 человека - lstResults[i]
lstResults[i].m_Place = ++TrainStartIndex;
}
Train.Clear();
}
}
lstResults.RemoveAt(lstResults.Count - 1);
// Окончательно сортируем участников
lstResults.Sort((lhs, rhs) => (lhs.m_Place.CompareTo(rhs.m_Place)));
}
// Выводим полученные места в протокол двоеборья
int FirstRowInSpeed = wsh.Range[RN_FIRST_DATA_ROW].Row;
int RowInSpeed = 0;
foreach (CPersonalItem PersonalItem in lstResults)
{
if (PersonalItem.m_Place > 0)
{
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_PLACE_COL_NUM].Value = GlobalDefines.EncodePlace((byte)PersonalItem.m_Place);
}
else
{
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_PLACE_COL_NUM].Value = null;
}
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_PERSONAL_COL_NUM].Value = PersonalItem.m_SurnameAndName;
if (CompSettings.SecondColNameType == enSecondColNameType.Coach)
{
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.coaches.First(arg => arg.id_coach == PersonalItem.m_Team).name;
}
else
{
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.teams.First(arg => arg.id_team == PersonalItem.m_Team).name;
}
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_YEAR_OF_BIRTH_COL_NUM].Value = PersonalItem.m_YearOfBirth;
GradeMarkupConverter conv = new GradeMarkupConverter();
wsh.Cells[FirstRowInSpeed + RowInSpeed, EXCEL_GRADE_COL_NUM].Value = conv.Convert(PersonalItem.m_Grade,
PersonalItem.m_Grade.GetType(),
null,
CultureInfo.GetCultureInfo(GlobalDefines.RUSSIAN_CULTURE_NAME));
wsh.Range[RN_SPEED].Offset[RowInSpeed].Value = PersonalItem.m_SpeedPlace;
if (PersonalItem.m_LeadPlace > 0)
{
wsh.Range[RN_LEAD].Offset[RowInSpeed].Value = PersonalItem.m_LeadPlace;
}
if (PersonalItem.m_TotalPlace > 0)
{
wsh.Range[RN_RESULT].Offset[RowInSpeed].Value = PersonalItem.m_TotalPlace;
}
else
{ // Вставляем формулу, чтобы потом было бастрее работать с протоколом
switch (CurTask.m_PlaceAggregationMethod)
{
case enPersRepPlacesAggregationMethod.Sum:
wsh.Range[RN_RESULT].Offset[RowInSpeed].FormulaR1C1 = "=RC[-2] + RC[-1]";
break;
case enPersRepPlacesAggregationMethod.Mul:
wsh.Range[RN_RESULT].Offset[RowInSpeed].FormulaR1C1 = "=RC[-2] * RC[-1]";
break;
}
}
RowInSpeed++;
}
// Удаляем лишние строки
wsh.Rows[(FirstRowInSpeed + RowInSpeed).ToString() + ":" + (EXCEL_MAX_LINES_IN_REPORTS + FirstRowInSpeed - 1).ToString()].Delete(MSExcel.XlDirection.xlUp);
// Выводим дату соревнований в самом конце
wsh.Range[RN_ROUND_DATE].Value = GlobalDefines.CreateCompDate(StartDate.Value, EndDate);
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="excelApp">
/// Приложение Excel, в котором открыты книги
/// <paramref name="wbkTarget"/> и <paramref name="wbkTemplates"/>
/// </param>
/// <param name="wbkTarget">
/// Книга, в которую добавляется отчёт. Добавление всегда происходит в конец
/// </param>
/// <param name="wbkTemplates">
/// Книга с шаблонами отчётов
/// </param>
/// <returns></returns>
public override bool CreateReport(MSExcel.Application excelApp,
MSExcel.Workbook wbkTarget,
MSExcel.Workbook wbkTemplates)
{
List <CTeamWithMembersItem> TeamReportData = new List <CTeamWithMembersItem>();
foreach (CTeamForReportItem item in CurTask.m_Teams)
{
TeamReportData.Add(new CTeamWithMembersItem()
{
m_TeamInfo = item
});
}
// Копируем в конец новой книги лист-шаблон
MSExcel.Worksheet wsh = null;
lock (DBManagerApp.m_AppSettings.m_SettingsSyncObj)
{
wsh = wbkTemplates.Worksheets[DBManagerApp.m_AppSettings.m_Settings.ExcelSettings.dictReportTemplates[enReportTypes.Team]];
}
wsh.Copy(After: wbkTarget.Worksheets[wbkTarget.Worksheets.Count]);
// Лист, в который нужно будет вставлять данные
wsh = wbkTarget.Worksheets[wbkTarget.Worksheets.Count];
wsh.Name = Properties.Resources.resTeamReportSheetName;
DateTime?MinDate = null, MaxDate = null;
wsh.Range[RN_WOMEN_IN_GROUP].Value = CurTask.m_WomenInGroup;
wsh.Range[RN_MEN_IN_GROUP].Value = CurTask.m_MenInGroup;
bool HasLeadReport = CurTask.m_LeadReportInfo.m_wbkLeadReport != null;
string UnknownLeadTeams = "";
#region Подсчёт статистики в трудности
if (HasLeadReport)
{
// Проходим все листы трудности - все группы
foreach (KeyValuePair <long, CGroupItem> LeadGroupInfo in CurTask.m_LeadReportInfo.m_dictLeadGroupInfos)
{
if (MinDate == null || LeadGroupInfo.Value.StartDate < MinDate)
{
MinDate = LeadGroupInfo.Value.StartDate;
}
if (LeadGroupInfo.Value.EndDate != null)
{
if (MaxDate == null || LeadGroupInfo.Value.EndDate > MaxDate)
{
MaxDate = LeadGroupInfo.Value.EndDate;
}
}
MSExcel.Worksheet wshLead = CurTask.m_LeadReportInfo.m_wbkLeadReport.Worksheets[LeadGroupInfo.Value.LeadSheetIndex + 1];
List <CPersonalItem> LeadMembers = new List <CPersonalItem>();
for (int Row = 0;
wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value != null &&
!string.IsNullOrWhiteSpace(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value.ToString());
Row++)
{
int PlaceInLead;
if (!int.TryParse(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PlaceColumnIndex].Value.ToString(), out PlaceInLead))
{ // У участника какое-то неонятное место => пропускаем
continue;
}
string[] arrNameAndSurname;
string SurnameAndName = GlobalDefines.CorrectSurnameAndName(wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_PersonalDataColumnIndex].Value,
out arrNameAndSurname);
// Ищем команду в списке
string TeamNameInLead = wshLead.Cells[Row + CurTask.m_LeadReportInfo.m_FirstMemberRow, CurTask.m_LeadReportInfo.m_TeamColumnIndex].Value;
TeamNameInLead = GlobalDefines.CorrectTeam(TeamNameInLead);
CTeamWithMembersItem TeamInfo = TeamReportData.Find(arg => arg.m_TeamInfo.Subteams.FirstOrDefault(arg1 => arg1.Name == TeamNameInLead) != null);
if (TeamInfo == null)
{ // Такой команды нет => запоминаем её название, чтобы потом вывести его в сообщении
// Но из рассмотрения этого участника не выкидываем, т.к. нужно посчитать заработанные им баллы
UnknownLeadTeams += string.Format("\t {0} {1} {2};\n", GlobalDefines.BULLET_CHAR_IN_MSG_BOX, SurnameAndName, TeamNameInLead);
}
// Заносим участника в LeadMembers, чтобы потом посчитать его баллы
CPersonalItem Member = new CPersonalItem()
{
m_Place = PlaceInLead,
m_GroupId = LeadGroupInfo.Value.id,
m_SurnameAndName = SurnameAndName,
m_Sex = LeadGroupInfo.Value.Sex
};
LeadMembers.Add(Member);
if (TeamInfo != null)
{
List <CPersonalItem> GroupLeadMembers;
if (TeamInfo.m_dictLeadMembers.TryGetValue(Member.m_GroupId, out GroupLeadMembers))
{
if (GroupLeadMembers.Count < (LeadGroupInfo.Value.Sex == enSex.Male ? CurTask.m_MenInGroup : CurTask.m_WomenInGroup))
{ // Группа ещё не заполнена
GroupLeadMembers.Add(Member);
}
}
else
{
GroupLeadMembers = new List <CPersonalItem>();
GroupLeadMembers.Add(Member);
TeamInfo.m_dictLeadMembers.Add(Member.m_GroupId, GroupLeadMembers);
}
}
}
MakeBallsForPlaces(LeadMembers); // Считаем баллы за места в трудности
LeadMembers.Clear();
}
// Удаляем команды, которых нет в трудности, т.к. для командного зачёта необходим результат команды и в трудности, и в скорости
TeamReportData.RemoveAll(arg => arg.m_dictLeadMembers.Count == 0);
if (!string.IsNullOrWhiteSpace(UnknownLeadTeams))
{ // Есть команды, которых нет в TeamReportData => сообщаем об этом
// Оставляем не более 10 записей, чтобы не раздувать сообщение
List <string> Lines = UnknownLeadTeams.Split('\n').ToList();
Lines.RemoveAt(Lines.Count - 1); // Последняя строка будет всегда пустой
if (Lines.Count > 10)
{
UnknownLeadTeams = "";
for (int i = 0; i < 10; i++)
{
UnknownLeadTeams += Lines[i] + "\n";
}
UnknownLeadTeams += "...\n";
}
MessageBox.Show(string.Format(Properties.Resources.resfmtUnknownTeamsInLead, UnknownLeadTeams),
DBManagerApp.MainWnd.Title,
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
#endregion
#region Подсчитываем статистику в скорости
bool IsFirstGroup = true;
foreach (groups GroupInDB in CurTask.m_CompDesc.groups)
{
CCompSettings CompSettings = new CCompSettings(GroupInDB);
if (IsFirstGroup)
{
IsFirstGroup = false;
wsh.Range[RN_COMP_NAME].Value = CompSettings.CompName;
wsh.Range[RN_MAIN_JUDGE].Value = CompSettings.MainJudge;
wsh.Range[RN_MAIN_SECRETARY].Value = CompSettings.MainSecretary;
}
if (MinDate == null || CompSettings.StartDate.Date < MinDate)
{
MinDate = CompSettings.StartDate.Date;
}
if (CompSettings.EndDate != null)
{
if (MaxDate == null || CompSettings.EndDate.Date > MaxDate)
{
MaxDate = CompSettings.EndDate.Date;
}
}
// Раскидываем участников по командам
foreach (CPersonalItem Member in (from member in DBManagerApp.m_Entities.members
join part in DBManagerApp.m_Entities.participations on member.id_member equals part.member
where part.Group == GroupInDB.id_group &&
part.result_place.HasValue &&
part.results_speed.Count(arg => arg.balls.HasValue && arg.balls.Value > 0) == 1 // Выбираем только те группы, у которых расставлены баллы
orderby part.result_place
select new CPersonalItem()
{
m_Place = part.result_place.Value,
m_GroupId = part.Group,
m_SurnameAndName = member.surname + " " + member.name,
m_Sex = (enSex)member.sex,
m_Balls = (from result in part.results_speed
where result.balls.HasValue
select result.balls.Value).FirstOrDefault(),
m_Team = CompSettings.SecondColNameType == enSecondColNameType.Coach ? part.coach : part.team
}).ToList())
{
string TeamName = "";
if (CompSettings.SecondColNameType == enSecondColNameType.Coach)
{
TeamName = DBManagerApp.m_Entities.coaches.First(arg => arg.id_coach == Member.m_Team).name;
}
else
{
TeamName = DBManagerApp.m_Entities.teams.First(arg => arg.id_team == Member.m_Team).name;
}
CTeamWithMembersItem TeamInfo = TeamReportData.Find(arg => arg.m_TeamInfo.Subteams.FirstOrDefault(arg1 => arg1.Name == TeamName) != null);
List <CPersonalItem> GroupSpeedMembers;
if (TeamInfo.m_dictSpeedMembers.TryGetValue(Member.m_GroupId, out GroupSpeedMembers))
{
if (GroupSpeedMembers.Count < (Member.m_Sex == enSex.Male ? CurTask.m_MenInGroup : CurTask.m_WomenInGroup))
{ // Группа ещё не заполнена
GroupSpeedMembers.Add(Member);
}
}
else
{
GroupSpeedMembers = new List <CPersonalItem>();
GroupSpeedMembers.Add(Member);
TeamInfo.m_dictSpeedMembers.Add(Member.m_GroupId, GroupSpeedMembers);
}
}
}
// Удаляем команды, которых нет в скорости, т.к. для командного зачёта необходим результат команды и в трудности, и в скорости
TeamReportData.RemoveAll(arg => arg.m_dictSpeedMembers.Count == 0);
#endregion
// Выводим дату соревнований
wsh.Range[RN_ROUND_DATE].Value = GlobalDefines.CreateCompDate(MinDate.Value, MaxDate);
// Сортируем команды по убыванию заработанных баллов
TeamReportData.Sort((lhs, rhs) =>
{
if (GlobalDefines.IsFloatsEqu(lhs.TotalBalls, rhs.TotalBalls, 1e-3))
{
return(0);
}
else if (lhs.TotalBalls < rhs.TotalBalls)
{
return(1);
}
else
{
return(-1);
}
});
TeamReportData.ForEach(team =>
{
// Сортируем группы по полу: сначала девочки, потом мальчики, а потом по годам рождения
team.m_dictLeadMembers = (from GroupMembers in team.m_dictLeadMembers
orderby GroupMembers.Value[0].m_Sex descending,
DBManagerApp.m_Entities.groups.FirstOrDefault(gr => gr.id_group == GroupMembers.Key).start_year descending
select GroupMembers).ToDictionary(key => key.Key, value => value.Value);
team.m_dictSpeedMembers = (from GroupMembers in team.m_dictSpeedMembers
orderby GroupMembers.Value[0].m_Sex descending,
DBManagerApp.m_Entities.groups.FirstOrDefault(gr => gr.id_group == GroupMembers.Key).start_year descending
select GroupMembers).ToDictionary(key => key.Key, value => value.Value);
});
#region Выводим отчёт
int FirstRow = wsh.Range[RN_FIRST_DATA_ROW].Row;
int CurPlace = 1;
float?PrevResult = null; // Такого результата не может быть
for (int i = 0; i < TeamReportData.Count; i++)
{
if (TeamReportData[i].TotalBalls != PrevResult)
{
CurPlace = i + 1;
}
wsh.Cells[i + FirstRow, EXCEL_PLACE_COL_NUM].Value = GlobalDefines.EncodePlace((byte)CurPlace);
wsh.Cells[i + FirstRow, EXCEL_TEAM_COL_NUM].Value = TeamReportData[i].m_TeamInfo.Name;
string MembersList = "";
if (HasLeadReport)
{
foreach (KeyValuePair <long, List <CPersonalItem> > GroupMembers in TeamReportData[i].m_dictLeadMembers)
{
GroupMembers.Value.ForEach(member =>
{
MembersList += string.Format("{0} ({1}) ",
member.m_SurnameAndName,
member.m_Balls);
});
MembersList += "\n";
}
wsh.Range[RN_LEAD].Offset[i].Cells[1, EXCEL_TEAM_MEMBERS_OFS + 1].Value = MembersList.Left(MembersList.Length - 1);
wsh.Range[RN_LEAD].Offset[i].Cells[1, EXCEL_TEAM_BALLS_OFS + 1].Value = TeamReportData[i].LeadBalls;
}
MembersList = "";
foreach (KeyValuePair <long, List <CPersonalItem> > GroupMembers in TeamReportData[i].m_dictSpeedMembers)
{
GroupMembers.Value.ForEach(member =>
{
MembersList += string.Format("{0} ({1}) ",
member.m_SurnameAndName,
member.m_Balls);
});
MembersList += "\n";
}
wsh.Range[RN_SPEED].Offset[i].Cells[1, EXCEL_TEAM_MEMBERS_OFS + 1].Value = MembersList.Left(MembersList.Length - 1);
wsh.Range[RN_SPEED].Offset[i].Cells[1, EXCEL_TEAM_BALLS_OFS + 1].Value = TeamReportData[i].SpeedBalls;
if (HasLeadReport)
{
wsh.Range[RN_RESULT].Offset[i].Value = TeamReportData[i].TotalBalls;
}
else
{
wsh.Range[RN_RESULT].Offset[i].FormulaR1C1 = "=RC[-3] + RC[-1]";
}
PrevResult = TeamReportData[i].TotalBalls;
}
// Удаляем лишние строки
wsh.Rows[(FirstRow + TeamReportData.Count).ToString() + ":" + (EXCEL_MAX_LINES_IN_TEAM_REPORT + FirstRow - 1).ToString()].Delete(MSExcel.XlDirection.xlUp);
#endregion
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="excelApp">
/// Приложение Excel, в котором открыты книги
/// <paramref name="wbkTarget"/> и <paramref name="wbkTemplates"/>
/// </param>
/// <param name="wbkTarget">
/// Книга, в которую добавляется отчёт. Добавление всегда происходит в конец
/// </param>
/// <param name="wbkTemplates">
/// Книга с шаблонами отчётов
/// </param>
/// <returns></returns>
public override bool CreateReport(MSExcel.Application excelApp,
MSExcel.Workbook wbkTarget,
MSExcel.Workbook wbkTemplates)
{
// Копируем в конец новой книги лист-шаблон
MSExcel.Worksheet wsh = null;
MSExcel.Range rng = null;
lock (DBManagerApp.m_AppSettings.m_SettingsSyncObj)
{
wsh = wbkTemplates.Worksheets[DBManagerApp.m_AppSettings.m_Settings.ExcelSettings.dictReportTemplates[enReportTypes.Total]];
}
wsh.Copy(After: wbkTarget.Worksheets[wbkTarget.Worksheets.Count]);
// Лист, в который нужно будет вставлять данные
wsh = wbkTarget.Worksheets[wbkTarget.Worksheets.Count];
wsh.Name = CurTask.m_GroupToExport.Rounds.First(arg => arg.id == enRounds.Total).SheetName;
groups GroupInDB = CurTask.m_CompDesc.groups.First(arg =>
{
return(arg.id_group == CurTask.m_GroupToExport.id);
});
CCompSettings CompSettings = new CCompSettings(GroupInDB);
wsh.Range[RN_COMP_NAME].Value = CompSettings.CompName;
wsh.Range[RN_ROUND_DATE].Value = GlobalDefines.CreateCompDate(CompSettings.StartDate, CompSettings.EndDate == null ? null : (DateTime?)CompSettings.EndDate.Date);
wsh.Range[RN_MAIN_JUDGE].Value = CompSettings.MainJudge;
wsh.Range[RN_MAIN_SECRETARY].Value = CompSettings.MainSecretary;
wsh.Range[RN_SECOND_COL_NAME].Value = CompSettings.SecondColName;
if (CompSettings.Row6 == GlobalDefines.DEFAULT_XML_STRING_VAL)
{
wsh.Cells[wsh.Range[RN_ROW_6].Row, 1].Value = "";
}
else
{
wsh.Range[RN_ROW_6].Value = CompSettings.Row6;
}
string ReportName = wsh.Range[RN_REPORT_NAME].Value;
int SelectedStartYear, SelectedEndYear;
wsh.Range[RN_REPORT_NAME].Value = ReportName.Replace(GlobalDefines.EXCEL_REPORT_NAME_TEXT_TO_REPLACE,
CreateGroupName(CurTask.m_GroupToExport,
CompSettings.AgeGroup,
out SelectedStartYear,
out SelectedEndYear));
// Выводим участников соревнования
int FirstRow = wsh.Range[RN_FIRST_DATA_ROW].Row;
List <enRounds> CompRounds = (from round in CurTask.m_GroupToExport.Rounds
orderby round.id
select round.id).ToList();
int FirstMiddleSheetRoundMembers = 0;
if (CompRounds.Contains(enRounds.Qualif2))
{
FirstMiddleSheetRoundMembers = GroupInDB.from_2_qualif.Value;
}
else
{
FirstMiddleSheetRoundMembers = GroupInDB.from_1_qualif.Value;
// Удаляем столбцы со второй квалификацией
int ColQ = wsh.Range[RN_QUALIF_2].Columns.Count;
for (int col = 0; col < ColQ; col++)
{
wsh.Columns[wsh.Range[RN_QUALIF_2].Column].Delete();
}
}
// Удаляем столбцы для тех раундов, которых нет
if (!CompRounds.Contains(enRounds.OneEighthFinal))
{
wsh.Columns[wsh.Range[RN_ONE_EIGHTH_FINAL].Column].Delete();
}
if (!CompRounds.Contains(enRounds.QuaterFinal))
{
wsh.Columns[wsh.Range[RN_QUATERFINAL].Column].Delete();
}
if (!CompRounds.Contains(enRounds.SemiFinal))
{
wsh.Columns[wsh.Range[RN_SEMIFINAL].Column].Delete();
}
List <results_speed> AllGroupResultsInDB = (from part in DBManagerApp.m_Entities.participations
join result in DBManagerApp.m_Entities.results_speed on part.id_participation equals result.participation
where part.Group == CurTask.m_GroupToExport.id
select result).ToList();
List <CMemberInTotal> lstResults = (from member in DBManagerApp.m_Entities.members
join part in DBManagerApp.m_Entities.participations on member.id_member equals part.member
where part.Group == GroupInDB.id_group &&
part.result_place.HasValue
orderby part.result_place
select new CMemberInTotal
{
MemberInfo = new CFullMemberInfo()
{
IDMember = member.id_member,
Surname = member.surname,
Name = member.name,
YearOfBirth = member.year_of_birth,
Coach = part.coach,
Team = part.team,
InitGrade = part.init_grade,
},
TotalGrade = part.result_grade,
Place = part.result_place,
id_part = part.id_participation
}).ToList();
byte CurPlace = 1;
byte ResultIndex = 0;
int RowsQ = 0;
int? PrevResult = null; // Такого результата не может быть
foreach (CMemberInTotal MemberInTotal in lstResults)
{
RowsQ++;
if (MemberInTotal.MemberInfo.YearOfBirth.HasValue &&
(MemberInTotal.MemberInfo.YearOfBirth.Value < SelectedStartYear ||
MemberInTotal.MemberInfo.YearOfBirth.Value > SelectedEndYear))
{ // Фильтруем участников
continue;
}
ResultIndex++;
// Заносим результат всех раундов для участника
IEnumerable <results_speed> CurMemberResults = AllGroupResultsInDB.Where(arg => arg.participation == MemberInTotal.id_part);
foreach (results_speed MemberResult in CurMemberResults)
{
if (MemberResult.round == CurMemberResults.Max(arg => arg.round))
{
wsh.Range[RN_BALLS_FOR_PLACES].Offset[RowsQ].Value = MemberResult.balls;
}
switch ((enRounds)MemberResult.round)
{
case enRounds.Qualif:
wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_ROUTE1_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route1, (enAdditionalEventTypes?)MemberResult.event_1);
wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_ROUTE2_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route2, (enAdditionalEventTypes?)MemberResult.event_2);
wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
case enRounds.Qualif2:
wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_ROUTE1_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route1, (enAdditionalEventTypes?)MemberResult.event_1);
wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_ROUTE2_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route2, (enAdditionalEventTypes?)MemberResult.event_2);
wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
case enRounds.OneEighthFinal:
wsh.Range[RN_ONE_EIGHTH_FINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
case enRounds.QuaterFinal:
wsh.Range[RN_QUATERFINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
case enRounds.SemiFinal:
wsh.Range[RN_SEMIFINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
case enRounds.Final:
wsh.Range[RN_FINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum);
break;
}
}
if (MemberInTotal.Place.HasValue && MemberInTotal.Place != PrevResult)
{
CurPlace = ResultIndex;
}
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_PLACE_COL_NUM].Value = GlobalDefines.EncodePlace(CurPlace);
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_PERSONAL_COL_NUM].Value = MemberInTotal.MemberInfo.SurnameAndName;
if (CompSettings.SecondColNameType == enSecondColNameType.Coach)
{
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.coaches.First(arg => arg.id_coach == MemberInTotal.MemberInfo.Coach).name;
}
else
{
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.teams.First(arg => arg.id_team == MemberInTotal.MemberInfo.Team).name;
}
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_YEAR_OF_BIRTH_COL_NUM].Value = MemberInTotal.MemberInfo.YearOfBirth;
GradeMarkupConverter conv = new GradeMarkupConverter();
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_GRADE_COL_NUM].Value = conv.Convert(MemberInTotal.MemberInfo.InitGrade,
MemberInTotal.MemberInfo.InitGrade.GetType(),
null,
CultureInfo.GetCultureInfo(GlobalDefines.RUSSIAN_CULTURE_NAME));
if (MemberInTotal.TotalGrade.HasValue)
{
wsh.Range[RN_RESULT_GRADE].Offset[RowsQ].Value = conv.Convert(MemberInTotal.TotalGrade,
MemberInTotal.TotalGrade.GetType(),
null,
CultureInfo.GetCultureInfo(GlobalDefines.RUSSIAN_CULTURE_NAME));
}
PrevResult = MemberInTotal.Place;
}
// Редактируем сетку
// в квалификации
rng = wsh.Range[wsh.Cells[FirstMiddleSheetRoundMembers + FirstRow, EXCEL_PLACE_COL_NUM],
wsh.Cells[RowsQ + FirstRow - 1, EXCEL_GRADE_COL_NUM + EXCEL_SUM_COL_OFS]];
rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
// В выполненном разряде
rng = wsh.Range[wsh.Range[RN_RESULT_GRADE].Offset[FirstMiddleSheetRoundMembers + 1],
wsh.Range[RN_RESULT_GRADE].Offset[RowsQ + FirstRow]];
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
// В баллах
rng = wsh.Range[wsh.Range[RN_BALLS_FOR_PLACES].Offset[FirstMiddleSheetRoundMembers + 1],
wsh.Range[RN_BALLS_FOR_PLACES].Offset[RowsQ + FirstRow]];
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
if (CompRounds.Contains(enRounds.Qualif2))
{ // Во второй квалификации
// Удаляем подчёркивание, где оно не нужно и добавляем там, где нужно
rng = wsh.Range[wsh.Cells[FirstMiddleSheetRoundMembers + FirstRow, EXCEL_PLACE_COL_NUM],
wsh.Cells[GroupInDB.from_1_qualif.Value + FirstRow - 1, wsh.Range[RN_QUALIF_2].Column + EXCEL_SUM_COL_OFS - 1]];
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
// Удаляем сетку, где она не нужна
rng = wsh.Range[wsh.Range[RN_QUALIF_2].Offset[GroupInDB.from_1_qualif.Value + 1].Cells[1, 1],
wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS]];
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlInsideVertical].LineStyle = MSExcel.Constants.xlNone;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
// В выполненном разряде
// Выполняем подчёркивание
rng = wsh.Range[RN_RESULT_GRADE].Offset[GroupInDB.from_1_qualif.Value + 1];
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
// В баллах
rng = wsh.Range[RN_BALLS_FOR_PLACES].Offset[GroupInDB.from_1_qualif.Value + 1];
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight =
rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous;
rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium;
}
bool NeedCorrectBorders = false;
int MaxRows = EXCEL_MAX_LINES_IN_REPORTS;
rng = wsh.Range[RN_TABLE_HEADER].Cells[1, EXCEL_PERSONAL_COL_NUM];
for (int r = 0; r < RowsQ;)
{
if (string.IsNullOrEmpty(rng.Offset[r + 1].Value as string))
{
wsh.Rows[rng.Offset[r + 1].Row].Delete();
MaxRows--;
RowsQ--;
if (r < GroupInDB.from_1_qualif.Value)
{
NeedCorrectBorders = true;
}
}
else
{
r++;
}
}
wsh.Rows[(RowsQ + FirstRow).ToString() + ":" + (MaxRows + FirstRow - 1).ToString()].Delete(MSExcel.XlDirection.xlUp);
if (NeedCorrectBorders)
{ // Ещё раз корректируем подчёркивание
if (CompRounds.Contains(enRounds.Qualif2))
{
GlobalDefines.CorrectBordersInTotal(wsh,
wsh.Range[RN_QUALIF_2].Offset[1].Cells[1, EXCEL_SUM_COL_OFS],
RowsQ,
EXCEL_PLACE_COL_NUM,
RN_RESULT_GRADE,
RN_BALLS_FOR_PLACES);
}
if (CompRounds.Contains(enRounds.OneEighthFinal))
{
GlobalDefines.CorrectBordersInTotal(wsh,
wsh.Range[RN_ONE_EIGHTH_FINAL].Offset[1],
RowsQ,
EXCEL_PLACE_COL_NUM,
RN_RESULT_GRADE,
RN_BALLS_FOR_PLACES);
}
if (CompRounds.Contains(enRounds.QuaterFinal))
{
GlobalDefines.CorrectBordersInTotal(wsh,
wsh.Range[RN_QUATERFINAL].Offset[1],
RowsQ,
EXCEL_PLACE_COL_NUM,
RN_RESULT_GRADE,
RN_BALLS_FOR_PLACES);
}
if (CompRounds.Contains(enRounds.SemiFinal))
{
GlobalDefines.CorrectBordersInTotal(wsh,
wsh.Range[RN_FINAL].Offset[1], // Здесь должен быть RN_FINAL
RowsQ,
EXCEL_PLACE_COL_NUM,
RN_RESULT_GRADE,
RN_BALLS_FOR_PLACES);
}
}
if (!CurTask.m_GroupToExport.Rounds.First(arg => arg.id == enRounds.Total).ExtraOption.Value)
{ // Удаляем столбец с баллами
wsh.Columns[wsh.Range[RN_BALLS_FOR_PLACES].Column].Delete(MSExcel.XlDirection.xlToLeft);
}
return(true);
}