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)); } }
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 DotNetReportResultModel 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 = Context.Request.Url.Host.Contains("localhost"), Pager = new DotNetReportPagerModel { CurrentPage = pageNumber, PageSize = pageSize, TotalRecords = dt.Rows.Count, TotalPages = (int)((dt.Rows.Count / pageSize) + 1) } }; return(model); } catch (Exception ex) { return(new DotNetReportResultModel { ReportSql = sql, HasError = true, Exception = ex.Message }); } }