public ActionResult Index(DateTime?SDateFrom, DateTime?SDateTo)
{
if (Session["Id"] != null)
{
if (SDateFrom == null && SDateTo == null)
{
//SDateFrom = DateTime.Parse("2017-01-01");
//SDateTo = DateTime.Parse("2019-12-31");
DateTime now = DateTime.Now;
SDateFrom = new DateTime(now.Year, now.Month, 1);
SDateTo = SDateFrom.Value.AddMonths(1).AddDays(-1);
String sDateFromstring = String.Format("{0:yyyy-MM-dd}", SDateFrom);
String sDateTostring = String.Format("{0:yyyy-MM-dd}", SDateTo);
ViewBag.SDateFrom = sDateFromstring;
ViewBag.SDateTo = sDateTostring;
}
else
{
String sDateFromstring = String.Format("{0:yyyy-MM-dd}", SDateFrom);
String sDateTostring = String.Format("{0:yyyy-MM-dd}", SDateTo);
ViewBag.SDateFrom = sDateFromstring;
ViewBag.SDateTo = sDateTostring;
}
var TIDs = TempData["TIDs"] as IEnumerable <Int64>;
DateTime ReportFrom = SDateFrom.HasValue ? SDateFrom.Value:new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).Date;
DateTime ReportTo = SDateTo.HasValue?SDateTo.Value: DateTime.Now.Date;
int SMonth = ReportFrom.Month;
int TMonth = ReportTo.Month;
int SYear = ReportFrom.Year;
int TYear = ReportTo.Year;
int CurrentMonth = DateTime.Now.Month;
int CurrentYear = DateTime.Now.Year;
var tenants = _tenantInfoManager.GetAll().Where(x => x.IsActive).ToList();
var Tin = TIDs == null?new TenantInfoManager().GetAll(): new TenantInfoManager().GetAll().Where(x => TIDs.Contains(x.Id)).ToList();
IEnumerable <TenantInfo> tins;
if (TIDs == null)
{
tins = new TenantInfoManager().GetAll();
}
else
{
tins = new TenantInfoManager().GetAll().Where(x => TIDs.Contains(x.Id)).ToList();
}
var Fri = new FloorRentInfoManager().GetAll().Where(x => x.IsActive == true).ToList();
var Bim = new BillingInfoPerMonthManager().GetAll().Where(x => x.IsActive && (Convert.ToInt64(x.Year) * 12 + Convert.ToInt64(x.Month)) >= SYear * 12 + SMonth && (Convert.ToInt64(x.Year) * 12 + Convert.ToInt64(x.Month)) <= TYear * 12 + TMonth).ToList();
var Bri = new BillingReceivedInfoManager().GetAll().Where(x => x.Date >= ReportFrom && x.Date <= ReportTo && x.IsActive).ToList();
var query = (from tin in tins
where tin.IsActive.Equals(true)
join fri in Fri on tin.Id equals fri.TenantInfoId into fris
join bim in Bim on tin.Id equals bim.TenantInfoId
join bri in Bri on tin.Id equals bri.TenantInfoId into bris
select new MonthlyBillingReceivedInfoReportVm
{
TenantId = tin.Id,
TenantName = tin.Name,
MonthlyBillingId = bim.Id,
Month = Convert.ToInt32(bim.Month),
Year = Convert.ToInt32(bim.Year),
RentBill = bim.RentBill,
CommonBill = bim.CommonBill,
ElectricityBill = bim.ElectricityBill,
EmElectricityBill = bim.EmElectricityBill,
WasaBill = bim.WasaBill,
FloorBasics = fris.Select(x => new FloorBasic
{
FloorLevel = x.FloorLevel,
FloorSpace = x.RentSpaceSFT,
RentPerSft = x.SftRate,
EffectiveDate = x.EffectiveDate,
ExpiryDate = x.ExpiryDate,
CommonBillRateSFT = x.CommonBillRateSFT,
AdvanceAdjustment = x.AdvanceAdjustment
}).ToList(),
ReceivedBillsPerMonth = bris.Select(x => new BillingReceivedPerMonth
{
Date = x.Date,
Amount = x.Amount,
Purpose = x.Purpose
}).ToList(),
LessAITPercent = tin.LessAITPercent,
AdvanceAdjustment = tin.AdvancePay,
RentAIT = tin.RentAIT,
CommonAIT = tin.CommonAIT,
OpeningBalanceRent = bim.OpeningBalanceRent,
CurrentDuesRent = bim.CurrentDuesRent,
RemarksRent = bim.RemarksRent,
OpeningBalanceCommon = bim.OpeningBalanceCommon,
CurrentDuesCommon = bim.CurrentDuesCommon,
RemarksCommon = bim.RemarksCommon,
OpeningBalanceElectricity = bim.OpeningBalanceElectricity,
CurrentDuesElectricity = bim.CurrentDuesElectricity,
RemarksElectricity = bim.RemarksElectricity,
OpeningBalanceWasa = bim.OpeningBalanceWasa,
CurrentDuesWasa = bim.CurrentDuesWasa,
RemarksWasa = bim.RemarksWasa,
OpeningBalanceEmElectricity = bim.OpeningBalanceEmElectricity,
CurrentDuesEmElectricity = bim.CurrentDuesEmElectricity,
RemarksEmElectricity = bim.RemarksEmElectricity,
}).OrderByDescending(x => x.Month);
var result = query.ToList();
return(View(result));
}
else
{
return(RedirectToAction("Login", "AccountInfo"));
}
}
public JsonResult GetList()
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
//Find paging info
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
//Find order columns info
var sortColumnIndex = Request.Form.GetValues("order[0][column]").FirstOrDefault();
var sortColumnName = Request.Form.GetValues("columns[" + sortColumnIndex + "][data]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
//find search columns info
var search = Request.Form.GetValues("search[value]").FirstOrDefault().ToLower();
var sFloorLevel = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault().ToLower();
var sTenantInfoId = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault().ToLower();
var sEffectiveDate = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();
var sExpiryDate = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault().ToLower();
var pageSize = length != null?Convert.ToInt32(length) : 0;
var skip = start != null?Convert.ToInt16(start) : 0;
var floorRentInfo = _floorRentInfoManager.GetAll().Where(s => s.IsActive && s.IsCurrent);
var tenantInfo = _tenantInfoManager.GetAll().Where(x => x.IsActive && x.IsCurrent == true);
var query = (from f in floorRentInfo
join t in tenantInfo on f.TenantInfoId equals t.Id
select new FloorRentInfoVm {
Id = f.Id,
TenantInfoId = f.TenantInfoId,
FloorLevel = f.FloorLevel,
TenantName = t.Name,
EffectiveDate = f.EffectiveDate,
ExpiryDate = f.ExpiryDate,
RentSpaceSFT = f.RentSpaceSFT,
SftRate = f.SftRate,
LessAITPercent = f.LessAITPercent,
RentAIT = f.RentAIT,
CommonAIT = f.CommonAIT,
CommonBillRateSFT = f.CommonBillRateSFT,
AdvancePay = f.AdvancePay,
AdvanceAdjustment = f.AdvanceAdjustment
});
var total = query.Count();
//SEARCHING...
//query = query.Where(q => q.FloorLevel == sFloorLevel || string.IsNullOrEmpty(sFloorLevel));
query = query.Where(q => q.TenantInfoId.ToString() == sTenantInfoId || string.IsNullOrEmpty(sTenantInfoId));
if (!string.IsNullOrEmpty(sEffectiveDate))
{
var sdEffectiveDate = DateTime.Parse(sEffectiveDate);
query = query.Where(q => q.EffectiveDate == sdEffectiveDate);
}
if (!string.IsNullOrEmpty(sExpiryDate))
{
var sdExpiryDate = DateTime.Parse(sExpiryDate);
query = query.Where(q => q.ExpiryDate == sdExpiryDate);
}
//SORTING... (For sorting we need to add a reference System.Linq.Dynamic)
if (!(string.IsNullOrEmpty(sortColumnName) && string.IsNullOrEmpty(sortColumnDir)))
{
query = query.OrderBy(sortColumnName + " " + sortColumnDir);
}
var filtered = query.Count();
if (pageSize != -1)
{
query = query.Skip(skip).Take(pageSize);
}
return(Json(new { draw, recordsFiltered = filtered, recordsTotal = total, data = query.ToList() },
JsonRequestBehavior.AllowGet));
}
public JsonResult GetList()
{
//jQuery DataTables Param
var draw = Request.Form.GetValues("draw").FirstOrDefault();
//Find paging info
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
//Find order columns info
var sortColumnIndex = Request.Form.GetValues("order[0][column]").FirstOrDefault();
var sortColumnName = Request.Form.GetValues("columns[" + sortColumnIndex + "][data]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
//find search columns info
var search = Request.Form.GetValues("search[value]").FirstOrDefault().ToLower();
var sTenantInfoId = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault().ToLower();
var sDateFrom = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault().ToLower();
var sDateTo = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault().ToLower();
var pageSize = length != null?Convert.ToInt32(length) : 0;
var skip = start != null?Convert.ToInt16(start) : 0;
var billingInfo = _billingReceivedInfoManager.GetAll().Where(x => x.IsActive);
var tenantInfo = _tenantInfoManager.GetAll();
var query = (from b in billingInfo
join t in tenantInfo on b.TenantInfoId equals t.Id
orderby b.Date
select new BillingReceivedInfoList
{
Id = b.Id,
TenantInfoId = b.TenantInfoId,
TenantName = t.Name,
Purpose = b.Purpose,
Date = b.Date,
Note = b.Note,
Amount = b.Amount
});
var total = query.Count();
var desQuery = query.OrderByDescending(x => x.Date).ToList();
//List<BillingReceivedInfoList> desQuery = null;
//SEARCHING...
query = query.Where(q => q.TenantInfoId.ToString() == sTenantInfoId || string.IsNullOrEmpty(sTenantInfoId));
if (!string.IsNullOrEmpty(sDateFrom))
{
query = query.Where(q => q.Date >= DateTime.Parse(sDateFrom));
}
if (!string.IsNullOrEmpty(sDateTo))
{
query = query.Where(q => q.Date <= DateTime.Parse(sDateTo));
}
//SORTING... (For sorting we need to add a reference System.Linq.Dynamic)
if (!(string.IsNullOrEmpty(sortColumnName) && string.IsNullOrEmpty(sortColumnDir)))
{
query = query.OrderBy(sortColumnName + " " + sortColumnDir);
}
var filtered = query.Count();
if (pageSize != -1)
{
query = query.Skip(skip).Take(pageSize);
}
return(Json(new { draw, recordsFiltered = filtered, recordsTotal = total, data = query.ToList() },
JsonRequestBehavior.AllowGet));
}
public JsonResult GetList()
{
return(Json(new { info = _tenantInfoManagerger.GetAll().ToList(), status = true }, JsonRequestBehavior.AllowGet));
}