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 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");
}
var s = id.SpaceCamelCase();
return(cn.ExecuteReader(content, p, commandTimeout: 1200).ToExcel(s + ".xlsx", fromSql: true));
}
public ActionResult ToQuickBooks(TotalsByFundModel m)
{
List <int> lFunds = new List <int>();
List <QBJournalEntryLine> qbjel = new List <QBJournalEntryLine>();
var entries = m.TotalsByFund();
QuickBooksHelper qbh = new QuickBooksHelper();
foreach (var item in entries)
{
if (item.QBSynced > 0)
{
continue;
}
var accts = (from e in DbUtil.Db.ContributionFunds
where e.FundId == item.FundId
select e).Single();
if (accts.QBAssetAccount > 0 && accts.QBIncomeAccount > 0)
{
QBJournalEntryLine jelCredit = new QBJournalEntryLine();
jelCredit.sDescrition = item.FundName;
jelCredit.dAmount = item.Total ?? 0;
jelCredit.sAccountID = accts.QBIncomeAccount.ToString();
jelCredit.bCredit = true;
QBJournalEntryLine jelDebit = new QBJournalEntryLine(jelCredit);
jelDebit.sAccountID = accts.QBAssetAccount.ToString();
jelDebit.bCredit = false;
qbjel.Add(jelCredit);
qbjel.Add(jelDebit);
lFunds.Add(item.FundId ?? 0);
}
}
int iJournalID = qbh.CommitJournalEntries("Bundle from BVCMS", qbjel);
if (iJournalID > 0)
{
string sStart = m.Dt1.Value.ToString("u");
string sEnd = m.Dt2.Value.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("u");
sStart = sStart.Substring(0, sStart.Length - 1);
sEnd = sEnd.Substring(0, sEnd.Length - 1);
string sFundList = string.Join(",", lFunds.ToArray());
string sUpdate = "UPDATE dbo.Contribution SET QBSyncID = " + iJournalID + " WHERE FundId IN (" +
sFundList + ") AND ContributionDate BETWEEN '" + sStart + "' and '" + sEnd + "'";
DbUtil.Db.ExecuteCommand(sUpdate);
}
return(View("TotalsByFund", m));
}
public ActionResult FundList(TotalsByFundModel m)
{
return(Content($@"
<pre>
{string.Join(",", APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, m.FundSet))}
</pre>
"));
}
public ActionResult TotalsByFundResults(TotalsByFundModel m)
{
if (m.IncludeBundleType)
{
return(View("TotalsByFundResults2", m));
}
return(View(m));
}
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 TotalsByFundCustomExport(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();
var s = id.SpaceCamelCase();
return(cn.ExecuteReader(content, p, commandTimeout: 1200).ToExcel(s + ".xlsx", fromSql: true));
}
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 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 ActionResult TotalsByFund()
{
var model = new TotalsByFundModel(CurrentDatabase);
return(View(model));
}
public ActionResult TotalsByFundResults(TotalsByFundModel m)
{
return(View(m));
}
public ActionResult TotalsByFundExport(TotalsByFundModel m)
{
m.SaveAsExcel();
return(Content("done"));
}
public ActionResult TotalsByFund()
{
var m = new TotalsByFundModel();
return(View(m));
}
public ActionResult DonorTotalsByRangeResults(TotalsByFundModel m)
{
return(View(m));
}
public ActionResult DonorTotalsByRange()
{
var m = new TotalsByFundModel();
return(View(m));
}
public ActionResult TotalsByFundExport(TotalsByFundModel model)
{
model.SaveAsExcel();
return(SimpleContent("done"));
}
public ActionResult FundList(TotalsByFundModel model)
{
return(SimpleContent($@"<pre>{string.Join(",", APIContributionSearchModel.GetCustomFundSetList(CurrentDatabase, model.FundSet))}</pre>"));
}
