public ActionResult RunScriptExcel(string scriptname, string parameter = null)
{
var model = new SqlScriptModel(CurrentDatabase);
var content = CurrentDatabase.ContentOfTypeSql(scriptname);
if (content == null)
{
return(Message("no content"));
}
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 = model.AddParametersForSql(parameter, content, p, ViewBag);
if (script.StartsWith("Not Authorized"))
{
return(Message(script));
}
using (var cn = CurrentDatabase.ReadonlyConnection())
{
cn.Open();
return(cn.ExecuteReader(script, p, commandTimeout: 1200).ToExcel("RunScript.xlsx", fromSql: true));
}
}
public ActionResult RunScriptExcel(string scriptname, string parameter = null)
{
var content = CurrentDatabase.ContentOfTypeSql(scriptname);
if (content == null)
{
return(Message("no content"));
}
var cs = User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
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));
}
return(cn.ExecuteReader(script, p, commandTimeout: 1200).ToExcel("RunScript.xlsx", fromSql: true));
}
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 Export(int id)
{
var query = CurrentDatabase.ContentOfTypeSql("BundleExportSql").DefaultTo(@"
SELECT header.BundleHeaderId [Bundle ID]
,header.DepositDate [Deposit Date]
,contrib.ContributionDate [Contribution Date]
,people.Name [Name]
,contrib.ContributionAmount [Amount]
,fund.FundIncomeAccount [Income Account]
,fund.FundId [Fund ID]
FROM dbo.BundleHeader header
JOIN dbo.BundleDetail detail ON detail.BundleHeaderId = header.BundleHeaderId
JOIN dbo.Contribution contrib ON contrib.ContributionId = detail.ContributionId
JOIN dbo.ContributionFund fund ON fund.FundId = contrib.FundId
JOIN dbo.People people ON people.PeopleId = contrib.PeopleId
WHERE contrib.ContributionTypeId <> 8 AND header.BundleHeaderId = @BundleId
ORDER BY contrib.ContributionDate
");
var connection = CurrentDatabase.ReadonlyConnection();
connection.Open();
var queryParameters = new DynamicParameters();
queryParameters.Add("@BundleId", id);
var filename = $"Bundle-Export-{id}.xlsx";
return(connection.ExecuteReader(query, queryParameters, commandTimeout: 1200).ToExcel(filename, fromSql: true));
}
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 TotalsByFundCustomExport(string id, TotalsByFundModel model)
{
var content = CurrentDatabase.ContentOfTypeSql(id);
if (content == null)
{
return(SimpleContent("no content"));
}
var connection = CurrentDatabase.ReadonlyConnection();
connection.Open();
var queryParameters = model.GetDynamicParameters();
var s = id.SpaceCamelCase();
return(connection.ExecuteReader(content, queryParameters, commandTimeout: 1200).ToExcel(s + ".xlsx", fromSql: true));
}
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));
}
private IDataReader ExecuteReader(string report, string oids, DateTime?meetingDate1, DateTime?meetingDate2)
{
var content = CurrentDatabase.ContentOfTypeSql(report);
if (!content.HasValue())
{
throw new Exception("no content");
}
if (!SpecialReportViewModel.CanRunScript(content))
{
throw new Exception("Not Authorized to run this script");
}
if (!content.Contains("@OrgIds", ignoreCase: true))
{
throw new Exception("missing @OrgIds");
}
var p = GetSqlParameters(oids, meetingDate1, meetingDate2, content);
var cn = CurrentDatabase.ReadonlyConnection();
cn.Open();
return(cn.ExecuteReader(content, p, commandTimeout: 1200));
}