public JsonResult GetLoanOutStandingDetail(T_Loan ObjLoan)
{
var ObjList = new T_LoanDetails();
try
{
if (_blLoan.GetLoanDetailList(ObjLoan).Count > 0)
{
ObjList = _blLoan.GetLoanOutStandingDetail(ObjLoan);
}
else
{
var t_Loan = _blLoan.GetById(ObjLoan.Id);
if (t_Loan != null)
{
ObjList.TotalLoanAmount = t_Loan.Amount;
ObjList.OutStandingAmount = t_Loan.Amount;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Json(new { ObjList, Message = "Sucess" }, JsonRequestBehavior.AllowGet));
}
public T_LoanDetails GetLoanOutStandingDetail(T_Loan ObjLoan)
{
var ObjList = new T_LoanDetails();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var queryLoan = _objUnitOfWork._T_Loan_Repository.Query();
var queryLoanDetail = _objUnitOfWork._T_LoanDetails_Repository.Query();
var vLoan = queryLoan.Where(x => x.Id == ObjLoan.Id).FirstOrDefault();
var vLoanDetail = queryLoanDetail.Where(x => x.LoanId == ObjLoan.Id).ToList();
if (vLoan != null)
{
ObjList = vLoanDetail.ToList().Select(x => new T_LoanDetails()
{
OutStandingAmount = (vLoan.Amount - vLoanDetail.Sum(y => y.PaidAmount)),
}).FirstOrDefault();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjList);
}
public ActionResult Edit(T_Loan ObjModel)
{
try
{
if (ModelState.IsValid)
{
var vObj = _blLoan.GetById(ObjModel.Id);
if (vObj != null)
{
vObj.EventDate = Convert.ToDateTime(ObjModel.EventDate);
vObj.Name = ObjModel.Name;
vObj.Amount = ObjModel.Amount;
vObj.Remark = ObjModel.Remark;
vObj.ModifyBy = _objAuthentication.UserName;
vObj.ModifyDate = DateTime.Now;
_blLoan.Update(vObj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(RedirectToAction("Index", "Loan"));
}
public List <T_LoanDetails> GetLoanDetailList(T_Loan ObjLoan)
{
var ObjList = new List <T_LoanDetails>();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var vList = (from LoanDetail in _objUnitOfWork._T_LoanDetails_Repository.Get()
join Loan in _objUnitOfWork._T_Loan_Repository.Get() on LoanDetail.LoanId equals Loan.Id
where LoanDetail.LoanId == ObjLoan.Id
select new T_LoanDetails()
{
Id = LoanDetail.Id,
EventDate = LoanDetail.EventDate,
LoanId = LoanDetail.LoanId,
SlNo = LoanDetail.SlNo,
OutStandingAmount = LoanDetail.OutStandingAmount,
PaidAmount = LoanDetail.PaidAmount,
DueAmount = LoanDetail.DueAmount,
Remark = LoanDetail.Remark,
CreatedDate = LoanDetail.CreatedDate
}).OrderBy(x => x.SlNo).ToList();
ObjList = vList;
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjList);
}
public JsonResult GetLoanDetailList(T_Loan ObjLoan)
{
var ObjList = new List <T_LoanDetails>();
try
{
ObjList = _blLoan.GetLoanDetailList(ObjLoan);
}
catch (Exception ex)
{
throw ex;
}
return(Json(ObjList, JsonRequestBehavior.AllowGet));
}
public T_Loan Delete(T_Loan ObjLoan)
{
try
{
using (_objUnitOfWork = new UnitOfWork())
{
_objUnitOfWork._T_Loan_Repository.Delete(ObjLoan.Id);
_objUnitOfWork.Save();
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjLoan);
}
public List <T_Loan> GetList(T_Loan ObjLoan)
{
var ObjList = new List <T_Loan>();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var queryLoan = _objUnitOfWork._T_Loan_Repository.Query();
var queryLoanDetails = _objUnitOfWork._T_LoanDetails_Repository.Query();
if (ObjLoan.EventDate != null && ObjLoan.EventDate.ToString("yyyy-MM-dd") != "0001-01-01")
{
queryLoan = queryLoan.Where(x => x.EventDate == ObjLoan.EventDate);
}
if (!string.IsNullOrWhiteSpace(ObjLoan.LoanNumber))
{
queryLoan = queryLoan.Where(x => x.LoanNumber.Contains(ObjLoan.LoanNumber));
}
if (!string.IsNullOrWhiteSpace(ObjLoan.Name))
{
queryLoan = queryLoan.Where(x => x.Name.Contains(ObjLoan.Name));
}
queryLoan = queryLoan.OrderByDescending(x => x.CreatedDate);
var vList = queryLoan.ToList();
ObjList = vList.Select(x => new T_Loan
{
Id = x.Id,
LoanNumber = x.LoanNumber,
Amount = x.Amount,
PaidAmount = queryLoanDetails.Where(s => s.LoanId == x.Id).ToList().Sum(y => y.PaidAmount),
DueAmount = (x.Amount - queryLoanDetails.Where(s => s.LoanId == x.Id).ToList().Sum(y => y.PaidAmount)),
EventDate = x.EventDate,
Name = x.Name,
Remark = x.Remark,
CreatedDate = x.CreatedDate
}).ToList();
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjList);
}
public T_Loan GetFirstOrDefault(T_Loan ObjLoan)
{
var ReturnCompanyObj = new T_Loan();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
ReturnCompanyObj = _objUnitOfWork._T_Loan_Repository.GetFirstOrDefault(x => x.LoanNumber == ObjLoan.LoanNumber);
}
}
catch (Exception ex)
{
throw ex;
}
return(ReturnCompanyObj);
}
public T_Loan GetById(Guid Id)
{
var ObjLoan = new T_Loan();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
ObjLoan = _objUnitOfWork._T_Loan_Repository.GetById(Id);
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjLoan);
}
public ActionResult Create(T_Loan ObjLoan)
{
try
{
bool bAnyError = false;
if (ModelState.IsValid)
{
var vNameExists = _blLoan.GetFirstOrDefault(ObjLoan);
if (vNameExists != null)
{
ViewBag.ErrorMsg = "Order No alreay exists in our system";
bAnyError = true;
}
if (bAnyError == false)
{
T_Loan _Obj_T_Loan = new T_Loan()
{
Id = Guid.NewGuid(),
EventDate = Convert.ToDateTime(ObjLoan.EventDate),
LoanNumber = ObjLoan.LoanNumber,
Amount = ObjLoan.Amount,
Name = ObjLoan.Name,
Remark = ObjLoan.Remark,
CreatedBy = _objAuthentication.UserName,
CreatedDate = DateTime.Now
};
var vReturnObj = _blLoan.Create(_Obj_T_Loan);
}
}
else
{
bAnyError = true;
}
if (bAnyError)
{
return(View(ObjLoan));
}
return(RedirectToAction("Index", "Loan"));
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult Edit(string Id)
{
var model = new T_Loan();
if (!string.IsNullOrWhiteSpace(Id))
{
var vDetails = _blLoan.GetById(new Guid(Id));
if (vDetails != null)
{
model.EventDate = Convert.ToDateTime(vDetails.EventDate);
model.LoanNumber = vDetails.LoanNumber;
model.Name = vDetails.Name;
model.Amount = vDetails.Amount;
model.Remark = vDetails.Remark;
}
}
return(View(model));
}
public ActionResult Delete(T_Loan ObjLoan)
{
try
{
if (!string.IsNullOrWhiteSpace(Convert.ToString(ObjLoan.Id)))
{
var vObj = _blLoan.GetById(ObjLoan.Id);
if (vObj != null)
{
_blLoan.Delete(ObjLoan);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Json(ObjLoan));
}
public ActionResult ExportToExcel(string Id)
{
if (string.IsNullOrWhiteSpace(Id))
{
return(null);
}
T_Loan ObjItem = new T_Loan()
{
Id = Guid.Parse(Id)
};
var vObjList = _blLoan.GetLoanDetailList(ObjItem);
// ExcelPackageF dll Need
OfficeOpenXml.ExcelPackage Ep = new OfficeOpenXml.ExcelPackage();
OfficeOpenXml.ExcelWorksheet workSheet = Ep.Workbook.Worksheets.Add("Report");
string sEndColumn = "J1";
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Font.Bold = true;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightSeaGreen);
var allCells = workSheet.Cells[string.Format("A1:{0}", sEndColumn)];
var cellFont = allCells.Style.Font;
workSheet.Cells["A1"].Value = "Slno";
//sheet["A3:A4"].Merge();
workSheet.Cells["B1"].Value = "Item";
workSheet.Cells["C1"].Value = "Brand";
workSheet.Cells["D1"].Value = "Guage";
workSheet.Cells["E1"].Value = "Stock Quantity";
workSheet.Cells["F1"].Value = "Quantity";
workSheet.Cells["G1"].Value = "UOM";
workSheet.Cells["H1"].Value = "Latest Price";
workSheet.Cells["I1"].Value = "Selling Price";
workSheet.Cells["J1"].Value = "Created Dated";
string dateformat = "dd-MM-yyyy";
int row = 2;
foreach (var item in vObjList)
{
//workSheet.Cells[string.Format("A{0}", row)].Value = item.EventDate;
//workSheet.Cells[string.Format("A{0}", row)].Style.Numberformat.Format = dateformat;
workSheet.Cells[string.Format("A{0}", row)].Value = item.SlNo;
//workSheet.Cells[string.Format("B{0}", row)].Value = item.ItemsName;
//workSheet.Cells[string.Format("C{0}", row)].Value = item.BrandName;
//workSheet.Cells[string.Format("D{0}", row)].Value = item.GuageName;
//workSheet.Cells[string.Format("E{0}", row)].Value = item.StockQuantity;
//workSheet.Cells[string.Format("F{0}", row)].Value = item.Quantity;
//workSheet.Cells[string.Format("G{0}", row)].Value = item.UomName;
//workSheet.Cells[string.Format("H{0}", row)].Value = item.LatestPrice;
//workSheet.Cells[string.Format("I{0}", row)].Value = item.SellingPrice;
workSheet.Cells[string.Format("J{0}", row)].Value = item.CreatedDate;
workSheet.Cells[string.Format("J{0}", row)].Style.Numberformat.Format = dateformat;
row++;
}
workSheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "Expense.csv" + DateTime.Now.ToString("s"));
Response.BinaryWrite(Ep.GetAsByteArray());
Response.End();
return(View());
}
