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 })); }