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);

				}
			}
		}
示例#2
0
        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));
                }
            }
        }