public ActionResult SqlReport(OrgSearchModel m, string report, DateTime?dt1 = null, DateTime?dt2 = null)
{
try
{
var orgs = m.FetchOrgs();
var oids = string.Join(",", orgs.Select(oo => oo.OrganizationId));
ViewBag.ExcelUrl = $"/OrgSearch/SqlReportExcel/{report}";
ViewBag.DisplayName = report.SpaceCamelCase();
ViewBag.OrgIds = oids;
ViewBag.dt1 = dt1;
ViewBag.dt2 = dt2;
var content = CurrentDatabase.ContentOfTypeSql(report);
if (content.Contains("pagebreak", ignoreCase: true))
{
var p = m.GetSqlParameters(oids, dt1, dt2, content);
ViewBag.Results = PythonModel.PageBreakTables(CurrentDatabase, content, p);
return(View());
}
ViewBag.Results = m.SqlTable(report, oids, dt1, dt2);
return(View());
}
catch (Exception ex)
{
return(Message(ex));
}
}
public ActionResult RunScript(string name, string parameter = null, string title = null)
{
var content = CurrentDatabase.ContentOfTypeSql(name);
if (content == null)
{
return(Content("no content"));
}
var cs = User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
var d = Request.QueryString.AllKeys.ToDictionary(key => key, key => Request.QueryString[key]);
var p = new DynamicParameters();
foreach (var kv in d)
{
p.Add("@" + kv.Key, kv.Value);
}
string script = ScriptModel.RunScriptSql(parameter, content, p, ViewBag);
if (script.StartsWith("Not Authorized"))
{
return(Message(script));
}
ViewBag.Report = name;
ViewBag.Name = title ?? $"{name.SpaceCamelCase()} {parameter}";
if (script.Contains("pagebreak"))
{
ViewBag.report = PythonModel.PageBreakTables(CurrentDatabase, script, p);
return(View("RunScriptPageBreaks"));
}
ViewBag.Url = Request.Url?.PathAndQuery;
var rd = cn.ExecuteReader(script, p, commandTimeout: 1200);
ViewBag.ExcelUrl = Request.Url?.AbsoluteUri.Replace("RunScript/", "RunScriptExcel/");
return(View(rd));
}
public string Table()
{
var cn = db.ReadonlyConnection();
var p = Parameters();
var sql = Sql();
if (sql.Contains("@userid"))
{
p.Add("@userid", Util.UserId);
}
if (sql.Contains("pagebreak"))
{
return(PythonModel.PageBreakTables(db, sql, p));
}
var rd = cn.ExecuteReader(sql, p);
return(GridResult.Table(rd, Name2, 2000));
}
public string Table()
{
var cs = db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var p = Parameters();
var sql = Sql();
if (sql.Contains("@userid"))
{
p.Add("@userid", Util.UserId);
}
if (sql.Contains("pagebreak"))
{
return(PythonModel.PageBreakTables(DbUtil.Db, sql, p));
}
var rd = cn.ExecuteReader(sql, p);
return(GridResult.Table(rd, Name2, 2000));
}
public ActionResult RunScript(string name, string parameter = null, string title = null)
{
var m = new SqlScriptModel(CurrentDatabase);
var sql = m.FetchScript(name);
if (sql == null)
{
return(Message("no sql script named " + name));
}
if (!SqlScriptModel.CanRunScript(sql))
{
return(Message("Not Authorized to run this script"));
}
var p = m.FetchParameters();
var pSql = m.AddParametersForSql(parameter, sql, p, ViewBag);
ViewBag.Report = name;
ViewBag.Name = title ?? $"{name.SpaceCamelCase()} {parameter}";
if (sql.Contains("pagebreak"))
{
ViewBag.report = PythonModel.PageBreakTables(CurrentDatabase, sql, p);
return(View("RunScriptPageBreaks"));
}
ViewBag.Url = Request.Url?.PathAndQuery;
string html;
using (var cn = CurrentDatabase.ReadonlyConnection())
{
cn.Open();
var rd = cn.ExecuteReader(sql, p, commandTimeout: 1200);
ViewBag.ExcelUrl = Request.Url?.AbsoluteUri.Replace("RunScript/", "RunScriptExcel/");
html = GridResult.Table(rd, ViewBag.Name2);
}
return(View(new HtmlHolder {
html = html
}));
}