public ActionResult Outstanding(OutstandingSearch search)
{
var sqlPath = System.Web.Hosting.HostingEnvironment.MapPath("~/App_Data/sql/renewals.txt");
using (var session = NHibernateHelper.CreateSessionFactory())
{
using (var transaction = session.BeginTransaction())
{
var sql = System.IO.File.ReadAllText(sqlPath);
var renewalsData =
from x in session.CreateSQLQuery(sql).DynamicList()
select new
{
x.RenewalId,
x.CacNumber,
x.EstablishmentName,
x.Date,
x.RenewalTypeId,
x.AmountDue,
x.TotalPaid,
x.Balance
};
var renewalSearchList = new List<RenewalSearch>();
if (renewalsData.Any())
{
foreach (var r in renewalsData)
{
var renewalSearchItem = new RenewalSearch
{
AmountDue = r.AmountDue,
Balance = r.Balance,
CacNumber = r.CacNumber,
Date = r.Date,
EstablishmentName = r.EstablishmentName,
RenewalId = r.RenewalId,
RenewalTypeId = r.RenewalTypeId,
TotalPaid = r.TotalPaid
};
renewalSearchList.Add(renewalSearchItem);
}
}
if (!string.IsNullOrEmpty(search.EstablishmentName))
{
renewalSearchList = renewalSearchList.Where(s => s.EstablishmentName.ToLower().Contains(search.EstablishmentName.ToLower())).ToList();
}
// filter to only show outstanding ones
renewalSearchList = renewalSearchList.Where(s => s.AmountDue > 0).ToList();
var jsonResult =
from x in renewalSearchList
select x;
return Json(jsonResult);
}
}
}
public ActionResult Outstanding(OutstandingSearch search)
{
var sqlPath = System.Web.Hosting.HostingEnvironment.MapPath("~/App_Data/sql/renewals.txt");
using (var session = NHibernateHelper.CreateSessionFactory())
{
using (var transaction = session.BeginTransaction())
{
var sql = System.IO.File.ReadAllText(sqlPath);
var renewalsData =
from x in session.CreateSQLQuery(sql).DynamicList()
select new
{
x.RenewalId,
x.CacNumber,
x.EstablishmentName,
x.Date,
x.RenewalTypeId,
x.AmountDue,
x.TotalPaid,
x.Balance
};
var renewalSearchList = new List <RenewalSearch>();
if (renewalsData.Any())
{
foreach (var r in renewalsData)
{
var renewalSearchItem = new RenewalSearch
{
AmountDue = r.AmountDue,
Balance = r.Balance,
CacNumber = r.CacNumber,
Date = r.Date,
EstablishmentName = r.EstablishmentName,
RenewalId = r.RenewalId,
RenewalTypeId = r.RenewalTypeId,
TotalPaid = r.TotalPaid
};
renewalSearchList.Add(renewalSearchItem);
}
}
if (!string.IsNullOrEmpty(search.EstablishmentName))
{
renewalSearchList = renewalSearchList.Where(s => s.EstablishmentName.ToLower().Contains(search.EstablishmentName.ToLower())).ToList();
}
// filter to only show outstanding ones
renewalSearchList = renewalSearchList.Where(s => s.AmountDue > 0).ToList();
var jsonResult =
from x in renewalSearchList
select x;
return(Json(jsonResult));
}
}
}