protected override void Process(ISheet table) { if (ListInt.Count == 0) { table.FindCellByMacros(title).SetValue(GetNotNullString("Отчет по сценариям (Форма 2). Нет данных!")); return; } table.FindCellByMacros(title).SetValue(GetNotNullString(string.Format("Отчет по сценариям (Форма 2)"))); ICellStyle style = table.Workbook.CreateCellStyle(); style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; table.GetRow(3).CreateCell(1).SetCellValue(ListInt.ElementAt(0)); table.GetRow(3).GetCell(1).CellStyle = style; table.GetRow(4).CreateCell(1).SetCellValue(ListInt.ElementAt(1)); table.GetRow(4).GetCell(1).CellStyle = style; table.GetRow(5).CreateCell(1).SetCellValue(ListInt.ElementAt(2)); table.GetRow(5).GetCell(1).CellStyle = style; table.GetRow(3).CreateCell(2).SetCellValue(ListInt.ElementAt(3)); table.GetRow(3).GetCell(2).CellStyle = style; table.GetRow(4).CreateCell(2).SetCellValue(ListInt.ElementAt(4)); table.GetRow(4).GetCell(2).CellStyle = style; table.GetRow(5).CreateCell(2).SetCellValue(ListInt.ElementAt(5)); table.GetRow(5).GetCell(2).CellStyle = style; table.GetRow(6).CreateCell(1).SetCellValue(ListInt.ElementAt(6)); table.GetRow(6).CreateCell(2).SetCellValue(ListInt.ElementAt(7)); }
protected override void Process(ISheet table) { table.FindCellByMacros(FULLNAME_LPU).SetValue(Pretension.M_mo); string string1 = "В связи с поступившими обращениями застрахованных о нарушении их прав на выбор медицинской организации, АО «МСК «УралСиб» просит предоставить для проведения целевой медико-экономической экспертизы заявление о выборе " + Pretension.M_mo + " следующих граждан:"; table.FindCellByMacros(GENERATIONString1).SetValue(string1); string string2 = Client.ActualVersion.Lastname + " " + Client.ActualVersion.Firstname + " " + Client.ActualVersion.Secondname + ", " + (Client.ActualVersion.Birthday.HasValue ? GetDateString(Client.ActualVersion.Birthday, "dd.MM.yyyy") : string.Empty) + " г.р., " + "дата прикрепления " + GetDateString(Pretension.DATE_IN, "dd.MM.yyyy") + " г., "; string ENP = Client.Visits.OrderBy(a => a.StatusDate).LastOrDefault().UnifiedPolicyNumber; if (string.IsNullOrEmpty(ENP)) { string2 = string2 + "серия " + Client.Visits.OrderBy(a => a.StatusDate).LastOrDefault().PolicySeries + " номер " + Client.Visits.OrderBy(a => a.StatusDate).LastOrDefault().PolicyNumber; } else { string2 = string2 + "ЕНП " + ENP; } table.FindCellByMacros(GENERATIONString2).SetValue(string2); table.FindCellByMacros(DATE).SetValue(GetDateString(Pretension.M_dakt, "dd.MM.yyyy")); table.FindCellByMacros(USER_FIO).SetValue(Pretension.UserFIO); }
protected override void Process(ISheet table) { var firstVisit = Visits.FirstOrDefault(); if (firstVisit == null) { table.FindCellByMacros(title).SetValue(GetNotNullString("Журнал выдачи полисов ОМС")); return; } string partyNumber = firstVisit.PolicyParty; table.FindCellByMacros(title).SetValue(GetNotNullString(string.Format("Журнал выдачи полисов ОМС (заявка {0})", partyNumber))); int rowNumber = 3; ICellStyle style = table.Workbook.CreateCellStyle(); style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; for (int i = 0; i < Visits.Count; i++) { var clientVisit = Visits[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetValue(GetNotNullString(clientVisit.TemporaryPolicyNumber)); row.CreateCell(2).SetValue(GetDateString(clientVisit.TemporaryPolicyDate, "dd.MM.yyyy")); row.CreateCell(3).SetValue(GetNotNullString(clientVisit.UnifiedPolicyNumber)); string fio = string.Format("{0} {1} {2}", clientVisit.Lastname ?? string.Empty, clientVisit.Firstname ?? string.Empty, clientVisit.Secondname ?? string.Empty); row.CreateCell(4).SetValue(GetNotNullString(fio)); row.CreateCell(5).SetValue(GetDateString(clientVisit.Birthday, "dd.MM.yyyy")); row.CreateCell(6).SetValue(GetNotNullString(clientVisit.DeliveryPoint)); row.CreateCell(7).SetValue(GetNotNullString(clientVisit.DeliveryCenter.Name)); row.CreateCell(8); row.CreateCell(9); if (!string.IsNullOrEmpty(clientVisit.Comment)) { clientVisit.Comment = clientVisit.Comment.Replace(" ", string.Empty); } row.CreateCell(10).SetValue(GetNotNullString(clientVisit.Comment)); row.GetCell(0).CellStyle = style; row.GetCell(1).CellStyle = style; row.GetCell(2).CellStyle = style; row.GetCell(3).CellStyle = style; row.GetCell(4).CellStyle = style; row.GetCell(5).CellStyle = style; row.GetCell(6).CellStyle = style; row.GetCell(7).CellStyle = style; row.GetCell(8).CellStyle = style; row.GetCell(9).CellStyle = style; row.GetCell(10).CellStyle = style; rowNumber++; } }
protected override void Process(ISheet table) { if (ListStatement == null || ListStatement.Count == 0) { table.FindCellByMacros(title).SetValue(GetNotNullString("Отчет по обращениям. Данных нет!")); return; } table.FindCellByMacros(title).SetValue(GetNotNullString(string.Format("Отчет по обращениям."))); int rowNumber = 3; for (int i = 0; i < ListStatement.Count; i++) //можно и через foreach { var item = ListStatement[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetCellValue(item.Id); row.CreateCell(2).SetValue(GetNotNullString(item.Lastname)); row.CreateCell(3).SetValue(GetNotNullString(item.Firstname)); row.CreateCell(4).SetValue(GetNotNullString(item.Secondname)); row.CreateCell(5).SetValue(GetNotNullString(item.Phone)); row.CreateCell(6).SetValue(GetNotNullString(item.Email)); row.CreateCell(7).SetValue(GetNotNullString(item.SubjectInsuranceName)); row.CreateCell(8).SetValue(GetDateString(item.CreateDate, "dd.MM.yyyy")); //dd.MM.yyyy H:mm:ss row.CreateCell(9).SetValue(GetNotNullString(item.LastStatusName)); row.CreateCell(10).SetValue(GetDateString(item.LastStatusDate, "dd.MM.yyyy")); row.CreateCell(11).SetValue(GetNotNullString(item.ClientID.ToString())); row.CreateCell(12).SetValue(GetNotNullString(item.VisitGroupID.ToString())); row.CreateCell(13).SetValue(GetNotNullString(item.CuratorFIO)); row.CreateCell(14).SetValue(GetNotNullString(item.ResponsibleFIO)); row.CreateCell(15).SetValue(GetNotNullString(item.ExecutiveFIO)); row.CreateCell(16).SetValue(GetNotNullString(item.MedDocumentTypeName)); row.CreateCell(17).SetValue(GetNotNullString(item.UnifiedPolicyNumber)); row.CreateCell(18).SetValue(GetNotNullString(item.LocalityName)); row.CreateCell(19).SetValue(GetDateString(item.IncidentDate, "dd.MM.yyyy")); row.CreateCell(20).SetValue(GetNotNullString(item.Description)); row.CreateCell(21).SetValue(GetNotNullString(item.IncomingСhannelName)); row.CreateCell(22).SetValue(GetNotNullString(item.AuthorFIO)); row.CreateCell(23).SetValue(GetNotNullString(item.StatusComment)); row.CreateCell(24).SetValue(GetNotNullString(item.AssignedToUserFIO)); row.CreateCell(25).SetValue(GetDateString(item.ExecuteToDate, "dd.MM.yyyy")); row.CreateCell(26).SetValue(GetNotNullString(item.ExpertiseName)); row.CreateCell(27).SetValue(GetDateString(item.ExpertiseDate, "dd.MM.yyyy")); if (item.FinancialSanctions.HasValue) { row.CreateCell(28).SetCellValue((double)item.FinancialSanctions); } if (item.Straf.HasValue) { row.CreateCell(29).SetCellValue((double)item.Straf); } row.CreateCell(30).SetValue(GetNotNullString(item.ReasonName)); row.CreateCell(31).SetValue(GetNotNullString(item.DescriptionExecution)); row.CreateCell(32).SetValue(GetNotNullString(item.LPU_Code)); row.CreateCell(33).SetValue(GetNotNullString(item.LPU_Name)); rowNumber++; } }
protected override void Process(ISheet table) { var firstVisit = Visits.FirstOrDefault(); if (firstVisit == null) { table.FindCellByMacros(ConstTitle).SetValue(GetNotNullString("Отчет по статусам. Данных нет!")); return; } table.FindCellByMacros(ConstTitle).SetValue(GetNotNullString("Отчет по статусам c " + GetDateString(StatusDateFrom, "dd.MM.yyyy") + " по " + GetDateString(StatusDateTo, "dd.MM.yyyy"))); string statuses = "Выбранные статусы: "; if (ListStatus.Count > 0) { foreach (var item in ListStatus) { statuses = statuses + item + ", "; } } else { statuses = statuses + "Все"; } statuses = statuses.TrimEnd(' ', ','); table.FindCellByMacros(ConstStatuses).SetValue(GetNotNullString(statuses)); int rowNumber = 4; for (int i = 0; i < Visits.Count; i++) { var clientVisit = Visits[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetCellValue(clientVisit.ClientId); row.CreateCell(2).SetCellValue(clientVisit.VisitGroupId); row.CreateCell(3).SetValue(GetNotNullString(clientVisit.DeliveryCenter != null ? clientVisit.DeliveryCenter.Code : string.Empty)); row.CreateCell(4).SetValue(GetNotNullString(clientVisit.DeliveryCenter.Name)); row.CreateCell(5).SetValue(GetNotNullString(clientVisit.DeliveryPoint)); row.CreateCell(6).SetValue(GetNotNullString(clientVisit.PolicyParty)); row.CreateCell(7).SetValue(GetNotNullString(clientVisit.TemporaryPolicyNumber)); row.CreateCell(8).SetValue(GetNotNullString(clientVisit.UnifiedPolicyNumber)); row.CreateCell(9).SetValue(GetNotNullString(clientVisit.Lastname)); row.CreateCell(10).SetValue(GetNotNullString(clientVisit.Firstname)); row.CreateCell(11).SetValue(GetNotNullString(clientVisit.Secondname)); row.CreateCell(12).SetValue(GetDateString(clientVisit.Birthday, "dd.MM.yyyy")); row.CreateCell(13).SetValue(GetNotNullString(clientVisit.Phone)); if (!string.IsNullOrEmpty(clientVisit.Comment)) { clientVisit.Comment = clientVisit.Comment.Replace(" ", string.Empty); } row.CreateCell(14).SetValue(GetNotNullString(clientVisit.Comment)); rowNumber++; } }
protected override void Process(ISheet table) { table.FindCellByMacros(title).SetValue("Отчет по отправленным смс с " + CreateDateFrom.ToShortDateString() + " по " + CreateDateTo.ToShortDateString()); int rowNumber = 3; for (int i = 0; i < ListSmsBase.Count; i++) { var sms = ListSmsBase[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetValue(GetNotNullString(sms.SenderId)); row.CreateCell(2).SetValue(GetNotNullString(sms.ClientId.ToString())); row.CreateCell(3).SetValue(GetNotNullString(sms.VisitGroupId.ToString())); row.CreateCell(4).SetValue(GetNotNullString(sms.VisitId.ToString())); row.CreateCell(5).SetValue(GetNotNullString(sms.Phone)); row.CreateCell(6).SetValue(GetNotNullString(sms.Message)); row.CreateCell(7).SetValue(sms.CreateDate.ToString()); row.CreateCell(8).SetValue(GetNotNullString(sms.Comment)); row.CreateCell(9).SetValue(GetNotNullString(sms.StatusIdInside.ToString())); row.CreateCell(10).SetValue(GetNotNullString(sms.StatusFromService)); row.CreateCell(11).SetValue(GetNotNullString(sms.MessageFromService)); row.CreateCell(12).SetValue(sms.SendDate.ToString()); row.CreateCell(13).SetValue(GetDateString(sms.StatuRepeatDate, "dd.MM.yyyy")); row.CreateCell(14).SetValue(GetNotNullString(sms.StatusIdRepeat.ToString())); rowNumber++; } }
protected override void Process(ISheet table) { var firstVisit = Visits.FirstOrDefault(); if (firstVisit == null) { table.FindCellByMacros(title).SetValue(GetNotNullString("Отчет по распределению БСО. Данных нет!")); return; } string partyNumber = firstVisit.PolicyParty; table.FindCellByMacros(title).SetValue(GetNotNullString(string.Format("Отчет по распределению БСО"))); int rowNumber = 3; for (int i = 0; i < Visits.Count; i++) { var clientVisit = Visits[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetValue(GetNotNullString(clientVisit.DeliveryCenter != null ? clientVisit.DeliveryCenter.Code : string.Empty)); row.CreateCell(2).SetValue(GetNotNullString(clientVisit.DeliveryPoint)); row.CreateCell(3).SetValue(GetNotNullString(clientVisit.PolicyParty)); row.CreateCell(4).SetValue(GetNotNullString(clientVisit.TemporaryPolicyNumber)); row.CreateCell(5).SetValue(GetDateString(clientVisit.TemporaryPolicyDate, "dd.MM.yyyy")); row.CreateCell(6).SetValue(GetNotNullString(clientVisit.UnifiedPolicyNumber)); row.CreateCell(7).SetValue(GetDateString(clientVisit.PolicyIssueDate, "dd.MM.yyyy")); string fio = string.Format("{0} {1} {2}", clientVisit.Lastname ?? string.Empty, clientVisit.Firstname ?? string.Empty, clientVisit.Secondname ?? string.Empty); row.CreateCell(8).SetValue(GetNotNullString(fio)); row.CreateCell(9).SetValue(GetDateString(clientVisit.Birthday, "dd.MM.yyyy")); row.CreateCell(10).SetValue(GetNotNullString(clientVisit.Phone)); if (!string.IsNullOrEmpty(clientVisit.Comment)) { clientVisit.Comment = clientVisit.Comment.Replace(" ", string.Empty); } row.CreateCell(11).SetValue(GetNotNullString(clientVisit.Comment)); rowNumber++; } }
protected override void Process(ISheet table) { string dateTime = string.Format("за отчетный период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString()); string numbers = null; foreach (BSOInfo b in ListBSOInfo) { numbers = numbers + b.TemporaryPolicyNumber + ", "; } table.FindCellByMacros(dateTimeConst).SetValue(dateTime); table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter); table.FindCellByMacros(numberFailConst).SetValue(ListBSOInfo.Count.ToString()); table.FindCellByMacros(numberFail1Const).SetValue(ListBSOInfo.Count.ToString()); table.FindCellByMacros(numbersConst).SetValue(numbers); table.FindCellByMacros(FIOConst).SetValue(FIO); table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString()); }
protected override void Process(ISheet table) { table.FindCellByMacros(title).SetValue("Сводный отчет по обращениям с " + DateFrom.ToShortDateString() + " по " + DateTo.ToShortDateString()); int rowNumber = 3; var VisitGroup = ClientVisits.GroupBy(a => a.VisitGroupId); List <ClientVisitInfo> clearClientVisit = new List <ClientVisitInfo>(); foreach (var item in VisitGroup) { clearClientVisit.Add(item.OrderBy(a => a.StatusDate).FirstOrDefault()); //clearClientVisit.Add(item.OrderBy(a => a.StatusDate).LastOrDefault()); } var GroupByDeliveryCenter = clearClientVisit.GroupBy(a => a.DeliveryCenter.Id); foreach (var elem in GroupByDeliveryCenter) { var GroupByDeliveryPoint = elem.OrderBy(b => b.DeliveryPoint).GroupBy(a => a.DeliveryPoint); foreach (var item in GroupByDeliveryPoint) { var GroupByUserId = item.GroupBy(a => a.UserId); foreach (var item1 in GroupByUserId) { var GroupByClientAcquisitionEmployee = item1.GroupBy(a => a.ClientAcquisitionEmployee); foreach (var item2 in GroupByClientAcquisitionEmployee) { IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetCellValue(rowNumber - 2); row.CreateCell(1).SetCellValue(item2.FirstOrDefault().DeliveryCenter.Name); row.CreateCell(2).SetCellValue(item2.FirstOrDefault().DeliveryPoint); row.CreateCell(3).SetCellValue(ListUser.Where(a => a.Id == item2.FirstOrDefault().UserId).Select(b => b.Fullname).FirstOrDefault()); row.CreateCell(4).SetCellValue(item2.FirstOrDefault().ClientAcquisitionEmployee); row.CreateCell(5).SetCellValue(item2.Count()); rowNumber++; } } } } }
protected override void Process(ISheet table) { string dateTime = string.Format("За период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString()); table.FindCellByMacros(dateTimeConst).SetValue(dateTime); table.FindCellByMacros(deliveryPointConst).SetValue(DeliveryPoint); FIO = FIO + " (" + DateTime.Now.ToShortDateString() + ")"; table.FindCellByMacros(FIOConst).SetValue(FIO); if (ChoiceSNILS == null) { table.FindCellByMacros(SNILSConst).SetValue("Все значения"); } else if (ChoiceSNILS == true) { table.FindCellByMacros(SNILSConst).SetValue("Есть"); } else { table.FindCellByMacros(SNILSConst).SetValue("Нет"); } int rowNumber = 6; foreach (var item in ListClientVisitInfo) { IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(item.Lastname); row.CreateCell(1).SetValue(item.Firstname); row.CreateCell(2).SetValue(item.Secondname); row.CreateCell(3).SetValue(item.Sex); row.CreateCell(4).SetValue(GetDateString(item.Birthday, "dd.MM.yyyy")); row.CreateCell(5).SetValue(item.Citizenship); row.CreateCell(6).SetValue(item.SNILS); row.CreateCell(7).SetValue(item.Phone); row.CreateCell(8).SetValue(item.TemporaryPolicyNumber); row.CreateCell(9).SetValue(GetDateString(item.TemporaryPolicyDate, "dd.MM.yyyy")); row.CreateCell(10).SetValue(item.Status.Name); row.CreateCell(11).SetValue(GetDateString(item.StatusDate, "dd.MM.yyyy")); row.CreateCell(12).SetValue(item.Scenario.Code); row.CreateCell(13).SetValue(item.Scenario.Name); row.CreateCell(14).SetValue(item.DeliveryCenter.Name); row.CreateCell(15).SetValue(item.DeliveryPoint); row.CreateCell(16).SetValue(ListUser.Where(a => a.Id == item.UserId).FirstOrDefault().Fullname); rowNumber++; } }
protected override void Process(ISheet table) { table.FindCellByMacros(title).SetValue(string.Format("Отчет по загрузке файла {0}", reportTitle)); int rowNumber = 3; for (int i = 0; i < reportItems.Count; i++) { ClientVisit.UpdateResultData reportItem = reportItems[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetValue(GetNotNullString(reportItem.UnifiedPolicyNumber)); row.CreateCell(2).SetValue(GetNotNullString(reportItem.Lastname)); row.CreateCell(3).SetValue(GetNotNullString(reportItem.Firstname)); row.CreateCell(4).SetValue(GetNotNullString(reportItem.Secondname)); row.CreateCell(5).SetValue(GetDateString(reportItem.Birthday, "dd.MM.yyyy")); row.CreateCell(6).SetValue(GetNotNullString(reportItem.Message)); row.CreateCell(7).SetValue(GetNotNullString(reportItem.Status != null ? reportItem.Status.Id.ToString() : string.Empty)); row.CreateCell(8).SetValue(GetNotNullString(reportItem.RECID.ToString())); row.CreateCell(9).SetValue(GetNotNullString(reportItem.Id.ToString())); row.CreateCell(10).SetValue(GetNotNullString(reportItem.ClientId.ToString())); row.CreateCell(11).SetValue(GetNotNullString(reportItem.ClientVisitGroupId.ToString())); rowNumber++; } }
protected override void Process(ISheet table) { table.FindCellByMacros(newFirstname).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Firstname)); table.FindCellByMacros(newSecondname).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Secondname)); table.FindCellByMacros(newLastname).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Lastname)); table.FindCellByMacros(oldFirstname).SetValue(GetNotNullString(ClientVisit.OldClientInfo.Firstname)); table.FindCellByMacros(oldSecondname).SetValue(GetNotNullString(ClientVisit.OldClientInfo.Secondname)); table.FindCellByMacros(oldLastname).SetValue(GetNotNullString(ClientVisit.OldClientInfo.Lastname)); table.FindCellByMacros(isNewMen).SetCheckValue(ClientVisit.NewClientInfo.Sex == "1", false); table.FindCellByMacros(isNewFemale).SetCheckValue(ClientVisit.NewClientInfo.Sex == "2", false); table.FindCellByMacros(isOldMen).SetCheckValue(ClientVisit.OldClientInfo.Sex == "1", false); table.FindCellByMacros(isOldFemale).SetCheckValue(ClientVisit.OldClientInfo.Sex == "2", false); table.FindCellByMacros(birthdate).SetValue(GetDateString(ClientVisit.NewClientInfo.Birthday, "dd/MM/yyyy")); table.FindCellByMacros(birthplace).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Birthplace)); table.FindCellByMacros(oldBirthdate).SetValue(GetDateString(ClientVisit.OldClientInfo.Birthday, "dd/MM/yyyy")); table.FindCellByMacros(oldBirthplace).SetValue(GetNotNullString(ClientVisit.OldClientInfo.Birthplace)); table.FindCellByMacros(newDocumentType).SetValue(GetNotNullString(ClientVisit.NewDocument.DocumentType != null ? ClientVisit.NewDocument.DocumentType.Name : null)); table.FindCellByMacros(newDocumentSeries).SetValue(GetNotNullString(ClientVisit.NewDocument.Series)); table.FindCellByMacros(newDocumentNumber).SetValue(GetNotNullString(ClientVisit.NewDocument.Number)); table.FindCellByMacros(newDocumentIssueDate).SetValue(GetDateString(ClientVisit.NewDocument.IssueDate, "dd/MM/yyyy")); table.FindCellByMacros(newDocumentIssueDepartment).SetValue(GetNotNullString(ClientVisit.NewDocument.IssueDepartment)); table.FindCellByMacros(enp).SetValue(GetNotNullString(ClientVisit.NewPolicy.UnifiedPolicyNumber)); table.FindCellByMacros(citizenship).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Citizenship != null ? ClientVisit.NewClientInfo.Citizenship.Name : null)); table.FindCellByMacros(citizenship).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Citizenship != null ? ClientVisit.NewClientInfo.Citizenship.Name : null)); table.FindCellByMacros(isBOMZH).SetCheckValue(false, false); if (string.IsNullOrEmpty(ClientVisit.RegistrationAddress.Region)) { table.FindCellByMacros(registrationPostIndex).SetValue(GetNotNullString(ClientVisit.LivingAddress.PostIndex)); table.FindCellByMacros(registrationArea).SetValue(GetNotNullString(ClientVisit.LivingAddress.Area)); table.FindCellByMacros(registrationRegion).SetValue(GetNotNullString(ClientVisit.LivingAddress.Region)); table.FindCellByMacros(registrationCity).SetValue(GetNotNullString(ClientVisit.LivingAddress.City)); table.FindCellByMacros(registrationLocality).SetValue(GetNotNullString(ClientVisit.LivingAddress.Locality)); table.FindCellByMacros(registrationStreet).SetValue(GetNotNullString(ClientVisit.LivingAddress.Street)); table.FindCellByMacros(registrationHouse).SetValue(GetNotNullString(ClientVisit.LivingAddress.House)); table.FindCellByMacros(registrationBuilding).SetValue( GetNotNullString( string.IsNullOrEmpty(ClientVisit.LivingAddress.Building) ? ClientVisit.LivingAddress.Housing : ClientVisit.LivingAddress.Building)); table.FindCellByMacros(registrationHousing).SetValue(GetNotNullString(ClientVisit.LivingAddress.Housing)); table.FindCellByMacros(registrationAppartment).SetValue(GetNotNullString(ClientVisit.LivingAddress.Appartment)); } else { table.FindCellByMacros(registrationPostIndex).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.PostIndex)); table.FindCellByMacros(registrationArea).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Area)); table.FindCellByMacros(registrationRegion).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Region)); table.FindCellByMacros(registrationCity).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.City)); table.FindCellByMacros(registrationLocality).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Locality)); table.FindCellByMacros(registrationStreet).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Street)); table.FindCellByMacros(registrationHouse).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.House)); table.FindCellByMacros(registrationBuilding).SetValue( GetNotNullString( string.IsNullOrEmpty(ClientVisit.RegistrationAddress.Building) ? ClientVisit.RegistrationAddress.Housing : ClientVisit.RegistrationAddress.Building)); table.FindCellByMacros(registrationHousing).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Housing)); table.FindCellByMacros(registrationAppartment).SetValue(GetNotNullString(ClientVisit.RegistrationAddress.Appartment)); } table.FindCellByMacros(registrationDate).SetValue(GetDateString(ClientVisit.RegistrationAddressDate, "dd/MM/yyyy")); table.FindCellByMacros(livingPostIndex).SetValue(GetNotNullString(ClientVisit.LivingAddress.PostIndex)); table.FindCellByMacros(livingArea).SetValue(GetNotNullString(ClientVisit.LivingAddress.Area)); table.FindCellByMacros(livingRegion).SetValue(GetNotNullString(ClientVisit.LivingAddress.Region)); table.FindCellByMacros(livingCity).SetValue(GetNotNullString(ClientVisit.LivingAddress.City)); table.FindCellByMacros(livingLocality).SetValue(GetNotNullString(ClientVisit.LivingAddress.Locality)); table.FindCellByMacros(livingStreet).SetValue(GetNotNullString(ClientVisit.LivingAddress.Street)); table.FindCellByMacros(livingHouse).SetValue(GetNotNullString(ClientVisit.LivingAddress.House)); table.FindCellByMacros(livingBuilding).SetValue( GetNotNullString( string.IsNullOrEmpty(ClientVisit.LivingAddress.Building) ? ClientVisit.LivingAddress.Housing : ClientVisit.LivingAddress.Building)); table.FindCellByMacros(livingHousing).SetValue(GetNotNullString(ClientVisit.LivingAddress.Housing)); table.FindCellByMacros(livingAppartment).SetValue(GetNotNullString(ClientVisit.LivingAddress.Appartment)); table.FindCellByMacros(foreignDocumentStartDate).SetValue(GetDateString(ClientVisit.NewForeignDocument.IssueDate, "dd/MM/yyyy")); table.FindCellByMacros(foreignDocumentExpirationDate).SetValue(GetDateString(ClientVisit.NewForeignDocument.ExpirationDate, "dd/MM/yyyy")); table.FindCellByMacros(SNILS).SetValue(GetNotNullString(ClientVisit.NewClientInfo.SNILS)); table.FindCellByMacros(phone).SetValue(GetNotNullString(ClientVisit.Phone)); table.FindCellByMacros(email).SetValue(GetNotNullString(ClientVisit.Email)); table.FindCellByMacros(representativeLastname).SetValue(GetNotNullString(ClientVisit.Representative.Lastname)); table.FindCellByMacros(representativeFirstname).SetValue(GetNotNullString(ClientVisit.Representative.Firstname)); table.FindCellByMacros(representativeSecondname).SetValue(GetNotNullString(ClientVisit.Representative.Secondname)); table.FindCellByMacros(RepresentativeDocumentType).SetValue(GetNotNullString(ClientVisit.Representative.DocumentType != null ? ClientVisit.Representative.DocumentType.Name : null)); table.FindCellByMacros(RepresentativeDocumentSeries).SetValue(GetNotNullString(ClientVisit.Representative.Series)); table.FindCellByMacros(RepresentativeDocumentNumber).SetValue(GetNotNullString(ClientVisit.Representative.Number)); table.FindCellByMacros(RepresentativeDocumentIssueDate).SetValue(GetDateString(ClientVisit.Representative.IssueDate, "dd/MM/yyyy")); string fio = string.Format("{0} {1} {2}", ClientVisit.NewClientInfo.Lastname ?? string.Empty, ClientVisit.NewClientInfo.Firstname ?? string.Empty, ClientVisit.NewClientInfo.Secondname ?? string.Empty); string fioRepresentative; if (ClientVisit.Representative.RepresentativeTypeId != 1) { fioRepresentative = string.Format("{0} {1} {2}", ClientVisit.Representative.Lastname ?? string.Empty, ClientVisit.Representative.Firstname ?? string.Empty, ClientVisit.Representative.Secondname ?? string.Empty); table.FindCellByMacros(FIO).SetValue(fioRepresentative); table.FindCellByMacros(FIO).SetValue(fioRepresentative); table.FindCellByMacros(FIO).SetValue(fioRepresentative); } else { table.FindCellByMacros(FIO).SetValue(fio); table.FindCellByMacros(FIO).SetValue(fio); table.FindCellByMacros(FIO).SetValue(fio); } table.FindCellByMacros(SignatureDate).SetValue(GetDateString(DateTime.Now, "dd/MM/yyyy")); table.FindCellByMacros(SignatureDate2).SetValue(GetDateString(DateTime.Now, "dd/MM/yyyy")); string registrator = string.Format("{0} {1} {2}", ClientVisit.Registrator.Lastname ?? string.Empty, ClientVisit.Registrator.Firstname ?? string.Empty, ClientVisit.Registrator.Secondname ?? string.Empty); table.FindCellByMacros(Registrator).SetValue(GetNotNullString(registrator)); table.FindCellByMacros(TemporaryPolicyNumber).SetValue(GetNotNullString(ClientVisit.TemporaryPolicyNumber)); table.FindCellByMacros(IssueDate).SetValue(GetDateString(ClientVisit.IssueDate, "dd/MM/yyyy")); }
protected override void Process(ISheet table) { string title = Pretension.M_nakt + " от " + GetDateString(Pretension.M_dakt, "dd.MM.yyyy") + " г."; table.FindCellByMacros(Title).SetValue(title); for (int i = 0; i < 4; i++) //4 раз выводится { table.FindCellByMacros(M_dakt).SetValue(GetDateString(Pretension.M_dakt, "dd.MM.yyyy")); } for (int i = 0; i < 2; i++) //2 раз выводится { table.FindCellByMacros(M_expert).SetValue(Pretension.M_expert); } table.FindCellByMacros(M_smo).SetValue("АО МСК \"УралСиб\""); table.FindCellByMacros(M_mcod).SetValue(Pretension.M_mcod); table.FindCellByMacros(M_mo).SetValue(Pretension.M_mo); table.FindCellByMacros(M_period).SetValue(Pretension.M_period); table.FindCellByMacros(M_snpol).SetValue(Pretension.M_snpol); for (int i = 0; i < 2; i++) //2 раз выводится { table.FindCellByMacros(M_fd).SetValue(Pretension.M_fd.Replace(" ", " ")); } string All_M_nd; string Confirm1; string Confirm2; if (Pretension.IsConfirm.HasValue) { if (Pretension.IsConfirm == true) { Confirm1 = " представлено. "; Confirm2 = " обоснованным. "; } else { Confirm1 = " не представлено. "; Confirm2 = " необоснованным. "; } } else { Confirm1 = " не выбрано! "; Confirm2 = " не выбрано! "; } All_M_nd = Pretension.M_nd1 + Confirm1 + Pretension.M_nd2 + Confirm2; table.FindCellByMacros(M_nd).SetValue(All_M_nd.Replace(" ", " ")); string osn230 = Pretension.M_osn230Ref.Where(a => a.Id == Pretension.M_osn230_Id).Select(b => b.Code).FirstOrDefault(); string err = Pretension.M_osn230Ref.Where(a => a.Id == Pretension.M_osn230_Id).Select(b => b.ErrCode).FirstOrDefault(); double?straf = (double?)Pretension.M_straf; double?sum = straf * (double?)Pretension.Coefficient; if (Pretension.IsConfirm == true) { osn230 = string.Empty; err = string.Empty; straf = null; sum = null; } table.FindCellByMacros(M_osn230).SetValue(osn230); table.FindCellByMacros(M_er_c).SetValue(err); for (int i = 0; i < 1; i++) //1 раз выводится { if (straf.HasValue) { table.FindCellByMacros(M_straf).SetCellValue(Math.Round((double)straf, 2)); } } for (int i = 0; i < 3; i++)// 3 раза выводится { if (sum.HasValue) { table.FindCellByMacros(Sum).SetCellValue(Math.Round((double)sum, 2)); } } }
protected override void Process(ISheet table) { table.FindCellByMacros(Generator_M_dakt).SetValue("Предписание № " + string.Format("{0,4:D4}", Pretension.Generator) + " от " + GetDateString(Pretension.M_dakt, "dd.MM.yyyy") + " г."); table.FindCellByMacros(M_mo).SetValue(Pretension.M_mo); table.FindCellByMacros(M_mcod_M_mo).SetValue(Pretension.M_mcod + " " + Pretension.M_mo); table.FindCellByMacros(PeriodFrom).SetValue(GetDateString(Pretension.PeriodFrom, "dd.MM.yyyy")); table.FindCellByMacros(PeriodTo).SetValue(GetDateString(Pretension.PeriodTo, "dd.MM.yyyy")); table.FindCellByMacros(M_nakt).SetValue(Pretension.M_nakt); table.FindCellByMacros(MCOD).SetValue("В соответствии с Договором на оказание и оплату медицинской помощи по обязательному медицинскому страхованию № " + Pretension.MCOD + ".23 от 29 декабря 2015 г."); for (int i = 0; i < 3; i++)// 3 раза выводится { table.FindCellByMacros(M_dakt).SetValue(GetDateString(Pretension.M_dakt, "dd.MM.yyyy")); } string osn230 = Pretension.M_osn230Ref.Where(a => a.Id == Pretension.M_osn230_Id).Select(b => b.Code).FirstOrDefault(); double?straf = (double?)Pretension.M_straf; double?sum = straf * (double?)Pretension.Coefficient; if (Pretension.IsConfirm == true) { osn230 = string.Empty; straf = null; Pretension.Coefficient = null; sum = null; } table.FindCellByMacros(M_osn230).SetValue(osn230); if (Pretension.Coefficient.HasValue) { table.FindCellByMacros(Coefficient).SetCellValue(Math.Round((double)Pretension.Coefficient, 2)); } for (int i = 0; i < 3; i++)// 3 раза выводится { if (sum.HasValue) { table.FindCellByMacros(Sum).SetCellValue(Math.Round((double)sum, 2)); } } table.FindCellByMacros(UserPosition_UserFIO).SetValue(Pretension.UserPosition + ", " + Pretension.UserFIO); }
protected override void Process(ISheet table) { table.FindCellByMacros(SMOName).SetValue(GetNotNullString(ClientVisit.DeliveryCenter.DisplayName.Trim() + string.Format(" \"P2{0}\"", ClientVisit.DeliveryCenter.Code ?? string.Empty))); table.FindCellByMacros(isMen).SetCheckValue(ClientVisit.NewClientInfo.Sex == "1", false); table.FindCellByMacros(isWoman).SetCheckValue(ClientVisit.NewClientInfo.Sex == "2", false); string addressAndPhoneString = string.Format("{0}, тел. {1}", string.IsNullOrEmpty(ClientVisit.DeliveryCenter.Address) ? string.Empty : ClientVisit.DeliveryCenter.Address.Trim(), ClientVisit.DeliveryCenter.Phone ?? string.Empty); table.FindCellByMacros(addressAndPhone).SetValue(GetNotNullString(addressAndPhoneString.Trim())); table.FindCellByMacros(birthplace).SetValue(GetNotNullString(ClientVisit.NewClientInfo.Birthplace)); if (ClientVisit.TemporaryPolicyDate.HasValue) { table.FindCellByMacros(issueDateDay).SetValue(GetNotNullString(GetTwoCharactersDate(ClientVisit.TemporaryPolicyDate.Value.Day))); table.FindCellByMacros(issueDateMonth).SetValue(GetNotNullString(GetTwoCharactersDate(ClientVisit.TemporaryPolicyDate.Value.Month))); table.FindCellByMacros(issueDateYear).SetValue(GetNotNullString(ClientVisit.TemporaryPolicyDate.Value.Year.ToString())); } else { table.FindCellByMacros(issueDateDay).SetValue(string.Empty); table.FindCellByMacros(issueDateMonth).SetValue(string.Empty); table.FindCellByMacros(issueDateYear).SetValue(string.Empty); } if (ClientVisit.TemporaryPolicyExpirationDate.HasValue) { table.FindCellByMacros(ExpDay).SetValue(GetNotNullString(GetTwoCharactersDate(ClientVisit.TemporaryPolicyExpirationDate.Value.Day))); table.FindCellByMacros(ExpM).SetValue(GetNotNullString(GetTwoCharactersDate(ClientVisit.TemporaryPolicyExpirationDate.Value.Month))); table.FindCellByMacros(ExpY).SetValue(GetNotNullString(ClientVisit.TemporaryPolicyExpirationDate.Value.Year.ToString())); } else { table.FindCellByMacros(ExpDay).SetValue(string.Empty); table.FindCellByMacros(ExpM).SetValue(string.Empty); table.FindCellByMacros(ExpY).SetValue(string.Empty); } string fio = string.Format("{0} {1} {2}", ClientVisit.NewClientInfo.Lastname ?? string.Empty, ClientVisit.NewClientInfo.Firstname ?? string.Empty, ClientVisit.NewClientInfo.Secondname ?? string.Empty); table.FindCellByMacros(FIO).SetValue(fio); string docInfo = string.Format("{0}, {1} {2} {3}, выдан {4}", GetDateString(ClientVisit.NewClientInfo.Birthday, "dd-MM-yyyy"), ClientVisit.NewDocument.DocumentType.Name, ClientVisit.NewDocument.Series, ClientVisit.NewDocument.Number, GetDateString(ClientVisit.NewDocument.IssueDate, "dd-MM-yyyy")); table.FindCellByMacros(BithdayDocTypeDocSeriesNumberIssueDate).SetValue(docInfo); table.FindCellByMacros(DocumentIssueDepartment).SetValue(GetNotNullString(ClientVisit.NewDocument.IssueDepartment)); string registrator = string.Format("{0} {1} {2}", ClientVisit.Registrator.Lastname ?? string.Empty, ClientVisit.Registrator.Firstname ?? string.Empty, ClientVisit.Registrator.Secondname ?? string.Empty); table.FindCellByMacros(Registrator).SetValue(GetNotNullString(registrator)); }
//заполняем шапку private static void FillTemplate(ISheet table, int year) { table.FindCellByMacros(B1).SetValue(string.Format("Остаток бланков временных свидетельств на {0} г.", new DateTime(year, 1, 1).ToShortDateString())); for (int i = 0; i < 2; i++) { table.FindCellByMacros(Months1).SetCellValue(new DateTime(year, 1, 1)); table.FindCellByMacros(Months2).SetCellValue(new DateTime(year, 2, 1)); table.FindCellByMacros(Months3).SetCellValue(new DateTime(year, 3, 1)); table.FindCellByMacros(Months4).SetCellValue(new DateTime(year, 4, 1)); table.FindCellByMacros(Months5).SetCellValue(new DateTime(year, 5, 1)); table.FindCellByMacros(Months6).SetCellValue(new DateTime(year, 6, 1)); table.FindCellByMacros(Months7).SetCellValue(new DateTime(year, 7, 1)); table.FindCellByMacros(Months8).SetCellValue(new DateTime(year, 8, 1)); table.FindCellByMacros(Months9).SetCellValue(new DateTime(year, 9, 1)); table.FindCellByMacros(Months10).SetCellValue(new DateTime(year, 10, 1)); table.FindCellByMacros(Months11).SetCellValue(new DateTime(year, 11, 1)); table.FindCellByMacros(Months12).SetCellValue(new DateTime(year, 12, 1)); table.FindCellByMacros(FilialMonths12).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year + 1, 1, 1).ToShortDateString())); } table.FindCellByMacros(FilialMonths1).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 1 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths2).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 2 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths3).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 3 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths4).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 4 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths5).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 5 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths6).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 6 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths7).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 7 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths8).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 8 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths9).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 9 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths10).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 10 + 1, 1).ToShortDateString())); table.FindCellByMacros(FilialMonths11).SetValue(string.Format("остаток в филиалах на {0}", new DateTime(year, 11 + 1, 1).ToShortDateString())); table.FindCellByMacros(totalYear1).SetValue(string.Format("Итого за {0} год", year)); table.FindCellByMacros(totalYear2).SetValue(string.Format("получено из Дирекции за {0} г.", year)); table.FindCellByMacros(issued).SetValue(string.Format("выдано за {0} г. ", year)); table.FindCellByMacros(ruined).SetValue(string.Format("испорчено за {0} г.", year)); }
protected override void Process(ISheet table) { //cсохранем низ документа int t = 47; IRow rowTemp1 = table.GetRow(t); IRow rowTemp2 = table.GetRow(t + 3); IRow rowTemp3 = table.GetRow(t + 4); IRow rowTemp4 = table.GetRow(t + 6); IRow rowTemp5 = table.GetRow(t + 7); IRow rowTemp6 = table.GetRow(t + 10); IRow rowTemp7 = table.GetRow(t + 11); IRow rowTemp8 = table.GetRow(t + 13); IRow rowTemp9 = table.GetRow(t + 14); IRow rowTemp10 = table.GetRow(t + 16); string dateTime = string.Format("за отчетный период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString()); table.FindCellByMacros(dateTimeConst).SetValue(dateTime); table.FindCellByMacros(deliveryPointConst).SetValue(DeliveryPoint); int rowNumber = 14; foreach (var element in ListBSOInfo) { IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(element.TemporaryPolicyNumber); row.CreateCell(1).SetValue(element.Status.Name); row.CreateCell(2).SetValue(((DateTime)element.StatusDate).ToShortDateString()); row.CreateCell(3).SetValue(element.ResponsibleName); row.CreateCell(4).SetValue(element.DeliveryCenter); row.CreateCell(5).SetValue(element.DeliveryPoint); rowNumber++; } if (rowNumber < 45) { table.FindCellByMacros(FIOConst).SetValue(FIO); table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString()); } else { //выводим сохраненный низ документа rowNumber = rowNumber + 2; IRow row1 = table.CreateRow(rowNumber); row1.CreateCell(0).SetValue(rowTemp1.GetCell(0).StringCellValue); rowNumber = rowNumber + 3; IRow row2 = table.CreateRow(rowNumber); row2.CreateCell(0).SetValue(rowTemp2.GetCell(0).StringCellValue); rowNumber = rowNumber + 1; IRow row3 = table.CreateRow(rowNumber); row3.CreateCell(4).SetValue(rowTemp3.GetCell(4).StringCellValue); rowNumber = rowNumber + 2; IRow row4 = table.CreateRow(rowNumber); row4.CreateCell(1).SetValue(rowTemp4.GetCell(1).StringCellValue); row4.CreateCell(4).SetValue(rowTemp4.GetCell(4).StringCellValue); rowNumber = rowNumber + 1; IRow row5 = table.CreateRow(rowNumber); row5.CreateCell(2).SetValue(rowTemp5.GetCell(2).StringCellValue); row5.CreateCell(5).SetValue(rowTemp5.GetCell(5).StringCellValue); rowNumber = rowNumber + 3; IRow row6 = table.CreateRow(rowNumber); row6.CreateCell(0).SetValue(rowTemp6.GetCell(0).StringCellValue); rowNumber = rowNumber + 1; IRow row7 = table.CreateRow(rowNumber); row7.CreateCell(4).SetValue(rowTemp7.GetCell(4).StringCellValue); rowNumber = rowNumber + 3; IRow row8 = table.CreateRow(rowNumber); row8.CreateCell(1).SetValue(rowTemp8.GetCell(1).StringCellValue); row8.CreateCell(4).SetValue(FIO); rowNumber = rowNumber + 1; IRow row9 = table.CreateRow(rowNumber); row9.CreateCell(2).SetValue(rowTemp9.GetCell(2).StringCellValue); row9.CreateCell(5).SetValue(rowTemp9.GetCell(5).StringCellValue); rowNumber = rowNumber + 2; IRow row10 = table.CreateRow(rowNumber); row10.CreateCell(0).SetValue(DateTime.Now.ToShortDateString()); } }
protected override void Process(ISheet table) { table.FindCellByMacros(title).SetValue("Отчет по обработанным записям. Дата обработки (загрузки): " + NompHistory.LoadDate.ToString()); table.FindCellByMacros(dateTimeNOMP).SetValue(string.Format("Номерник: {0:Y}", NompHistory.FileDate)); table.FindCellByMacros(countAll).SetValue(NompHistory.CountAll.ToString()); table.FindCellByMacros(countOur).SetValue(NompHistory.CountOur.ToString()); int[] countStatus = new int[6] { 0, 0, 0, 0, 0, 0 }; int rowNumber = 12; for (int i = 0; i < ListNOMP.Count; i++) { var nomp = ListNOMP[i]; IRow row = table.CreateRow(rowNumber); row.CreateCell(0).SetValue(GetNotNullString((i + 1).ToString())); row.CreateCell(1).SetValue(GetNotNullString(nomp.S_CARD)); row.CreateCell(2).SetValue(GetNotNullString(nomp.N_CARD.ToString())); row.CreateCell(3).SetValue(GetNotNullString(nomp.ENP)); row.CreateCell(4).SetValue(GetNotNullString(nomp.VSN)); row.CreateCell(5).SetValue(GetNotNullString(nomp.LPU_ID.ToString())); row.CreateCell(6).SetValue(GetDateString(nomp.DATE_IN, "dd.MM.yyyy")); row.CreateCell(7).SetValue(GetNotNullString(nomp.SPOS.ToString())); row.CreateCell(8).SetValue(GetNotNullString(nomp.Status.ToString())); string StatusMessage; if (nomp.Status == 1) { StatusMessage = "Ошибка, клиент отсутствует (не найден)"; } else if (nomp.Status == 2) { StatusMessage = "Ошибка, найдено более 1 клиента"; } else if (nomp.Status == 3) { StatusMessage = "Данные в системе актуальны"; } else if (nomp.Status == 4) { StatusMessage = "Данные обновлены у клиента"; } else if (nomp.Status == 5) { StatusMessage = "Данные загружены клиенту"; } else { StatusMessage = "Без статуса"; } row.CreateCell(9).SetValue(StatusMessage); row.CreateCell(10).SetValue(GetNotNullString(nomp.ClientID.ToString())); row.CreateCell(11).SetValue(GetNotNullString(nomp.Comment)); countStatus[nomp.Status]++; rowNumber++; } table.FindCellByMacros(_1).SetValue(countStatus[1].ToString()); table.FindCellByMacros(_2).SetValue(countStatus[2].ToString()); table.FindCellByMacros(_3).SetValue(countStatus[3].ToString()); table.FindCellByMacros(_4).SetValue(countStatus[4].ToString()); table.FindCellByMacros(_5).SetValue(countStatus[5].ToString()); table.FindCellByMacros(sum).SetValue(NompHistory.CountOur.ToString()); }
protected override void Process(ISheet table) { int rowNumber = 16; int rowNumberDown = 27; string dateTime = string.Format("за отчетный период с {0} г. по {1} г.", DateTimeFrom.ToShortDateString(), DateTimeTo.ToShortDateString()); table.FindCellByMacros(dateTimeConst).SetValue(dateTime); table.FindCellByMacros(deliveryPointConst).SetValue(DeliveryPoint); var ListHistoryGroupByBSO_IDs = ListHistory.GroupBy(a => a.Id); foreach (var item in ListHistoryGroupByBSO_IDs) { BSOHistoryItem temp = item.OrderBy(a => a.StatusDate).Where(b => b.Status.Id == (long)ListBSOStatusID.OnDelivery || b.Status.Id == (long)ListBSOStatusID.OnResponsible).LastOrDefault(); if (temp != null) { if (temp.StatusDate < DateTimeFrom) { BSOInfo bsoInfo = new BSOInfo() { TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(), //TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(), Status = new BSOStatusRef() { Id = temp.Status.Id } }; if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber)) { ListBSOBeforeDate.Add(bsoInfo); } } else { BSOInfo bsoInfo = new BSOInfo() { TemporaryPolicyNumber = ListBSOinRangeDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(), //TemporaryPolicyNumber = ListBSOAllDate.Where(a => a.Id == temp.Id).Select(b => b.TemporaryPolicyNumber).LastOrDefault(), Status = new BSOStatusRef() { Id = temp.Status.Id } }; if (!string.IsNullOrEmpty(bsoInfo.TemporaryPolicyNumber)) { ListBSOGet.Add(bsoInfo); } } } } Dictionary <string, long> dicBefore = SortingAndGroup(ListBSOBeforeDate, new List <long>() { (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible }); Dictionary <string, long> dicRange = SortingAndGroup(ListBSOGet, new List <long>() { (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible }); Dictionary <string, long> dicAll = SortingAndGroup(ListBSOAllDate, new List <long>() { (long)ListBSOStatusID.OnDelivery, (long)ListBSOStatusID.OnResponsible }); Dictionary <string, long> dicOnClient = SortingAndGroup(ListBSOinRangeDate, new List <long>() { (long)ListBSOStatusID.OnClient }); Dictionary <string, long> dicFail = SortingAndGroup(ListBSOinRangeDate, new List <long>() { (long)ListBSOStatusID.FailOnStorage, (long)ListBSOStatusID.FailOnResponsible, (long)ListBSOStatusID.FailGotoStorage }); //определяем в каком словаре максимальное кол-во элементов, для того чтоб насоздавать строки List <int> countDictionary = new List <int>(); countDictionary.Add(dicBefore == null ? 0 : dicBefore.Count); countDictionary.Add(dicRange == null ? 0 : dicRange.Count); countDictionary.Add(dicAll == null ? 0 : dicAll.Count); countDictionary.Add(dicOnClient == null ? 0 : dicOnClient.Count); countDictionary.Add(dicFail == null ? 0 : dicFail.Count); var max = countDictionary.Max() / 2; //если много данных - двигаем последние строчки еще ниже if (max > 10) { for (int i = 0; i < 10; i++) { table.CopyRow(i + rowNumberDown, i + rowNumberDown + max); } } ICellStyle style = table.Workbook.CreateCellStyle(); style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; List <IRow> rows = new List <IRow>(); for (int i = 0; i < countDictionary.Max() / 2; i++) { IRow row = table.CreateRow(i + rowNumber); for (int j = 0; j <= 14; j++) { row.CreateCell(j).CellStyle = style; } rows.Add(row); } Output(dicBefore, rows, 0); Output(dicRange, rows, 3); Output(dicOnClient, rows, 6); Output(dicFail, rows, 9); Output(dicAll, rows, 12); //Итого: IRow rowSum = table.CreateRow(max + rowNumber); rowSum.CreateCell(0).SetValue("Итого:"); rowSum.CreateCell(2).SetCellFormula(string.Format("SUM(C{0}:C{1})", rowNumber + 1, rowNumber + max)); rowSum.CreateCell(5).SetCellFormula(string.Format("SUM(F{0}:F{1})", rowNumber + 1, rowNumber + max)); rowSum.CreateCell(8).SetCellFormula(string.Format("SUM(I{0}:I{1})", rowNumber + 1, rowNumber + max)); rowSum.CreateCell(11).SetCellFormula(string.Format("SUM(L{0}:L{1})", rowNumber + 1, rowNumber + max)); rowSum.CreateCell(14).SetCellFormula(string.Format("SUM(O{0}:O{1})", rowNumber + 1, rowNumber + max)); table.FindCellByMacros(FIOConst).SetValue(FIO); table.FindCellByMacros(PositionConst).SetValue(Position); table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString()); }
protected override void Process(ISheet table) { int rowFIO = table.FindCellByMacros(FIOConst).RowIndex; int rowNumbers = table.FindCellByMacros(numbersConst).RowIndex; int colNumbers = table.FindCellByMacros(numbersConst).ColumnIndex; int rowPrice = table.FindCellByMacros(SUM).RowIndex; double price; try { IRow r = table.GetRow(rowPrice); price = r.GetCell(table.FindCellByMacros(SUM).ColumnIndex - 1).NumericCellValue; } catch { price = 0.0; } table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter); table.FindCellByMacros(deliveryCenterConst).SetValue(DeliveryCenter); table.FindCellByMacros(nowDateTimeConst).SetValue(DateTime.Now.ToShortDateString()); table.FindCellByMacros(FIOConst).SetValue(FIO); table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString()); table.FindCellByMacros(Count).SetValue(ListBsoNumber.Count.ToString()); int rowIndex = table.FindCellByMacros(SUM).RowIndex; int columnIndex = table.FindCellByMacros(numbersConst).ColumnIndex; if (ListBsoNumber.Count <= 1) { table.FindCellByMacros(SUM).SetValue((price * ListBsoNumber.Count).ToString()); rowIndex = table.FindCellByMacros(numbersConst).RowIndex; table.SetCellValue(rowIndex, columnIndex, ListBsoNumber.FirstOrDefault()); } else { List <long> listLong = new List <long>(ListBsoNumber.Count); try { //создаем список чисел (парсим номера БСО в long) foreach (var elem in ListBsoNumber) { listLong.Add(long.Parse(elem)); } //сортируем listLong.Sort((a, b) => a.CompareTo(b)); //создаем сортированный список БСО List <string> newListBsoNumber = new List <string>(listLong.Count); foreach (long l in listLong) { newListBsoNumber.Add(string.Format("{0,9:D9}", l)); } //группируем по диапазонам Dictionary <string, long> dic = new Dictionary <string, long>(); long temp = listLong.FirstOrDefault(); long count = 0; dic.Add("с " + newListBsoNumber.FirstOrDefault(), 0); for (int i = 0; i < listLong.Count; i++) { if (listLong[i] - temp > 1) { dic.Add("по " + newListBsoNumber[i - 1], count); dic.Add("с " + newListBsoNumber[i], 0); count = 0; } temp = listLong[i]; count++; } dic.Add("по " + newListBsoNumber.LastOrDefault(), count); //если много данных - двигаем 2 последние строчки еще ниже if (dic.Count > 10) { table.CopyRow(rowFIO, rowFIO + dic.Count - 9); table.CopyRow(rowFIO + 1, rowFIO + dic.Count - 8); table.CreateRow(rowFIO); table.CreateRow(rowFIO + 1); } ICellStyle style = table.Workbook.CreateCellStyle(); style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; //выводим весь этот колохоз int r = rowNumbers; foreach (var element in dic) { IRow row = table.CreateRow(r); row.CreateCell(0).CellStyle = style; row.CreateCell(1).CellStyle = style; row.CreateCell(2).CellStyle = style; row.CreateCell(3).CellStyle = style; row.CreateCell(4).CellStyle = style; row.CreateCell(5).CellStyle = style; row.CreateCell(6).CellStyle = style; row.CreateCell(7).CellStyle = style; row.CreateCell(8).CellStyle = style; row.CreateCell(9).CellStyle = style; row.CreateCell(10).CellStyle = style; row.GetCell(3).SetValue(element.Key); if (element.Value > 0) {//2ая строка в группе row.GetCell(2).SetValue("Временные свидетельства"); row.GetCell(2).CellStyle = style; row.GetCell(5).SetValue("шт."); row.GetCell(6).SetValue(element.Value.ToString()); row.GetCell(7).SetValue(element.Value.ToString()); row.GetCell(8).SetValue(price.ToString()); row.GetCell(9).SetValue((price * element.Value).ToString()); } else { //1ая строка } r++; } } catch { } } }
protected override void Process(ISheet table) { int rowNumber = 6; table.FindCellByMacros(titleConst).SetValue("Отчет по возможным дубликатам клиентов " + DateTime.Now.ToShortDateString()); if (ListCheckClient == null) { table.FindCellByMacros(countConst).SetCellValue("Дубликатов нет!"); return; } else { table.FindCellByMacros(countConst).SetCellValue(ListCheckClient.Count()); } table.FindCellByMacros(countDuplicateConst).SetValue(GetNotNullString(CountDublicate.ToString())); table.FindCellByMacros(textCheckConst).SetValue(GetNotNullString(TextCheck)); //создаем стиль для шапки таблицы ICellStyle style = table.Workbook.CreateCellStyle(); style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; IFont font = table.Workbook.CreateFont(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; style.SetFont(font); //заполняем шапку таблицы IRow rowHeadTable = table.CreateRow(rowNumber - 1); int colHeadTable = 0; if (ViewColumn.IsId) { rowHeadTable.CreateCell(colHeadTable).SetCellValue("ClientId"); colHeadTable++; } if (ViewColumn.IsLastname) { rowHeadTable.CreateCell(colHeadTable).SetValue("Фамилия"); colHeadTable++; } if (ViewColumn.IsFirstname) { rowHeadTable.CreateCell(colHeadTable).SetValue("Имя"); colHeadTable++; } if (ViewColumn.IsSecondname) { rowHeadTable.CreateCell(colHeadTable).SetValue("Отчество"); colHeadTable++; } if (ViewColumn.IsBirthday) { rowHeadTable.CreateCell(colHeadTable).SetValue("Дата рождения"); colHeadTable++; } if (ViewColumn.IsSex) { rowHeadTable.CreateCell(colHeadTable).SetValue("Пол"); colHeadTable++; } if (ViewColumn.IsPolicySeries) { rowHeadTable.CreateCell(colHeadTable).SetValue("Серия полиса"); colHeadTable++; } if (ViewColumn.IsPolicyNumber) { rowHeadTable.CreateCell(colHeadTable).SetValue("Номер полиса"); colHeadTable++; } if (ViewColumn.IsUnifiedPolicyNumber) { rowHeadTable.CreateCell(colHeadTable).SetValue("ЕНП"); colHeadTable++; } if (ViewColumn.IsDocumentSeries) { rowHeadTable.CreateCell(colHeadTable).SetValue("Серия паспорта"); colHeadTable++; } if (ViewColumn.IsDocumentNumber) { rowHeadTable.CreateCell(colHeadTable).SetValue("Номер паспорта"); colHeadTable++; } if (ViewColumn.IsLivingFullAddressString) { rowHeadTable.CreateCell(colHeadTable).SetValue("Адрес проживания"); colHeadTable++; } if (ViewColumn.IsOfficialFullAddressString) { rowHeadTable.CreateCell(colHeadTable).SetValue("Адрес регистрации"); colHeadTable++; } if (ViewColumn.IsTemporaryPolicyNumber) { rowHeadTable.CreateCell(colHeadTable).SetValue("ВС(БСО)"); colHeadTable++; } if (ViewColumn.IsTemporaryPolicyDate) { rowHeadTable.CreateCell(colHeadTable).SetValue("Дата обращения"); colHeadTable++; } if (ViewColumn.IsSNILS) { rowHeadTable.CreateCell(colHeadTable).SetValue("СНИЛС"); colHeadTable++; } if (ViewColumn.IsPhone) { rowHeadTable.CreateCell(colHeadTable).SetValue("Телефон"); colHeadTable++; } for (int i = 0; i < ListCheckClient.Count; i++) { int col = 0; var check = ListCheckClient[i]; IRow row = table.CreateRow(rowNumber); if (ViewColumn.IsId) { row.CreateCell(col).SetCellValue(check.Id); col++; } if (ViewColumn.IsLastname) { row.CreateCell(col).SetValue(GetNotNullString(check.Lastname)); col++; } if (ViewColumn.IsFirstname) { row.CreateCell(col).SetValue(GetNotNullString(check.Firstname)); col++; } if (ViewColumn.IsSecondname) { row.CreateCell(col).SetValue(GetNotNullString(check.Secondname)); col++; } if (ViewColumn.IsBirthday) { row.CreateCell(col).SetValue(GetDateString(check.Birthday, "dd.MM.yyyy")); col++; } if (ViewColumn.IsSex) { row.CreateCell(col).SetValue(GetNotNullString(check.Sex)); col++; } if (ViewColumn.IsPolicySeries) { row.CreateCell(col).SetValue(GetNotNullString(check.PolicySeries)); col++; } if (ViewColumn.IsPolicyNumber) { row.CreateCell(col).SetValue(GetNotNullString(check.PolicyNumber)); col++; } if (ViewColumn.IsUnifiedPolicyNumber) { row.CreateCell(col).SetValue(GetNotNullString(check.UnifiedPolicyNumber)); col++; } if (ViewColumn.IsDocumentSeries) { row.CreateCell(col).SetValue(GetNotNullString(check.DocumentSeries)); col++; } if (ViewColumn.IsDocumentNumber) { row.CreateCell(col).SetValue(GetNotNullString(check.DocumentNumber)); col++; } if (ViewColumn.IsLivingFullAddressString) { row.CreateCell(col).SetValue(GetNotNullString(check.LivingFullAddressString)); col++; } if (ViewColumn.IsOfficialFullAddressString) { row.CreateCell(col).SetValue(GetNotNullString(check.OfficialFullAddressString)); col++; } if (ViewColumn.IsTemporaryPolicyNumber) { row.CreateCell(col).SetValue(GetNotNullString(check.TemporaryPolicyNumber)); col++; } if (ViewColumn.IsTemporaryPolicyDate) { row.CreateCell(col).SetValue(GetDateString(check.TemporaryPolicyDate, "dd.MM.yyyy")); col++; } if (ViewColumn.IsSNILS) { row.CreateCell(col).SetValue(GetNotNullString(check.SNILS)); col++; } if (ViewColumn.IsPhone) { row.CreateCell(col).SetValue(GetNotNullString(check.Phone)); col++; } rowNumber++; } //применяем стиль для шапки таблицы for (int i = 0; i < colHeadTable; i++) { rowHeadTable.GetCell(i).CellStyle = style; } for (int i = 2; i < colHeadTable; i++) { table.AutoSizeColumn(i); } }