public DataTable GetDataTable(string query, Dictionary <string, string> parameters)
{
DataTable dataTable = new DataTable();
if (!IsConnectionOpened())
{
return(dataTable);
}
try {
FbCommand command = new FbCommand(query, connection);
if (parameters.Count > 0)
{
foreach (KeyValuePair <string, string> parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
FbDataAdapter fbDataAdapter = new FbDataAdapter(command);
fbDataAdapter.Fill(dataTable);
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error);
SystemLogging.LogMessageToFile(subject + " " + body);
connection.Close();
}
return(dataTable);
}
public bool ExecuteUpdateQuery(string query, Dictionary <string, object> parameters)
{
bool updated = false;
if (!IsConnectionOpened())
{
return(updated);
}
try {
FbCommand update = new FbCommand(query, connection);
if (parameters.Count > 0)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
update.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
updated = update.ExecuteNonQuery() > 0 ? true : false;
} catch (Exception e) {
string subject = "Ошибка выполнения запроса к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error);
SystemLogging.LogMessageToFile(subject + " " + body);
connection.Close();
}
return(updated);
}
private bool IsConnectionOpened()
{
if (connection.State != ConnectionState.Open)
{
try {
connection.Open();
} catch (Exception e) {
string subject = "Ошибка подключения к БД";
string body = e.Message + Environment.NewLine + e.StackTrace;
MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error);
SystemLogging.LogMessageToFile(subject + " " + body);
}
}
return(connection.State == ConnectionState.Open);
}
public static string WriteDataToExcel(List <ItemTreatmentDetails> details, string resultFilePrefix, BackgroundWorker backgroundWorker, double progressCurrent)
{
string templateFile = SystemLogging.AssemblyDirectory + "Template.xlsx";
double progressStep = 10.0 / details.Count;
foreach (char item in Path.GetInvalidFileNameChars())
{
resultFilePrefix = resultFilePrefix.Replace(item, '-');
}
if (!File.Exists(templateFile))
{
return("Не удалось найти файл шаблона: " + templateFile);
}
string resultPath = Path.Combine(SystemLogging.AssemblyDirectory, "Results");
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
string resultFile = Path.Combine(resultPath, resultFilePrefix + ".xlsx");
IWorkbook workbook;
using (FileStream stream = new FileStream(templateFile, FileMode.Open, FileAccess.Read))
workbook = new XSSFWorkbook(stream);
int rowNumber = 1;
int columnNumber = 0;
ISheet sheet = workbook.GetSheet("Data");
foreach (ItemTreatmentDetails item in details)
{
backgroundWorker.ReportProgress((int)progressCurrent, "Запись в excel строки " + rowNumber + " / " + details.Count);
IRow row = sheet.CreateRow(rowNumber);
//Console.WriteLine("create row: " + row);
DateTime treatDate = DateTime.ParseExact(item.TREATDATE, "dd.MM.yyyy h:mm:ss", CultureInfo.InvariantCulture);
double period = -1;
string referralPeriodMax = string.Empty;
foreach (ItemReferral referral in item.Referrals)
{
if (string.IsNullOrEmpty(referral.TREATDATE1))
{
continue;
}
DateTime refDate = DateTime.ParseExact(referral.TREATDATE1, "dd.MM.yyyy h:mm:ss", CultureInfo.InvariantCulture);
double days = (refDate - treatDate).TotalDays;
if (days > period)
{
period = days;
}
}
if (period > -1)
{
referralPeriodMax = period.ToString();
}
string[] array = new string[] {
item.TREATDATE,
item.FILIALNAME,
item.DEPNAME,
item.DOCNAME,
item.PATIENTNAME,
item.HISTNUM,
item.BDATE,
item.TREAT_TYPE,
item.MKBCODE,
item.Referrals.Count.ToString(),
referralPeriodMax
};
//Console.WriteLine("values: " + string.Join(", ", array));
foreach (string value in array)
{
try {
ICell cell = row.CreateCell(columnNumber);
Console.WriteLine("create column: " + columnNumber);
string valueToWrite = value.Replace(" 0:00:00", "");
if (double.TryParse(valueToWrite, out double result))
{
cell.SetCellValue(result);
}
else
{
cell.SetCellValue(valueToWrite);
}
} catch (Exception e) {
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
columnNumber++;
}
foreach (ItemReferral referral in item.Referrals)
{
string name = referral.SCHNAME;
int isActivated = string.IsNullOrEmpty(referral.TREATDATE1) ? 0 : 1;
for (int i = columnNumber; i < 16000; i++)
{
string currentValue = "";
try {
if (i < sheet.GetRow(0).LastCellNum)
{
currentValue = sheet.GetRow(0).GetCell(i).StringCellValue;
}
} catch (Exception) {
}
try {
if (string.IsNullOrEmpty(currentValue))
{
ICell cellHeader = sheet.GetRow(0).CreateCell(i);
cellHeader.SetCellValue(name);
ICell cellValue = row.CreateCell(i);
cellValue.SetCellValue(isActivated);
break;
}
} catch (Exception e) {
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
try {
if (currentValue.Equals(name))
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(isActivated);
break;
}
} catch (Exception e) {
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
}
}
columnNumber = 0;
rowNumber++;
progressCurrent += progressStep;
}
sheet = workbook.GetSheet("Services");
int rowUsed = 1;
Console.WriteLine("=======================================");
Console.WriteLine("sheet.LastRowNum: " + sheet.LastRowNum);
foreach (ItemTreatmentDetails item in details)
{
foreach (ItemReferral referral in item.Referrals)
{
string name = referral.SCHNAME;
bool isPresent = false;
bool isActivated = (string.IsNullOrEmpty(referral.TREATDATE1)) ? false : true;
double.TryParse(referral.SCOUNT, out double currentTotal);
double.TryParse(referral.SCHCOUNT, out double currentActivated);
Console.WriteLine("name: " + name + " | " + currentTotal + " " + currentActivated);
for (int row = 1; row < rowUsed; row++)
{
try {
string rowValue = sheet.GetRow(row).GetCell(0).StringCellValue;
if (rowValue.Equals(name))
{
double total = sheet.GetRow(row).GetCell(1).NumericCellValue;
double activated = sheet.GetRow(row).GetCell(2).NumericCellValue;
Console.WriteLine("finded: " + total + " - " + activated);
total += currentTotal;
activated += currentActivated;
sheet.GetRow(row).GetCell(1).SetCellValue(total);
sheet.GetRow(row).GetCell(2).SetCellValue(activated);
isPresent = true;
break;
}
} catch (Exception) {
break;
}
}
if (isPresent)
{
continue;
}
IRow rowLine = sheet.CreateRow(rowUsed);
ICell cellName = rowLine.CreateCell(0);
ICell cellTotal = rowLine.CreateCell(1);
ICell cellActivated = rowLine.CreateCell(2);
cellName.SetCellValue(name);
cellTotal.SetCellValue(currentTotal);
cellActivated.SetCellValue(currentActivated);
rowUsed++;
}
}
Console.WriteLine("=======================================");
using (FileStream stream = new FileStream(resultFile, FileMode.Create, FileAccess.Write))
workbook.Write(stream);
workbook.Close();
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
return("Не удалось открыть приложение Excel");
}
xlApp.Visible = false;
Excel.Workbook wb = xlApp.Workbooks.Open(resultFile);
if (wb == null)
{
return("Не удалось открыть книгу " + resultFile);
}
Excel.Worksheet ws = wb.Sheets["Data"];
if (ws == null)
{
return("Не удалось открыть лист Data");
}
try {
PerformSheet(wb, ws, xlApp);
} catch (Exception e) {
SystemLogging.LogMessageToFile(e.Message + Environment.NewLine + e.StackTrace);
}
ws = wb.Sheets["Services"];
ws.Activate();
try {
PerformSheetServices(wb, ws, xlApp);
} catch (Exception e) {
SystemLogging.LogMessageToFile(e.Message + Environment.NewLine + e.StackTrace);
}
wb.Save();
wb.Close();
xlApp.Quit();
return(resultFile);
}
