public JsonResult GetLookupList(string lookupSql, string connectKey)
{
var sql = DotNetReportHelper.Decrypt(lookupSql);
// Uncomment if you want to restrict max records returned
sql = sql.Replace("SELECT ", "SELECT TOP 500 ");
var json = new StringBuilder();
var dt = new DataTable();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectKey].ConnectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
var adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
int i = 0;
foreach (DataRow dr in dt.Rows)
{
json.AppendFormat("{{\"id\": \"{0}\", \"text\": \"{1}\"}}{2}", dr[0], dr[1], i != dt.Rows.Count - 1 ? "," : "");
i += 1;
}
return(Json((new JavaScriptSerializer()).DeserializeObject("[" + json.ToString() + "]"), JsonRequestBehavior.AllowGet));
}
public IActionResult GetLookupList(dynamic model)
{
string lookupSql = model.lookupSql;
string connectKey = model.connectKey;
var sql = DotNetReportHelper.Decrypt(lookupSql);
// Uncomment if you want to restrict max records returned
sql = sql.Replace("SELECT ", "SELECT TOP 500 ");
var dt = new DataTable();
using (var conn = new SqlConnection(Startup.StaticConfig.GetConnectionString(connectKey)))
{
conn.Open();
var command = new SqlCommand(sql, conn);
var adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
var data = new List <object>();
foreach (DataRow dr in dt.Rows)
{
data.Add(new { id = dr[0], text = dr[1] });
}
return(Ok(data));
}
public IActionResult DownloadXml(string reportSql, string connectKey, string reportName)
{
var xml = DotNetReportHelper.GetXmlFile(reportSql, connectKey, reportName);
Response.Headers.Add("content-disposition", "attachment; filename=" + reportName + ".xml");
Response.ContentType = "application/xml";
return(File(xml, "application/xml", reportName + ".xml"));
}
public IActionResult DownloadExcel(string reportSql, string connectKey, string reportName)
{
var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName);
Response.Headers.Add("content-disposition", "attachment; filename=" + reportName + ".xlsx");
Response.ContentType = "application/vnd.ms-excel";
return(File(excel, "application/vnd.ms-excel", reportName + ".xlsx"));
}
public async Task <ActionResult> DownloadPdf(string printUrl, int reportId, string reportSql, string connectKey, string reportName, bool expandAll)
{
reportSql = HttpUtility.HtmlDecode(reportSql);
var settings = GetSettings();
var dataFilters = settings.DataFilters != null?JsonConvert.SerializeObject(settings.DataFilters) : "";
var pdf = await DotNetReportHelper.GetPdfFile(printUrl, reportId, reportSql, connectKey, reportName, settings.UserId, settings.ClientId, string.Join(",", settings.CurrentUserRole), dataFilters, expandAll);
return(File(pdf, "application/pdf", reportName + ".pdf"));
}
public void DownloadXml(string reportSql, string connectKey, string reportName)
{
var xml = DotNetReportHelper.GetXmlFile(reportSql, connectKey, reportName);
Context.Response.ClearContent();
Context.Response.AddHeader("content-disposition", "attachment; filename=" + reportName + ".xml");
Context.Response.ContentType = "application/xml";
Context.Response.Write(xml);
Context.Response.End();
}
public void DownloadExcel(string reportSql, string connectKey, string reportName)
{
var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName);
Context.Response.ClearContent();
Context.Response.AddHeader("content-disposition", "attachment; filename=" + reportName + ".xlsx");
Context.Response.ContentType = "application/vnd.ms-excel";
Context.Response.BinaryWrite(excel);
Context.Response.End();
}
private async Task <string> GetConnectionString(ConnectViewModel connect)
{
using (var client = new HttpClient())
{
var response = await client.GetAsync(String.Format("{0}/ReportApi/GetDataConnectKey?account={1}&dataConnect={2}", connect.ApiUrl, connect.AccountApiKey, connect.DatabaseApiKey));
response.EnsureSuccessStatusCode();
var content = await response.Content.ReadAsStringAsync();
return(DotNetReportHelper.GetConnectionString(content.Replace("\"", "")));
}
}
public ActionResult DownloadExcel(string reportSql, string connectKey, string reportName, bool allExpanded, string expandSqls, string columnDetails = null)
{
var columns = columnDetails == null ? new List <ReportHeaderColumn>() : JsonConvert.DeserializeObject <List <ReportHeaderColumn> >(columnDetails);
var excel = DotNetReportHelper.GetExcelFile(reportSql, connectKey, reportName, allExpanded, expandSqls.Split(',').ToList(), columns);
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + reportName + ".xlsx");
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(excel);
Response.End();
return(View());
}
private DotNetReportDataModel DataTableToDotNetReportDataModel(DataTable dt, List <string> sqlFields)
{
var model = new DotNetReportDataModel
{
Columns = new List <DotNetReportDataColumnModel>(),
Rows = new List <DotNetReportDataRowModel>()
};
int i = 0;
foreach (DataColumn col in dt.Columns)
{
var sqlField = sqlFields[i++];
model.Columns.Add(new DotNetReportDataColumnModel
{
SqlField = sqlField.Substring(0, sqlField.IndexOf("AS")).Trim(),
ColumnName = col.ColumnName,
DataType = col.DataType.ToString(),
IsNumeric = DotNetReportHelper.IsNumericType(col.DataType)
});
}
foreach (DataRow row in dt.Rows)
{
i = 0;
var items = new List <DotNetReportDataRowItemModel>();
foreach (DataColumn col in dt.Columns)
{
items.Add(new DotNetReportDataRowItemModel
{
Column = model.Columns[i],
Value = row[col] != null ? row[col].ToString() : null,
FormattedValue = DotNetReportHelper.GetFormattedValue(col, row),
LabelValue = DotNetReportHelper.GetLabelValue(col, row)
});
i += 1;
}
model.Rows.Add(new DotNetReportDataRowModel
{
Items = items.ToArray()
});
}
return(model);
}
public async Task <JsonResult> CallReportApi(string method, string model)
{
using (var client = new HttpClient())
{
var settings = GetSettings();
var keyvalues = new List <KeyValuePair <string, string> >
{
new KeyValuePair <string, string>("account", settings.AccountApiToken),
new KeyValuePair <string, string>("dataConnect", settings.DataConnectApiToken),
new KeyValuePair <string, string>("clientId", settings.ClientId),
new KeyValuePair <string, string>("userId", settings.UserId),
new KeyValuePair <string, string>("userIdForSchedule", settings.UserIdForSchedule),
new KeyValuePair <string, string>("userRole", String.Join(",", settings.CurrentUserRole))
};
var data = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(model);
foreach (var key in data.Keys)
{
if ((key != "adminMode" || (key == "adminMode" && settings.CanUseAdminMode)) && data[key] != null)
{
keyvalues.Add(new KeyValuePair <string, string>(key, data[key].ToString()));
}
}
var content = new FormUrlEncodedContent(keyvalues);
var response = await client.PostAsync(new Uri(settings.ApiUrl + method), content);
var stringContent = await response.Content.ReadAsStringAsync();
if (stringContent.Contains("\"sql\":"))
{
var sqlqeuery = new JavaScriptSerializer().Deserialize <Dictionary <string, object> >(stringContent);
object value;
var keyValuePair = sqlqeuery.TryGetValue("sql", out value);
var sql = DotNetReportHelper.Decrypt(value.ToString());
}
Response.StatusCode = (int)response.StatusCode;
return(Json((new JavaScriptSerializer()).Deserialize <dynamic>(stringContent), JsonRequestBehavior.AllowGet));
}
}
public JsonResult RunReport(string reportSql, string connectKey, string reportType, int pageNumber = 1, int pageSize = 50, string sortBy = null, bool desc = false)
{
var sql = DotNetReportHelper.Decrypt(reportSql);
try
{
if (!String.IsNullOrEmpty(sortBy))
{
if (sortBy.StartsWith("DATENAME(MONTH, "))
{
sortBy = sortBy.Replace("DATENAME(MONTH, ", "MONTH(");
}
if (sortBy.StartsWith("MONTH(") && sortBy.Contains(")) +") && sql.Contains("Group By"))
{
sortBy = sortBy.Replace("MONTH(", "CONVERT(VARCHAR(3), DATENAME(MONTH, ");
}
sql = sql.Substring(0, sql.IndexOf("ORDER BY")) + "ORDER BY " + sortBy + (desc ? " DESC" : "");
}
// Execute sql
var dt = new DataTable();
var dtPaged = new DataTable();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectKey].ConnectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
var adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
dtPaged = (dt.Rows.Count > 0) ? dtPaged = dt.AsEnumerable().Skip((pageNumber - 1) * pageSize).Take(pageSize).CopyToDataTable() : dt;
var model = new DotNetReportResultModel
{
ReportData = DataTableToDotNetReportDataModel(dtPaged, sql),
Warnings = GetWarnings(sql),
ReportSql = sql,
ReportDebug = Request.Url.Host.Contains("localhost"),
Pager = new DotNetReportPagerModel
{
CurrentPage = pageNumber,
PageSize = pageSize,
TotalRecords = dt.Rows.Count,
TotalPages = (int)((dt.Rows.Count / pageSize) + 1)
}
};
return(new JsonResult()
{
Data = model,
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
MaxJsonLength = Int32.MaxValue
});
}
catch (Exception ex)
{
var model = new DotNetReportResultModel
{
ReportData = new DotNetReportDataModel(),
ReportSql = sql,
HasError = true,
Exception = ex.Message
};
return(Json(model, JsonRequestBehavior.AllowGet));
}
}
async Task IJob.Execute(IJobExecutionContext context)
{
var apiUrl = ConfigurationManager.AppSettings["dotNetReport.apiUrl"];
var accountApiKey = ConfigurationManager.AppSettings["dotNetReport.accountApiToken"];
var databaseApiKey = ConfigurationManager.AppSettings["dotNetReport.dataconnectApiToken"];
var fromEmail = ConfigurationManager.AppSettings["email.fromemail"];
var fromName = ConfigurationManager.AppSettings["email.fromname"];
var mailServer = ConfigurationManager.AppSettings["email.server"];
var mailUserName = ConfigurationManager.AppSettings["email.username"];
var mailPassword = ConfigurationManager.AppSettings["email.password"];
var clientId = ""; // you can specify client id here if needed
// Get all reports with schedule and run the ones that are due
using (var client = new HttpClient())
{
var response = await client.GetAsync($"{apiUrl}/ReportApi/GetScheduledReports?account={accountApiKey}&dataConnect={databaseApiKey}&clientId={clientId}");
response.EnsureSuccessStatusCode();
var content = await response.Content.ReadAsStringAsync();
var reports = JsonConvert.DeserializeObject <List <ReportWithSchedule> >(content);
foreach (var report in reports)
{
foreach (var schedule in report.Schedules)
{
try
{
var chron = new CronExpression(schedule.Schedule);
var lastRun = !String.IsNullOrEmpty(schedule.LastRun) ? Convert.ToDateTime(schedule.LastRun) : DateTimeOffset.UtcNow.AddMinutes(-10);
var nextRun = chron.GetTimeAfter(lastRun);
schedule.NextRun = (nextRun.HasValue ? nextRun.Value.ToLocalTime().DateTime : (DateTime?)null);
if (schedule.NextRun.HasValue && DateTime.Now >= schedule.NextRun && (!String.IsNullOrEmpty(schedule.LastRun) || lastRun <= schedule.NextRun))
{
// need to run this report
var dataFilters = new { }; // you can pass global data filters to apply as needed https://dotnetreport.com/kb/docs/advance-topics/global-filters/
response = await client.GetAsync($"{apiUrl}/ReportApi/RunScheduledReport?account={accountApiKey}&dataConnect={databaseApiKey}&scheduleId={schedule.Id}&reportId={report.Id}&localRunTime={schedule.NextRun.Value.ToShortDateString()} {schedule.NextRun.Value.ToShortTimeString()}&clientId={clientId}&dataFilters={(new JavaScriptSerializer()).Serialize(dataFilters)}");
response.EnsureSuccessStatusCode();
content = await response.Content.ReadAsStringAsync();
var reportToRun = JsonConvert.DeserializeObject <DotNetReportModel>(content);
response = await client.GetAsync($"{apiUrl}/ReportApi/LoadReportColumnDetails?account={accountApiKey}&dataConnect={databaseApiKey}&reportId={report.Id}&clientId={clientId}");
response.EnsureSuccessStatusCode();
content = await response.Content.ReadAsStringAsync();
var columnDetails = JsonConvert.DeserializeObject <List <ReportHeaderColumn> >(content);
var excelFile = DotNetReportHelper.GetExcelFile(reportToRun.ReportSql, reportToRun.ConnectKey, reportToRun.ReportName, columns: columnDetails);
// send email
var mail = new MailMessage
{
From = new MailAddress(fromEmail, fromName),
Subject = report.Name,
Body = $"Your scheduled report is attached.<br><br>{report.Description}",
IsBodyHtml = true
};
mail.To.Add(schedule.EmailTo);
var attachment = new Attachment(new MemoryStream(excelFile), report.Name + ".xlsx");
mail.Attachments.Add(attachment);
using (var smtpServer = new SmtpClient(mailServer))
{
smtpServer.Port = 587;
smtpServer.Credentials = new System.Net.NetworkCredential(mailUserName, mailPassword);
//smtpServer.EnableSsl = true;
smtpServer.Send(mail);
}
}
}
catch (Exception ex)
{
// could not run, ignore error
}
}
}
}
}
public JsonResult RunReport(string reportSql, string connectKey, string reportType, int pageNumber = 1, int pageSize = 50, string sortBy = null, bool desc = false, string reportSeries = null)
{
var sql = "";
var sqlCount = "";
int totalRecords = 0;
try
{
if (string.IsNullOrEmpty(reportSql))
{
throw new Exception("Query not found");
}
var allSqls = reportSql.Split(new string[] { "%2C" }, StringSplitOptions.RemoveEmptyEntries);
var dtPaged = new DataTable();
var dtCols = 0;
List <string> fields = new List <string>();
List <string> sqlFields = new List <string>();
for (int i = 0; i < allSqls.Length; i++)
{
sql = DotNetReportHelper.Decrypt(HttpUtility.HtmlDecode(allSqls[i]));
if (!sql.StartsWith("EXEC"))
{
var sqlSplit = sql.Substring(0, sql.IndexOf("FROM")).Replace("SELECT", "").Trim();
sqlFields = Regex.Split(sqlSplit, "], (?![^\\(]*?\\))").Where(x => x != "CONVERT(VARCHAR(3)")
.Select(x => x.EndsWith("]") ? x : x + "]")
.ToList();
var sqlFrom = $"SELECT {sqlFields[0]} {sql.Substring(sql.IndexOf("FROM"))}";
sqlCount = $"SELECT COUNT(*) FROM ({ (sqlFrom.Contains("ORDER BY") ? sqlFrom.Substring(0, sqlFrom.IndexOf("ORDER BY")) : sqlFrom)}) as countQry";
if (!String.IsNullOrEmpty(sortBy))
{
if (sortBy.StartsWith("DATENAME(MONTH, "))
{
sortBy = sortBy.Replace("DATENAME(MONTH, ", "MONTH(");
}
if (sortBy.StartsWith("MONTH(") && sortBy.Contains(")) +") && sql.Contains("Group By"))
{
sortBy = sortBy.Replace("MONTH(", "CONVERT(VARCHAR(3), DATENAME(MONTH, ");
}
if (!sql.Contains("ORDER BY"))
{
sql = sql + "ORDER BY " + sortBy + (desc ? " DESC" : "");
}
else
{
sql = sql.Substring(0, sql.IndexOf("ORDER BY")) + "ORDER BY " + sortBy + (desc ? " DESC" : "");
}
}
if (sql.Contains("ORDER BY"))
{
sql = sql + $" OFFSET {(pageNumber - 1) * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
}
}
// Execute sql
var dtPagedRun = new DataTable();
using (var conn = new OleDbConnection(DotNetReportHelper.GetConnectionString(connectKey)))
{
conn.Open();
var command = new OleDbCommand(sqlCount, conn);
if (!sql.StartsWith("EXEC"))
{
totalRecords = (int)command.ExecuteScalar();
}
command = new OleDbCommand(sql, conn);
var adapter = new OleDbDataAdapter(command);
adapter.Fill(dtPagedRun);
if (sql.StartsWith("EXEC"))
{
totalRecords = dtPagedRun.Rows.Count;
if (dtPagedRun.Rows.Count > 0)
{
dtPagedRun = dtPagedRun.AsEnumerable().Skip((pageNumber - 1) * pageSize).Take(pageSize).CopyToDataTable();
}
}
if (!sqlFields.Any())
{
foreach (DataColumn c in dtPagedRun.Columns)
{
sqlFields.Add($"{c.ColumnName} AS {c.ColumnName}");
}
}
string[] series = { };
if (i == 0)
{
dtPaged = dtPagedRun;
dtCols = dtPagedRun.Columns.Count;
fields.AddRange(sqlFields);
}
else if (i > 0)
{
// merge in to dt
if (!string.IsNullOrEmpty(reportSeries))
{
series = reportSeries.Split(new string[] { "%2C", "," }, StringSplitOptions.RemoveEmptyEntries);
}
var j = 1;
while (j < dtPagedRun.Columns.Count)
{
var col = dtPagedRun.Columns[j++];
dtPaged.Columns.Add($"{col.ColumnName} ({series[i - 1]})", col.DataType);
fields.Add(sqlFields[j - 1]);
}
foreach (DataRow dr in dtPaged.Rows)
{
DataRow match = null;
if (fields[0].ToUpper().StartsWith("CONVERT(VARCHAR(10)")) // group by day
{
match = dtPagedRun.AsEnumerable().Where(r => !string.IsNullOrEmpty(r.Field <string>(0)) && !string.IsNullOrEmpty((string)dr[0]) && Convert.ToDateTime(r.Field <string>(0)).Day == Convert.ToDateTime((string)dr[0]).Day).FirstOrDefault();
}
else if (fields[0].ToUpper().StartsWith("CONVERT(VARCHAR(3)")) // group by month/year
{
}
else
{
match = dtPagedRun.AsEnumerable().Where(r => r.Field <string>(0) == (string)dr[0]).FirstOrDefault();
}
if (match != null)
{
j = 1;
while (j < dtCols)
{
dr[j + i + dtCols - 2] = match[j];
j++;
}
}
}
}
}
}
sql = DotNetReportHelper.Decrypt(HttpUtility.HtmlDecode(allSqls[0]));
var model = new DotNetReportResultModel
{
ReportData = DataTableToDotNetReportDataModel(dtPaged, fields),
Warnings = GetWarnings(sql),
ReportSql = sql,
ReportDebug = Request.Url.Host.Contains("localhost"),
Pager = new DotNetReportPagerModel
{
CurrentPage = pageNumber,
PageSize = pageSize,
TotalRecords = totalRecords,
TotalPages = (int)(totalRecords == pageSize ? (totalRecords / pageSize) : (totalRecords / pageSize) + 1)
}
};
return(new JsonResult()
{
Data = model,
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
MaxJsonLength = Int32.MaxValue
});
}
catch (Exception ex)
{
var model = new DotNetReportResultModel
{
ReportData = new DotNetReportDataModel(),
ReportSql = sql,
HasError = true,
Exception = ex.Message
};
return(Json(model, JsonRequestBehavior.AllowGet));
}
}
public IActionResult RunReport(dynamic data)
{
string reportSql = data.reportSql;
string connectKey = data.connectKey;
string reportType = data.reportType;
int pageNumber = 1;
int pageSize = 50;
string sortBy = data.sortBy;
bool desc = data.desc;
var sql = DotNetReportHelper.Decrypt(HttpUtility.HtmlDecode(reportSql));
try
{
if (!String.IsNullOrEmpty(sortBy))
{
if (sortBy.StartsWith("DATENAME(MONTH, "))
{
sortBy = sortBy.Replace("DATENAME(MONTH, ", "MONTH(");
}
if (sortBy.StartsWith("MONTH(") && sortBy.Contains(")) +") && sql.Contains("Group By"))
{
sortBy = sortBy.Replace("MONTH(", "CONVERT(VARCHAR(3), DATENAME(MONTH, ");
}
sql = sql.Substring(0, sql.IndexOf("ORDER BY")) + "ORDER BY " + sortBy + (desc ? " DESC" : "");
}
// Execute sql
var dt = new DataTable();
var dtPaged = new DataTable();
using (var conn = new SqlConnection(Startup.StaticConfig.GetConnectionString(connectKey)))
{
conn.Open();
var command = new SqlCommand(sql, conn);
var adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
dtPaged = (dt.Rows.Count > 0) ? dtPaged = dt.AsEnumerable().Skip((pageNumber - 1) * pageSize).Take(pageSize).CopyToDataTable() : dt;
var model = new DotNetReportResultModel
{
ReportData = DataTableToDotNetReportDataModel(dtPaged, sql),
Warnings = GetWarnings(sql),
ReportSql = sql,
ReportDebug = Request.Host.Host.Contains("localhost"),
Pager = new DotNetReportPagerModel
{
CurrentPage = pageNumber,
PageSize = pageSize,
TotalRecords = dt.Rows.Count,
TotalPages = (int)((dt.Rows.Count / pageSize) + 1)
}
};
return(Ok(model));
}
catch (Exception ex)
{
var model = new DotNetReportResultModel
{
ReportData = new DotNetReportDataModel(),
ReportSql = sql,
HasError = true,
Exception = ex.Message
};
return(Ok(model));
}
}
