private static QueryData GetQueryData(string procName, string validName, string selectText, string descText, string number, string execText, string projectName, SqlConnection conn)
{
Log.Write("Start GetQueryData:" + execText);
var obj = new QueryData();
try
{
var lstColumns = new List<string>();
var sql = execText;
var cmd = new SqlCommand(sql.Replace("\\r\\n", "").Replace("\\r", ""), conn);
if (cmd.CommandText.StartsWith("EXEC"))
{
cmd.CommandTimeout = 200;
}
if (cmd.CommandText.Contains("check_patient"))
{
cmd.CommandTimeout = 90;
}
cmd.CommandTimeout = 200;
var rdrQD = cmd.ExecuteReader();
if (rdrQD.HasRows)
{
for (int i = 0; i < rdrQD.FieldCount; i++)
{
lstColumns.Add(rdrQD.GetName(i));
}
obj.FieldsName = lstColumns;
// Получаем сами данные
var dtData = new DataTable();
dtData.Load(rdrQD);
obj.Data = dtData;
obj.ProjectName = projectName;
obj.ValidationRule = validName;
obj.Description = descText;
obj.NameList = number;
}
else
{
obj = null;
}
rdrQD.Close();
rdrQD = null;
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Exit GetQueryData:" + execText);
}
return obj;
}
private static void FormatSheet(Excel.Worksheet sheet, QueryData obj)
{
//sheet.PageSetup.PrintGridlines = false;
// sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
// sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
// sheet.PageSetup.RightFooter = "Дата: &DD Стр &PP из &NN";
// sheet.PageSetup.RightHeader = obj.ProjectName + " - " + obj.ValidationRule;
// sheet.PageSetup.Zoom = false;
// sheet.PageSetup.LeftHeader = "e-CRF";
// sheet.PageSetup.TopMargin = 50;
// sheet.PageSetup.BottomMargin = 50;
// sheet.PageSetup.HeaderMargin = 20;
// sheet.PageSetup.FooterMargin = 20;
// sheet.PageSetup.RightMargin = 10;
// sheet.PageSetup.LeftMargin = 50;
// sheet.PageSetup.Order = Excel.XlOrder.xlOverThenDown;
sheet.Columns.EntireColumn.AutoFit();
sheet.Range[sheet.Cells[1, 1], sheet.Cells[obj.Data.Rows.Count, 3]].NumberFormat = "@";
}
private static void SimpleTable(Document document, QueryData data)
{
document.LastSection.AddParagraph("Описание: " + data.Description, "Heading2");
Table table = new Table();
table.Borders.Width = 0.75;
Row row ;
Cell cell;
int i = 0;
int cntr = 0;
foreach (var strFieldName in data.FieldsName)
{
var column = table.AddColumn();
column.Format.Alignment = ParagraphAlignment.Center;
column.Width = (document.DefaultPageSetup.PageWidth - document.DefaultPageSetup.RightMargin - document.DefaultPageSetup.LeftMargin)/data.FieldsName.Count;
}
row = table.AddRow();
row.Shading.Color = Colors.PaleGoldenrod;
row.HeadingFormat = true;
foreach (var strFieldName in data.FieldsName)
{
cell = row.Cells[i];
cell.AddParagraph(strFieldName);
i++;
}
for ( i = 0; i < data.Data.Rows.Count; i++)
{
cntr++;
row = table.AddRow();
if (cntr % 2 == 0)
{
row.Shading.Color = Colors.LightGray;
}
for (int j = 0; j < data.FieldsName.Count; j++)
{
cell = row.Cells[j];
cell.AddParagraph(data.Data.Rows[i][j].ToString());
cell.Format.Font.Size = 8;
}
}
table.SetEdge(0, 0, data.FieldsName.Count, data.Data.Rows.Count, Edge.Box, BorderStyle.Single, 1.5, Colors.Black);
document.LastSection.Add(table);
}