Calculate the sum of the values in a column of a table

Question

I am trying to add the total values of any numeric column of a table in MVC, and display the totals in a header row. For example, I would like to add all of the values in the TotalCashStake column, and have that number display in a header row. I am completly new to MVC and I am not sure how to implement this.

This is the index.cshtml page:

This is my source code for the index.cshtml page:

@model IEnumerable<DailyReport>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")

    @using (Html.BeginForm())
    {
    <p>
        Start Date: @Html.TextBox("StartDate") <br />
        <br />
        End Date: @Html.TextBox("EndDate") <br />
        <br />
        <input type="submit" value="Filter" />
    </p>
    }



</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.DailyReportDate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.BettingShop)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Estate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.SisSrNumber)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ShopBalance)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.TotalCashStake)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.TotalOtherCashOut)
        </th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.DailyReportDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.BettingShop)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Estate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.SisSrNumber)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.ShopBalance)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.TotalCashStake)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.TotalOtherCashOut)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.DailyReportId }) |
            @Html.ActionLink("Details", "Details", new { id=item.DailyReportId }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.DailyReportId })
        </td>
    </tr>
}
</table>

And this is the code for my Controller:

namespace Reporting.Controllers
{
    public class DailyReportController : Controller
    {
        private RiskEntities db = new RiskEntities();

        // GET: /DailyReport/
        public ActionResult Index(DateTime? startDate, DateTime? endDate)
        {


            if (startDate == null || endDate == null)
            {
                return View(db.DailyReports.ToList());
            }
            var endDateToUse = (DateTime) endDate;
            endDateToUse = endDateToUse.AddDays(+1);
            var dailyReports = (from dr in db.DailyReports
                                where dr.DailyReportDate >= startDate
                                && dr.DailyReportDate <= endDateToUse
                                select dr);



            return View(dailyReports.ToList());
        }

        // GET: /DailyReport/Details/5
        public ActionResult Details(Guid? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            DailyReport dailyreport = db.DailyReports.Find(id);
            if (dailyreport == null)
            {
                return HttpNotFound();
            }
            return View(dailyreport);
        }

        // GET: /DailyReport/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: /DailyReport/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include="DailyReportId,DailyReportDate,BettingShop,Estate,SisSrNumber,ShopBalance,TotalCashStake,TotalOtherCashOut,TotalPhoneAccountCashDeposit,TotalPhoneAccountCashWithdrawl,TotalCashFromBank,TotalTransfersFromOtherShops,TotalPaidByOtherShops,TotalCashToBank,TotalTransfersToOtherShops,TotalPaidHereForOtherShops,TotalExpenses,TotalOtherCashIn,TotalPayoutCancellations,TotalStakes,TotalStakeLessVoids,TotalVoid,TotalPayouts,TotalCreditPayouts,TotalCreditStakes,TotalCreditVoids,TotalEndFloatActual,PendingPayouts6Days,PendingPayouts1Day,TotalCashIn,TotalCashPayoutIncludingVoids,TotalCashPayoutExcludingVoids,TotalCashPayoutForHere,NumberCashSlips,NumberAccountSlips,NumberCashSlipsPaidForOtherDays,NumberCashSlipsPaidForToday,TotalTillTransferIn,TotalTillTransferOut,TotalCashOut,TotalCashLeftHandSide,TotalCashRightHandSide")] DailyReport dailyreport)
        {
            if (ModelState.IsValid)
            {
                dailyreport.DailyReportId = Guid.NewGuid();
                db.DailyReports.Add(dailyreport);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(dailyreport);
        }

        // GET: /DailyReport/Edit/5
        public ActionResult Edit(Guid? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            DailyReport dailyreport = db.DailyReports.Find(id);
            if (dailyreport == null)
            {
                return HttpNotFound();
            }
            return View(dailyreport);
        }

        // POST: /DailyReport/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include="DailyReportId,DailyReportDate,BettingShop,Estate,SisSrNumber,ShopBalance,TotalCashStake,TotalOtherCashOut,TotalPhoneAccountCashDeposit,TotalPhoneAccountCashWithdrawl,TotalCashFromBank,TotalTransfersFromOtherShops,TotalPaidByOtherShops,TotalCashToBank,TotalTransfersToOtherShops,TotalPaidHereForOtherShops,TotalExpenses,TotalOtherCashIn,TotalPayoutCancellations,TotalStakes,TotalStakeLessVoids,TotalVoid,TotalPayouts,TotalCreditPayouts,TotalCreditStakes,TotalCreditVoids,TotalEndFloatActual,PendingPayouts6Days,PendingPayouts1Day,TotalCashIn,TotalCashPayoutIncludingVoids,TotalCashPayoutExcludingVoids,TotalCashPayoutForHere,NumberCashSlips,NumberAccountSlips,NumberCashSlipsPaidForOtherDays,NumberCashSlipsPaidForToday,TotalTillTransferIn,TotalTillTransferOut,TotalCashOut,TotalCashLeftHandSide,TotalCashRightHandSide")] DailyReport dailyreport)
        {
            if (ModelState.IsValid)
            {
                db.Entry(dailyreport).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(dailyreport);
        }

        // GET: /DailyReport/Delete/5
        public ActionResult Delete(Guid? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            DailyReport dailyreport = db.DailyReports.Find(id);
            if (dailyreport == null)
            {
                return HttpNotFound();
            }
            return View(dailyreport);
        }

        // POST: /DailyReport/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(Guid id)
        {
            DailyReport dailyreport = db.DailyReports.Find(id);
            db.DailyReports.Remove(dailyreport);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

Answer 1

I would recommend using the the DataTables | Table plug-in for jQuery it's an open source plug in, or something similar rather than re-inventing the wheel for every controller. http://datatables.net/

This will work well for all types of tabular data

You can see how they do totalling here: http://datatables.net/examples/advanced_init/footer_callback.html

This will also give you other benefits like paging and sorting out of the box.

Answer 2

I would suggest that you calculate the sum inside your controller and then extend your model to include that value separately to your table data. This way your view is only concerned with displaying the data sent from the server instead of calculating values on the fly. This will also improve your page's performance.