public ActionResult SqlReport(Guid id, string report)
{
var content = DbUtil.Db.ContentOfTypeSql(report);
if (content == null)
{
return(Message("no content"));
}
if (!CanRunScript(content.Body))
{
return(Message("Not Authorized to run this script"));
}
if (!content.Body.Contains("@qtagid"))
{
return(Message("missing @qtagid"));
}
var tag = DbUtil.Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query);
var cs = User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
var p = new DynamicParameters();
p.Add("@qtagid", tag.Id);
ViewBag.name = report;
using (var rd = cn.ExecuteReader(content.Body, p))
ViewBag.report = GridResult.Table(rd);
return(View());
}
}
public ActionResult TotalsByFundCustomReport(string id, TotalsByFundModel model)
{
var content = CurrentDatabase.ContentOfTypeSql(id);
if (content == null)
{
return(SimpleContent("no content"));
}
var p = model.GetDynamicParameters();
ViewBag.Name = id.SpaceCamelCase();
var linkUrl = CurrentDatabase.ServerLink($"/TotalsByFundCustomExport/{id}");
var linkHtml = $"<a href='{linkUrl}' class='CustomExport btn btn-default' target='_blank'><i class='fa fa-file-excel-o'></i> Download as Excel</a>";
using (var connection = CurrentDatabase.ReadonlyConnection())
{
connection.Open();
var reader = connection.ExecuteReader(content, p, commandTimeout: 1200);
var contentTable = GridResult.Table(reader, id.SpaceCamelCase(), excellink: linkHtml);
return(SimpleContent(contentTable));
}
}
public ActionResult TotalsByFundCustomReport(string id, TotalsByFundModel m)
{
var content = DbUtil.Db.ContentOfTypeSql(id);
if (content == null)
{
return(Content("no content"));
}
var cs = Util.ConnectionStringReadOnlyFinance;
var cn = new SqlConnection(cs);
cn.Open();
var p = new DynamicParameters();
p.Add("@StartDate", m.Dt1);
p.Add("@EndDate", m.Dt2);
p.Add("@CampusId", m.CampusId);
p.Add("@Online", m.Online);
p.Add("@TaxNonTax", m.TaxDedNonTax);
p.Add("@IncludeUnclosedBundles", m.IncUnclosedBundles);
if (m.FilterByActiveTag)
{
var tagid = DbUtil.Db.TagCurrent().Id;
p.Add("@ActiveTagFilter", tagid);
}
else
{
p.Add("@ActiveTagFilter");
}
ViewBag.Name = id.SpaceCamelCase();
var rd = cn.ExecuteReader(content, p, commandTimeout: 1200);
return(Content(GridResult.Table(rd, id.SpaceCamelCase())));
}
public string Table()
{
var cs = db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var p = Parameters();
var sql = Sql();
var rd = cn.ExecuteReader(sql, p);
return(GridResult.Table(rd, Name2, 2000));
}
public void RunSqlReport()
{
DynamicParameters p;
var content = GetParameters(out p);
using (var cn = CurrentDatabase.ReadonlyConnection())
{
cn.Open();
using (var rd = cn.ExecuteReader(content, p))
{
Results = GridResult.Table(rd, Name2);
}
}
}
public void RunSqlReport()
{
var content = DbUtil.Db.ContentOfTypeSql(Report);
if (content == null)
{
throw new Exception("no content");
}
if (!CanRunScript(content.Body))
{
throw new Exception("Not Authorized to run this script");
}
var hasqtag = content.Body.Contains("@qtagid");
var hascurrentorg = content.Body.Contains("@CurrentOrgId");
if (!hasqtag && !hascurrentorg)
{
throw new Exception("missing @qtagid or @CurrentOrgId");
}
var p = new DynamicParameters();
if (hasqtag)
{
var tag = DbUtil.Db.PopulateSpecialTag(Id, DbUtil.TagTypeId_Query);
p.Add("@qtagid", tag.Id);
}
if (hascurrentorg)
{
var oid = DbUtil.Db.CurrentOrgId0;
p.Add("@CurrentOrgId", oid);
if (oid > 0)
{
Name2 = DbUtil.Db.LoadOrganizationById(oid).FullName2;
}
}
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
using (var rd = cn.ExecuteReader(content.Body, p))
Results = GridResult.Table(rd, Name2);
}
}
public void RunSqlReport()
{
DynamicParameters p;
var content = GetParameters(out p);
using (var cn = CurrentDatabase.ReadonlyConnection())
{
cn.Open();
using (var rd = cn.ExecuteReader(content, p))
{
Results = GridResult.Table(rd, Name2);
}
}
CurrentDatabase.LogActivity($"Run SQL report: {Report}", pid: CurrentDatabase.UserPeopleId, uid: CurrentDatabase.UserId);
}
public void RunSqlReport()
{
DynamicParameters p;
var content = GetParameters(out p);
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
using (var rd = cn.ExecuteReader(content, p))
Results = GridResult.Table(rd, Name2);
}
}
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 ActionResult TotalsByFundCustomReport(string id, TotalsByFundModel m)
{
var content = DbUtil.Db.ContentOfTypeSql(id);
if (content == null)
{
return(Content("no content"));
}
var cs = Util.ConnectionStringReadOnlyFinance;
var cn = new SqlConnection(cs);
cn.Open();
var p = m.GetDynamicParameters();
ViewBag.Name = id.SpaceCamelCase();
var rd = cn.ExecuteReader(content, p, commandTimeout: 1200);
var excelink = DbUtil.Db.ServerLink($"/TotalsByFundCustomExport/{id}");
var link = $"<a href='{excelink}' class='CustomExport btn btn-default' target='_blank'><i class='fa fa-file-excel-o'></i> Download as Excel</a>";
return(Content(GridResult.Table(rd, id.SpaceCamelCase(), excellink: link)));
}
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
}));
}
public ActionResult TotalsByFundCustomReport(string id, TotalsByFundModel m)
{
var content = DbUtil.Db.ContentOfTypeSql(id);
if (content == null)
{
return(Content("no content"));
}
var cs = Util.ConnectionStringReadOnlyFinance;
var cn = new SqlConnection(cs);
cn.Open();
var p = new DynamicParameters();
p.Add("@StartDate", m.Dt1);
p.Add("@EndDate", m.Dt2);
p.Add("@CampusId", m.CampusId);
p.Add("@Online", m.Online);
p.Add("@TaxNonTax", m.TaxDedNonTax);
p.Add("@IncludeUnclosedBundles", m.IncUnclosedBundles);
if (m.FilterByActiveTag)
{
var tagid = DbUtil.Db.TagCurrent().Id;
p.Add("@ActiveTagFilter", tagid);
}
else
{
p.Add("@ActiveTagFilter");
}
ViewBag.Name = id.SpaceCamelCase();
var rd = cn.ExecuteReader(content, p, commandTimeout: 1200);
var excelink = Request.Url?.AbsoluteUri.Replace("TotalsByFundCustomReport/", "TotalsByFundCustomExport/");
var link = $"<a href='{excelink}' class='CustomExport btn btn-default' target='_blank'><i class='fa fa-file-excel-o'></i> Download as Excel</a>";
return(Content(GridResult.Table(rd, id.SpaceCamelCase(), excellink: link)));
}
public string SqlTable(string report, string oids, DateTime?meetingDate1, DateTime?meetingDate2)
{
using (var rd = ExecuteReader(report, oids, meetingDate1, meetingDate2))
return(GridResult.Table(rd));
}
public string SqlTable(string report, string oids)
{
using (var rd = ExecuteReader(report, oids))
return(GridResult.Table(rd));
}