public static string WriteToExcel(DataTable dataTable, string resultFilePrefix, string templateFileName)
{
if (!CreateNewIWorkbook(resultFilePrefix, templateFileName, out IWorkbook workbook, out ISheet sheet, out string resultFile, string.Empty))
{
return(string.Empty);
}
List <ItemTreatment> treatments = ParseDataTable(dataTable);
int rowNumber = 1;
int columnNumber = 0;
foreach (ItemTreatment treatment in treatments)
{
List <object> valuesTreatment = new List <object>()
{
treatment.Treatdate,
treatment.Filial,
treatment.Treatcode,
treatment.Client,
treatment.Pcode,
treatment.Histnum,
treatment.Phone,
treatment.TreatType,
treatment.Depname,
treatment.Dcode,
treatment.Doc
};
int serviceCountResult = 0;
int serviceQuantityResult = 0;
double serviceAmountResult = 0;
int serviceRow = rowNumber;
for (int treSer = 0; treSer < treatment.Services.Count; treSer++)
{
ItemService service = treatment.Services[treSer];
serviceCountResult += service.Count;
serviceQuantityResult++;
serviceAmountResult += service.Amount;
List <object> valuesService = new List <object>()
{
service.Name,
service.Kodoper,
service.Count,
service.Amount
};
List <object> valuesToWrite = new List <object>();
valuesToWrite.AddRange(valuesTreatment);
valuesToWrite.AddRange(valuesService);
if (treSer > 0)
{
valuesToWrite[2] = null;
valuesToWrite[4] = null;
valuesToWrite[9] = null;
}
WriteOutRow(valuesToWrite, sheet, serviceRow, columnNumber);
serviceRow++;
}
int referralQuantityResult = 0;
int referralServiceQuantityResult = 0;
int referralServiceCountResult = 0;
double referralServiceAmountResult = 0;
int referralScheduleQuantityResult = 0;
int referralTreatQuantityResult = 0;
int referralTreatServiceQuantityResult = 0;
int referralTreatServiceCountResult = 0;
double referralTreatServiceAmountResult = 0;
int referralRow = rowNumber;
for (int treRef = 0; treRef < treatment.Referrals.Count; treRef++)
{
ItemReferral referral = treatment.Referrals.ElementAt(treRef).Value;
referralQuantityResult++;
List <long> treatcodesByReferrals = new List <long>();
for (int refSer = 0; refSer < referral.Services.Count; refSer++)
{
ItemService service = referral.Services[refSer];
referralServiceQuantityResult++;
referralServiceCountResult += service.Count;
referralServiceAmountResult += service.Amount;
List <object> valuesToWrite = new List <object>();
valuesToWrite.AddRange(valuesTreatment);
valuesToWrite.AddRange(new List <object> {
null,
null,
null,
null
});
List <object> valuesService = new List <object> {
referral.RefType,
refSer == 0 ? referral.Refid.ToString() : null,
service.Name,
service.Kodoper,
service.Count,
service.Amount
};
if (referral.Schedule is null)
{
valuesService.AddRange(new List <object> {
"Отсутствует",
null,
null,
null,
null,
null,
null,
null
});
}
else
{
referralScheduleQuantityResult++;
valuesService.AddRange(new List <object> {
referral.Schedule.Workdate,
refSer == 0 ? referral.Schedule.Schedid.ToString() : null,
refSer == 0 ? treatment.Pcode.ToString() : null,
referral.Schedule.Doc,
referral.Schedule.DocPost,
referral.Schedule.AuthorFilial,
referral.Schedule.AuthorName,
referral.Schedule.AuthorPost
});
}
if (referral.Treat is null)
{
valuesService.AddRange(new List <object> {
"Нет",
null,
null,
null,
null,
null,
null,
null
});
}
else
{
if (treatcodesByReferrals.Contains(referral.Treat.Treatcode))
{
valuesService.AddRange(new List <object> {
"Да",
referral.Treat.Treatdate,
null,
null
});
}
else
{
valuesService.AddRange(new List <object> {
"Да",
referral.Treat.Treatdate,
referral.Treat.Treatcode,
treatment.Pcode
});
referralTreatQuantityResult++;
treatcodesByReferrals.Add(referral.Treat.Treatcode);
}
foreach (ItemService treatService in referral.Treat.Services)
{
if (treatService.IsUsed)
{
continue;
}
if (!treatService.Name.Equals(service.Name))
{
continue;
}
referralTreatServiceQuantityResult++;
referralTreatServiceCountResult += treatService.Count;
referralTreatServiceAmountResult += treatService.Amount;
valuesService.AddRange(new List <object> {
treatService.Name,
treatService.Kodoper,
treatService.Count,
treatService.Amount
});
valuesService.Add(
((double)treatService.Amount / (double)service.Amount * 100.0d)
.ToString("N2", CultureInfo.CurrentCulture));
treatService.IsUsed = true;
break;
}
}
valuesToWrite.AddRange(valuesService);
if (treRef > 0)
{
valuesToWrite[2] = null;
valuesToWrite[4] = null;
valuesToWrite[9] = null;
}
WriteOutRow(valuesToWrite, sheet, referralRow, columnNumber);
referralRow++;
}
}
columnNumber = 0;
if (serviceRow != rowNumber || referralRow != rowNumber)
{
rowNumber = serviceRow > referralRow ? serviceRow : referralRow;
}
else
{
WriteOutRow(valuesTreatment, sheet, rowNumber, columnNumber);
rowNumber++;
}
List <object> valuesToWriteResult = new List <object>();
valuesToWriteResult.AddRange(valuesTreatment);
valuesToWriteResult.Add("Итого по услугам");
valuesToWriteResult.Add(serviceQuantityResult);
valuesToWriteResult.Add(serviceCountResult);
valuesToWriteResult.Add(serviceAmountResult);
valuesToWriteResult.Add("Итого по направлениям");
valuesToWriteResult.Add(referralQuantityResult);
valuesToWriteResult.Add(null);
valuesToWriteResult.Add(referralServiceQuantityResult);
valuesToWriteResult.Add(referralServiceCountResult);
valuesToWriteResult.Add(referralServiceAmountResult);
valuesToWriteResult.Add("Итого по назначениям");
valuesToWriteResult.Add(referralScheduleQuantityResult);
valuesToWriteResult.AddRange(new List <object> {
null,
null,
null,
null,
null,
null
});
string serviceResult = "Нет услуг в направлениях";
if (referralServiceCountResult > 0)
{
serviceResult = ((double)referralTreatServiceCountResult / (double)referralServiceCountResult * 100.0)
.ToString("N2", CultureInfo.CurrentCulture) + "% - исполнения (по услугам)";
}
valuesToWriteResult.Add(serviceResult);
valuesToWriteResult.Add("Итого по приемам");
valuesToWriteResult.Add(referralTreatQuantityResult);
valuesToWriteResult.Add(null);
valuesToWriteResult.Add(null);
valuesToWriteResult.Add(referralTreatServiceQuantityResult);
valuesToWriteResult.Add(referralTreatServiceCountResult);
valuesToWriteResult.Add(referralTreatServiceAmountResult);
if (referralServiceAmountResult > 0)
{
valuesToWriteResult.Add(
((double)referralTreatServiceAmountResult / (double)referralServiceAmountResult * 100.0d)
.ToString("N2", CultureInfo.CurrentCulture));
}
else
{
valuesToWriteResult.Add("Нет направлений");
}
valuesToWriteResult[2] = null;
valuesToWriteResult[4] = null;
valuesToWriteResult[9] = null;
WriteOutRow(valuesToWriteResult, sheet, rowNumber, columnNumber);
columnNumber = 0;
rowNumber++;
}
if (!SaveAndCloseIWorkbook(workbook, resultFile))
{
return(string.Empty);
}
return(resultFile);
}
static List <ItemTreatment> ParseDataTable(DataTable dataTable)
{
List <ItemTreatment> treatments = new List <ItemTreatment>();
foreach (DataRow row in dataTable.Rows)
{
string treatdate = row["TREATDATE"].ToString();
string treatcode = row["TREATCODE"].ToString();
string filial = row["FILIAL"].ToString();
string client = row["CLIENT"].ToString();
string pcode = row["PCODE"].ToString();
string histnum = row["HISTNUM"].ToString();
string phone = row["PHONE"].ToString();
string depname = row["DEPNAME"].ToString();
string dcode = row["DCODE"].ToString();
string doc = row["DOC"].ToString();
string services = row["SERVICES"].ToString();
string referralsAll = row["REFERRALS_ALL"].ToString();
string referralsScheduled = row["REFERRALS_SCHEDULED"].ToString();
string referralsCompleted = row["REFERRALS_COMPLETED"].ToString();
string[] phoneSplitted = phone.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
phone = string.Join(", ", phoneSplitted);
ItemTreatment itemTreatment = new ItemTreatment {
Treatdate = treatdate,
Treatcode = treatcode,
Filial = filial,
Client = client,
Pcode = pcode,
Histnum = histnum,
Phone = phone,
TreatType = services.ToLower().Contains("первичный") ? "Первичный" : (services.ToLower().Contains("повторный") ? "Повторный" : ""),
Depname = depname,
Dcode = dcode,
Doc = doc
};
string[] servicesSplitted = services.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string service in servicesSplitted)
{
if (ParseService(service, row, out ItemService itemService))
{
itemTreatment.Services.Add(itemService);
}
}
string[] referralsAllSplitted = referralsAll.Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string referall in referralsAllSplitted)
{
string[] referralSplitted = referall.Split('^');
if (referralSplitted.Length != 3)
{
Console.WriteLine("referralsAll: " + referralsAll);
Logging.ToLog("Неправильная длина объекта направление, должно быть 3 части, сейчас: " + referralSplitted.Length);
continue;
}
ItemReferral itemReferral = new ItemReferral {
Refid = long.Parse(referralSplitted[0]),
RefType = referralSplitted[1]
};
string[] refServicesSplitted = referralSplitted[2].Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string service in refServicesSplitted)
{
if (ParseService(service, row, out ItemService itemService))
{
itemReferral.Services.Add(itemService);
}
}
itemTreatment.Referrals.Add(itemReferral.Refid, itemReferral);
}
string[] referralsScheduledSplitted = referralsScheduled.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string referralScheduled in referralsScheduledSplitted)
{
string[] scheduleSplitted = referralScheduled.Split('$');
if (scheduleSplitted.Length != 8)
{
Console.WriteLine("referralsScheduled: " + referralsScheduled);
Logging.ToLog("Неправильная длина объекта запись, должно быть 8 частей, сейчас: " + scheduleSplitted.Length);
continue;
}
try {
long refid = long.Parse(scheduleSplitted[0]);
ItemSchedule itemSchedule = new ItemSchedule {
Schedid = long.Parse(scheduleSplitted[1]),
Workdate = DateTime.Parse(scheduleSplitted[2]),
Doc = scheduleSplitted[3],
DocPost = scheduleSplitted[4],
AuthorFilial = scheduleSplitted[5],
AuthorName = scheduleSplitted[6],
AuthorPost = scheduleSplitted[7]
};
if (itemTreatment.Referrals.ContainsKey(refid))
{
itemTreatment.Referrals[refid].Schedule = itemSchedule;
}
} catch (Exception e) {
Logging.ToLog(e.Message + Environment.NewLine + e.StackTrace);
}
}
string[] referralsCompletedSplitted = referralsCompleted.Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string referralCompleted in referralsCompletedSplitted)
{
string[] treatSplitted = referralCompleted.Split('^');
if (treatSplitted.Length != 4)
{
Console.WriteLine("referralsCompleted: " + referralsCompleted);
Logging.ToLog("Неправильная длина объекта прием, должно быть 4 части, сейчас: " + treatSplitted.Length);
continue;
}
try {
long refid = long.Parse(treatSplitted[0]);
ItemTreat itemTreat = new ItemTreat {
Treatcode = long.Parse(treatSplitted[1]),
Treatdate = DateTime.Parse(treatSplitted[2])
};
string treatServices = treatSplitted[3];
string[] treatServicesSplitted = treatServices.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string treatService in treatServicesSplitted)
{
if (ParseService(treatService, row, out ItemService itemService))
{
itemTreat.Services.Add(itemService);
}
}
if (itemTreatment.Referrals.ContainsKey(refid))
{
itemTreatment.Referrals[refid].Treat = itemTreat;
}
} catch (Exception e) {
Logging.ToLog(e.Message + Environment.NewLine + e.StackTrace);
}
}
treatments.Add(itemTreatment);
}
return(treatments);
}
