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);
}
}