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 OleDbConnection(DotNetReportHelper.GetConnectionString(connectKey)))
{
conn.Open();
var command = new OleDbCommand(sql, conn);
var adapter = new OleDbDataAdapter(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));
}
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 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));
}
}