public virtual string ExecuteHtml(string viewTemplate, OperationPackage package)
{
string date = $"{DateTime.Now:dd.MM.yy HH:mm:ss}";
if (!package.DataSets.Any())
{
return("No information obtained by query");
}
var packageValues = PackageParser.GetPackageValues(package);
var dataSet = packageValues.First();
var model = new
{
dataSet.Headers,
Content = dataSet.Rows,
Date = date
};
var engine = new RazorLightEngineBuilder()
.UseEmbeddedResourcesProject(typeof(Program))
.UseMemoryCachingProvider()
.Build();
var result = engine.CompileRenderStringAsync("templateKey", viewTemplate, model).Result;
return(result);
}
public virtual string ExecuteHtml(string viewTemplate, OperationPackage package)
{
string date = $"{DateTime.Now:dd.MM.yy HH:mm:ss}";
TemplateServiceConfiguration templateConfig =
new TemplateServiceConfiguration
{
DisableTempFileLocking = true,
CachingProvider = new DefaultCachingProvider(t => { })
};
var serv = RazorEngineService.Create(templateConfig);
Engine.Razor = serv;
Engine.Razor.Compile(viewTemplate, "somekey");
if (!package.DataSets.Any())
{
return("No information obtained by query");
}
var packageValues = PackageParser.GetPackageValues(package);
var dataSet = packageValues.First();
var model = new
{
dataSet.Headers,
Content = dataSet.Rows,
Date = date
};
return(Engine.Razor.Run("somekey", null, model));
}
public virtual ExcelPackage ExecuteXlsx(OperationPackage package, string reportName, bool useAllSets = false)
{
var pack = new ExcelPackage();
var packageContent = PackageParser.GetPackageValues(package);
if (useAllSets)
{
var i = 1;
foreach (var ds in packageContent)
{
AddDataSetToExcel(pack, ds);
if (pack.Workbook.Worksheets.Last().Name == "NoNamedList")
{
pack.Workbook.Worksheets.Last().Name = $"Dataset{i}";
}
i++;
}
}
else
{
AddDataSetToExcel(pack, packageContent.First());
}
return(pack);
}
public override ExcelPackage ExecuteXlsx(OperationPackage package, string reportName, bool useAllSets = false)
{
var pack = new ExcelPackage();
var packageContent = PackageParser.GetPackageValues(package);
if (useAllSets)
{
for (int i = 0; i < packageContent.Count; i++)
{
if (packageContent[i].GroupColumns != null)
{
AddDataSetToExcel(pack, packageContent[i]);
}
else
{
base.AddDataSetToExcel(pack, packageContent[i]);
}
if (pack.Workbook.Worksheets[i + 1].Name == "NoNamedList")
{
pack.Workbook.Worksheets[i + 1].Name = $"Dataset{i + 1}";
}
}
}
else
{
AddDataSetToExcel(pack, packageContent.First());
}
return(pack);
}
public List <DataSetContent> GetPackageValues(OperationPackage package)
{
var allContent = new List <DataSetContent>();
foreach (var set in package.DataSets)
{
var setHeaders = set.Columns.Select(col => col.Name).ToList();
var setRows = new List <List <object> >();
foreach (var row in set.Rows)
{
var rowValues = new List <object>();
for (int i = 0; i < set.Columns.Count; i++)
{
var colInfo = set.Columns[i];
var varValue = row.Values[i];
rowValues.Add(GetFromVariantValue(colInfo, varValue));
}
setRows.Add(rowValues);
}
allContent.Add(new DataSetContent
{
Headers = setHeaders,
Rows = setRows,
Name = set.Name
});
}
return(allContent);
}
private void SaveJsonPackageToServer(OperationPackage package, string packageFileName,
NetworkCredential credentials)
{
var parser = autofac.Resolve <IPackageParser>();
var sets = parser.GetPackageValues(package);
var dataToSave = UseAllSets
? JsonConvert.SerializeObject(sets)
: JsonConvert.SerializeObject(sets.First());
var packageNameJson = packageFileName
+ ".json";
var uri = Path.Combine(Host, FolderPath, packageNameJson);
if (WebRequest.Create(uri) is FtpWebRequest request)
{
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = credentials;
var byteds = Encoding.UTF8
.GetBytes(dataToSave);
using (Stream requestStream = request.GetRequestStream())
requestStream.Write(byteds, 0, byteds.Length);
}
}
public virtual ExcelPackage ExecuteXlsx(OperationPackage package, string reportName, bool useAllSets = false)
{
var pack = new ExcelPackage();
var packageContent = PackageParser.GetPackageValues(package);
if (useAllSets)
{
for (int i = 0; i < packageContent.Count; i++)
{
AddDataSetToExcel(pack, packageContent[i]);
if (pack.Workbook.Worksheets[i + 1].Name == "NoNamedList")
{
pack.Workbook.Worksheets[i + 1].Name = $"Dataset{i + 1}";
}
}
// foreach (var set in packageContent)
}
else
{
AddDataSetToExcel(pack, packageContent.First());
}
return(pack);
}
private void SaveCsvPackageToServer(OperationPackage package, string packageFileName, SftpClient client)
{
var filenameCsv = packageFileName
+ ".csv";
var csvBytes = viewExecutor.ExecuteCsv(package, useAllSets: UseAllSets);
using (var csvStream = new MemoryStream(csvBytes))
client.UploadFile(csvStream, Path.Combine(FolderPath, filenameCsv));
}
public void ExampleProtoUsing()
{
//TestingProtoMessage proto =new TestingProtoMessage
//{
// ColumnCount = 0,
// Columns = { new ColumnInfo
// {
// Tag = { 1,5,2,5},
// Name = { "412","vxzw","g215d"},
// TypeName = "Int32Type"
// },
// new ColumnInfo{
// Tag = {521,321,332,755},
// Name = { "41gsdf2","vxzb cvw",@"g43-\215d"},
// TypeName = "Intg32Type"
// }}
//};
OperationPackage package = new OperationPackage();
var t = package.DataSets;
ColumnInfo info = new ColumnInfo();
TestingProtoInt protoint = new TestingProtoInt
{
ColumnCount = { 1, 1, 1, 1, 1, 1, 1, 1, 32112 }
};
TestingProtoInt2 protoInt2 = new TestingProtoInt2
{
ColumnCount = { 1, 1, 1, 1, 1, 1, 1, 1, 32112 },
//RowCount = {2, 2, 2, 2, 2, 2, 2, 2}
};
using (var output =
File.OpenWrite(@"C:\ArsMak\ReportServer\TestModule\exampleserialized.dat"))
{
protoint.WriteTo(output);
}
using (var output =
File.OpenWrite(@"C:\ArsMak\ReportServer\TestModule\exampleint2-d.dat"))
{
protoint.WriteDelimitedTo(output);
protoint.WriteDelimitedTo(output);
}
using (var output =
File.OpenWrite(@"C:\ArsMak\ReportServer\TestModule\exampleint2-nd.dat"))
{
protoint.WriteTo(output);
protoint.WriteTo(output);
}
}
private void SaveXmlFileToServer(OperationPackage package, string packageFileName, SftpClient client)
{
var filenameXml = packageFileName
+ ".xml";
XmlSerializer formatter = new XmlSerializer(typeof(DataSet));
using var streamXml = new MemoryStream();
formatter.Serialize(streamXml, package.DataSets.First());
client.UploadFile(streamXml, Path.Combine(FolderPath, filenameXml));
}
private void SaveXlsxPackageToServer(OperationPackage package, string packageFileName, SftpClient client)
{
var filenameXlsx = packageFileName
+ ".xlsx";
using var excel = viewExecutor.ExecuteXlsx(package, Properties.PackageName, UseAllSets);
using var streamXlsx = new MemoryStream();
excel.SaveAs(streamXlsx);
streamXlsx.Position = 0;
client.UploadFile(streamXlsx, Path.Combine(FolderPath, filenameXlsx));
}
private void AddDataSetsXlsx(OperationPackage package, MailMessage msg,
MemoryStream streamXlsx, string fileName)
{
var excel = viewExecutor.ExecuteXlsx(package, fileName, UseAllSetsXlsx);
excel.SaveAs(streamXlsx);
excel.Dispose();
streamXlsx.Position = 0;
msg.Attachments.Add(new Attachment(streamXlsx, $@"{fileName}.xlsx",
@"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public static void AddRecipientsFromPackage(this MailMessage msg, OperationPackage package)
{
List <string> to = new List <string>();
List <string> bcc = new List <string>();
var set = package.DataSets.FirstOrDefault();
if (set == null)
{
return;
}
var addressIndex = set.Columns.IndexOf(set.Columns
.FirstOrDefault(col => col.Name == "Address"));
var recTypeIndex = set.Columns.IndexOf(set.Columns
.FirstOrDefault(col => col.Name == "RecType"));
if (addressIndex == -1 || recTypeIndex == -1)
{
return;
}
foreach (var row in set.Rows)
{
var newAddrs = row.Values[addressIndex].StringValue
.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries)
.ToList();
if (newAddrs.Count == 0)
{
continue;
}
var recType = row.Values[recTypeIndex].StringValue;
if (recType == "To")
{
to.AddRange(newAddrs);
}
else if (recType == "Bcc")
{
bcc.AddRange(newAddrs);
}
}
AddAddressesToCollection(to, msg.To);
AddAddressesToCollection(bcc, msg.Bcc);
}
public override string ExecuteHtml(string tableName, OperationPackage package)
{
string tableTemplate = @"<!DOCTYPE html>
<html>
<head>
<META http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
<title>ReportServer</title>
<link rel=""stylesheet"" href=""https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"">
<style>
table {
border-collapse: collapse;
width: 80%;
}
th, td {
border: 1px solid Black;
padding: 10px;
}
</style>
</head>
<body>"
+
$@"<h3 align=""center"">{tableName}</h3>"
+
@"<table class=""table table-bordered table-hover "">
<tr>
@foreach(var header in @Model.Headers)
{
<th> @header </th>
}
</tr>
@foreach(var props in @Model.Content)
{
<tr>
@foreach(var prop in @props)
{
<td> @prop</td>
}
</tr>
}
</table>
</body>
</html>";
return(base.ExecuteHtml(tableTemplate, package));
}
private void SaveCsvPackageToServer(OperationPackage package, string packageFileName,
NetworkCredential credentials)
{
var packageNameCsv = packageFileName
+ ".csv";
var csvBytes = viewExecutor.ExecuteCsv(package, useAllSets: UseAllSets);
var uri = Path.Combine(Host, FolderPath, packageNameCsv);
if (WebRequest.Create(uri) is FtpWebRequest request)
{
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = credentials;
using (Stream requestStream = request.GetRequestStream())
requestStream.Write(csvBytes, 0, csvBytes.Length);
}
}
private void SaveJsonPackageToServer(OperationPackage package, string packageFileName, SftpClient client)
{
var filenameJson = packageFileName
+ ".json";
var parser = autofac.Resolve <IPackageParser>();
var sets = parser.GetPackageValues(package);
var dataToSave = UseAllSets
? JsonConvert.SerializeObject(sets)
: JsonConvert.SerializeObject(sets.First());
using var streamJson = new MemoryStream(System.Text.Encoding.UTF8
.GetBytes(dataToSave));
client.UploadFile(streamJson, Path.Combine(FolderPath, filenameJson));
}
public List <DataSetContent> GetPackageValues(OperationPackage package)
{
var allContent = new List <DataSetContent>();
foreach (var set in package.DataSets)
{
var setHeaders = set.Columns.Select(col => col.Name).ToList();
var setRows = new List <List <object> >();
var groupNumbers = set.ViewSettings?.GroupingColumnNumbers?.ToList();
var orderSettings = set.ViewSettings?.OrderSettings
.Select(stgs => new OrderSettings
{
ColumnNumber = stgs.ColumnNumber,
Descending = stgs.Descending
}).ToList();
foreach (var row in set.Rows)
{
var rowValues = new List <object>();
for (int i = 0; i < set.Columns.Count; i++)
{
var colInfo = set.Columns[i];
var varValue = row.Values[i];
rowValues.Add(GetFromVariantValue(colInfo, varValue));
}
setRows.Add(rowValues);
}
allContent.Add(new DataSetContent
{
Headers = setHeaders,
Rows = setRows,
Name = set.Name,
GroupColumns = groupNumbers,
OrderColumns = orderSettings
});
}
return(allContent);
}
private void SaveXlsxPackageToServer(OperationPackage package, string packageFileName,
NetworkCredential credentials)
{
var packageNameXlsx = packageFileName
+ ".xlsx";
var uri = Path.Combine(Host, FolderPath, packageNameXlsx);
if (WebRequest.Create(uri) is FtpWebRequest request)
{
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = credentials;
using (var excel = viewExecutor.ExecuteXlsx(package, Properties.PackageName, UseAllSets))
{
using (Stream requestStream = request.GetRequestStream())
excel.SaveAs(requestStream);
}
}
}
public virtual string ExecuteTelegramView(OperationPackage package,
string reportName = "Отчёт", bool useAllSets = false)
{
var packageValues = PackageParser.GetPackageValues(package);
var tmRep = $@"*{reportName}*";
if (useAllSets)
{
foreach (var dataset in packageValues)
{
tmRep = AddDataSetToTelegView(tmRep, dataset);
}
}
else
{
tmRep = AddDataSetToTelegView(tmRep, packageValues.First());
}
return(tmRep);
}
public byte[] ExecuteCsv(OperationPackage package,
string delimiter = ";",
bool useAllSets = false) //byte[] because closing inner streams causes closing of external one
{
var csvStream = new MemoryStream();
try
{
using (var writerStream = new StreamWriter(csvStream))
{
using var csvWriter = new CsvWriter(writerStream);
csvWriter.Configuration.Delimiter = delimiter;
if (!useAllSets)
{
var firstSet = PackageParser.GetPackageValues(package).First();
AddDataSetToCsv(firstSet, csvWriter);
}
else
{
foreach (var dataSet in PackageParser.GetPackageValues(package))
{
AddDataSetToCsv(dataSet, csvWriter);
}
}
writerStream.Flush();
csvStream.Position = 0;
}
return(csvStream.ToArray());
}
finally
{
csvStream.Dispose();
}
}
public void SendError(List <Tuple <Exception, string> > exceptions, string taskName)
{
using (var client = new SmtpClient(ConfigurationManager.AppSettings["SMTPServer"], 25))
using (var msg = new MailMessage())
{
client.EnableSsl = true;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
msg.From = new MailAddress(ConfigurationManager.AppSettings["from"]);
foreach (var addr in ConfigurationManager.AppSettings["administrativeaddress"]
.Split(';'))
{
msg.To.Add(new MailAddress(addr));
}
msg.Subject = $"Errors occured in task {taskName} at" +
$" {DateTime.Now:dd.MM.yy HH:mm}";
msg.IsBodyHtml = true;
List <ColumnInfo> columns = new List <ColumnInfo>
{
new ColumnInfo
{
Name = "Operation",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Message",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Trace",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Source",
Type = ScalarType.String
}
};
var rows = exceptions.Select(pair => new Row
{
Values =
{
new VariantValue {
StringValue = pair.Item2
},
new VariantValue {
StringValue = pair.Item1.Message
},
new VariantValue {
StringValue = pair.Item1.StackTrace ?? ""
},
new VariantValue {
StringValue = pair.Item1.Source ?? ""
}
}
});
var exceptionsPack = new OperationPackage
{
DataSets =
{
new DataSet
{
Columns ={ columns },
Rows = { rows }
}
}
};
msg.Body =
executor.ExecuteHtml("Errors list", exceptionsPack);
client.Send(msg);
}
} //method
public string GetDefaultPackageView(string taskName, OperationPackage package)
{
return(executor.ExecuteHtml(taskName, package));
}
public override string ExecuteHtml(string tableName, OperationPackage package)
{
string date = $"{DateTime.Now:dd.MM.yy HH:mm:ss}";
if (!package.DataSets.Any())
{
return("No information obtained by query");
}
var packageValues = PackageParser.GetPackageValues(package);
var dataSet = packageValues.First();
var groupColumns = dataSet.GroupColumns;
var grouping = GetMergedRows(dataSet.Rows, groupColumns, groupColumns);
var groupedT = CreateGroupedHtmlTable(grouping);
groupedT = groupedT.Replace("@", "@"); //needed '@' symbol escaping for proper razorengine work
string tableTemplate =
@"<!DOCTYPE html>
<html>
<head>
<META http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
<title>ReportServer</title>
<link rel=""stylesheet"" href=""https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"">
<style>
table
{
border-collapse: collapse;
width: 80%;
}
th, td
{
border: 1px solid Black;
padding: 10px;
}
</style>
</head>
<body>"
+
$@"<h3 align=""center"">{tableName}</h3>"
+
@"<table class=""table table-bordered"">
<tr>
@foreach(var header in @Model.Headers)
{
<th> @header </th>
}
</tr>"
+
groupedT
+
@"</table>
</body>
</html>";
var headers = ChangeHeadersOrder(dataSet.Headers, groupColumns);
var model = new
{
Headers = headers,
Date = date
};
var engine = new RazorLightEngineBuilder()
.UseEmbeddedResourcesProject(typeof(Program))
.UseMemoryCachingProvider()
.Build();
var result = engine.CompileRenderStringAsync("templateKey", tableTemplate, model).Result;
return(result);
}
public byte[] ExecuteCsv(OperationPackage package,
string delimiter = ";",
bool useAllSets = false) //byte[] because closing inner streams causes closing of external one
{
var csvStream = new MemoryStream();
try
{
using (var writerStream = new StreamWriter(csvStream))
{
using (var csvWriter = new CsvWriter(writerStream))
{
csvWriter.Configuration.Delimiter = delimiter;
if (!useAllSets)
{
var firstSet = PackageParser.GetPackageValues(package).First();
foreach (var head in firstSet.Headers)
{
csvWriter.WriteField(head);
}
csvWriter.NextRecord();
foreach (var row in firstSet.Rows)
{
foreach (var value in row)
{
csvWriter.WriteField(value);
}
csvWriter.NextRecord();
}
}
else
{
foreach (var dataSet in PackageParser.GetPackageValues(package))
{
foreach (var head in dataSet.Headers)
{
csvWriter.WriteField(head);
}
csvWriter.NextRecord();
foreach (var row in dataSet.Rows)
{
foreach (var value in row)
{
csvWriter.WriteField(value);
}
csvWriter.NextRecord();
}
}
}
writerStream.Flush();
csvStream.Position = 0;
}
}
return(csvStream.ToArray());
}
finally
{
csvStream.Dispose();
}
}
public void SendError(List <Tuple <Exception, string> > exceptions, string taskName)
{
using var client = ConfigureClient();
using var msg = ConfigureHtmlMessage();
foreach (var addr in administrativeAddresses
.Split(';'))
{
msg.To.Add(new MailAddress(addr));
}
msg.Subject = $"Errors occured in task {taskName} at" +
$" {DateTime.Now:dd.MM.yy HH:mm}";
List <ColumnInfo> columns = new List <ColumnInfo>
{
new ColumnInfo
{
Name = "Operation",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Message",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Trace",
Type = ScalarType.String
},
new ColumnInfo
{
Name = "Source",
Type = ScalarType.String
}
};
var rows = exceptions.Select(pair => new Row
{
Values =
{
new VariantValue {
StringValue = pair.Item2
},
new VariantValue {
StringValue = pair.Item1.Message
},
new VariantValue {
StringValue = pair.Item1.StackTrace ?? ""
},
new VariantValue {
StringValue = pair.Item1.Source ?? ""
}
}
});
var exceptionsPack = new OperationPackage
{
DataSets =
{
new DataSet
{
Columns ={ columns },
Rows = { rows }
}
}
};
msg.Body =
executor.ExecuteHtml("Errors list", exceptionsPack);
client.Send(msg);
} //method
public override string ExecuteHtml(string tableName, OperationPackage package)
{
string date = $"{DateTime.Now:dd.MM.yy HH:mm:ss}";
TemplateServiceConfiguration templateConfig =
new TemplateServiceConfiguration
{
DisableTempFileLocking = true,
CachingProvider = new DefaultCachingProvider(t => { })
};
templateConfig.Namespaces
.Add("ReportService.Operations.DataExporters.ViewExecutors");
var serv = RazorEngineService.Create(templateConfig);
if (!package.DataSets.Any())
{
return("No information obtained by query");
}
var packageValues = PackageParser.GetPackageValues(package);
var dataSet = packageValues.First();
var groupColumns = dataSet.GroupColumns;
var grouping = GetMergedRows(dataSet.Rows, groupColumns, groupColumns);
var groupedT = CreateGroupedHtmlTable(grouping);
groupedT = groupedT.Replace("@", "@"); //needed '@' symbol escaping for proper razorengine work
string tableTemplate =
@"<!DOCTYPE html>
<html>
<head>
<META http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
<title>ReportServer</title>
<link rel=""stylesheet"" href=""https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"">
<style>
table
{
border-collapse: collapse;
width: 80%;
}
th, td
{
border: 1px solid Black;
padding: 10px;
}
</style>
</head>
<body>"
+
$@"<h3 align=""center"">{tableName}</h3>"
+
@"<table class=""table table-bordered"">
<tr>
@foreach(var header in @Model.Headers)
{
<th> @header </th>
}
</tr>"
+
groupedT
+
@"</table>
</body>
</html>";
var headers = ChangeHeadersOrder(dataSet.Headers, groupColumns);
var model = new
{
Headers = headers,
Date = date
};
Engine.Razor = serv;
Engine.Razor.Compile(tableTemplate, "somekey");
return(Engine.Razor.Run("somekey", null, model));
}
