public List <T_OutStandingDetails> GetOutStandingDetailList(T_OutStanding ObjOutStanding)
{
var ObjList = new List <T_OutStandingDetails>();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var vList = (from OutStandingDetail in _objUnitOfWork._T_OutStandingDetails_Repository.Get()
join OutStanding in _objUnitOfWork._T_OutStanding_Repository.Get() on OutStandingDetail.OSId equals OutStanding.Id
where OutStandingDetail.OSId == ObjOutStanding.Id
select new T_OutStandingDetails()
{
Id = OutStandingDetail.Id,
EventDate = OutStandingDetail.EventDate,
OSId = OutStandingDetail.OSId,
SlNo = OutStandingDetail.SlNo,
OutStandingAmount = OutStandingDetail.OutStandingAmount,
PaidAmount = OutStandingDetail.PaidAmount,
DueAmount = OutStandingDetail.DueAmount,
Remark = OutStandingDetail.Remark,
CreatedDate = OutStandingDetail.CreatedDate
}).OrderBy(x => x.SlNo).ToList();
ObjList = vList;
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjList);
}
public T_OutStandingDetails GetOutStandingOutStandingDetail(T_OutStanding ObjOutStanding)
{
var ObjList = new T_OutStandingDetails();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var queryOutStanding = _objUnitOfWork._T_OutStanding_Repository.Query();
var queryOutStandingDetail = _objUnitOfWork._T_OutStandingDetails_Repository.Query();
var vOutStanding = queryOutStanding.Where(x => x.Id == ObjOutStanding.Id).FirstOrDefault();
var vOutStandingDetail = queryOutStandingDetail.Where(x => x.OSId == ObjOutStanding.Id).ToList();
if (vOutStanding != null)
{
ObjList = vOutStandingDetail.ToList().Select(x => new T_OutStandingDetails()
{
OutStandingAmount = (vOutStanding.Amount - vOutStandingDetail.Sum(y => y.PaidAmount)),
}).FirstOrDefault();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjList);
}
public JsonResult GetOutStandingOutStandingDetail(T_OutStanding ObjOutStanding)
{
var ObjList = new T_OutStandingDetails();
try
{
if (_blOutStanding.GetOutStandingDetailList(ObjOutStanding).Count > 0)
{
ObjList = _blOutStanding.GetOutStandingOutStandingDetail(ObjOutStanding);
}
else
{
var t_OutStanding = _blOutStanding.GetById(ObjOutStanding.Id);
if (t_OutStanding != null)
{
ObjList.TotalOutStandingAmount = t_OutStanding.Amount;
ObjList.OutStandingAmount = t_OutStanding.Amount;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Json(new { ObjList, Message = "Sucess" }, JsonRequestBehavior.AllowGet));
}
public List <T_OutStanding> GetList(T_OutStanding ObjOutStanding)
{
var ObjList = new List <T_OutStanding>();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
var queryOutStanding = _objUnitOfWork._T_OutStanding_Repository.Query();
var queryOutStandingDetails = _objUnitOfWork._T_OutStandingDetails_Repository.Query();
var querySalesOrder = _objUnitOfWork._T_SalesOrder_Repository.Query();
if (ObjOutStanding.EventDate != null && ObjOutStanding.EventDate.ToString("yyyy-MM-dd") != "0001-01-01")
{
queryOutStanding = queryOutStanding.Where(x => x.EventDate == ObjOutStanding.EventDate);
}
if (!string.IsNullOrWhiteSpace(ObjOutStanding.OSNumber))
{
queryOutStanding = queryOutStanding.Where(x => x.OSNumber.Contains(ObjOutStanding.OSNumber));
}
if (!string.IsNullOrWhiteSpace(ObjOutStanding.Name))
{
queryOutStanding = queryOutStanding.Where(x => x.Name.Contains(ObjOutStanding.Name));
}
queryOutStanding = queryOutStanding.OrderByDescending(x => x.CreatedDate);
ObjList = (from os in queryOutStanding
join so in querySalesOrder on os.SalesOrderId equals so.Id into OsSoJoin
from OsObj in OsSoJoin.DefaultIfEmpty()
select new
{
id = os.Id,
oSNumber = os.OSNumber,
salesOrderId = OsObj.OrderId,
amount = os.Amount,
eventDate = os.EventDate,
name = os.Name,
remark = os.Remark,
createdDate = os.CreatedDate
}).ToList().Select(x => new T_OutStanding
{
Id = x.id,
OSNumber = x.oSNumber,
SalesOrderNumber = x.salesOrderId,
Amount = x.amount,
PaidAmount = queryOutStandingDetails.Where(s => s.OSId == x.id).ToList().Sum(y => y.PaidAmount),
DueAmount = (x.amount - queryOutStandingDetails.Where(s => s.OSId == 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 ActionResult Edit(T_OutStanding ObjModel)
{
try
{
if (ModelState.IsValid)
{
var vObj = _blOutStanding.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;
_blOutStanding.Update(vObj);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(RedirectToAction("Index", "OutStanding"));
}
public JsonResult GetOutStandingDetailList(T_OutStanding ObjOutStanding)
{
var ObjList = new List <T_OutStandingDetails>();
try
{
ObjList = _blOutStanding.GetOutStandingDetailList(ObjOutStanding);
}
catch (Exception ex)
{
throw ex;
}
return(Json(ObjList, JsonRequestBehavior.AllowGet));
}
public T_OutStanding Delete(T_OutStanding ObjOutStanding)
{
try
{
using (_objUnitOfWork = new UnitOfWork())
{
_objUnitOfWork._T_OutStanding_Repository.Delete(ObjOutStanding.Id);
_objUnitOfWork.Save();
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjOutStanding);
}
public T_OutStanding GetFirstOrDefault(T_OutStanding ObjOutStanding)
{
var ReturnCompanyObj = new T_OutStanding();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
ReturnCompanyObj = _objUnitOfWork._T_OutStanding_Repository.GetFirstOrDefault(x => x.OSNumber == ObjOutStanding.OSNumber);
}
}
catch (Exception ex)
{
throw ex;
}
return(ReturnCompanyObj);
}
public T_OutStanding GetById(Guid Id)
{
var ObjOutStanding = new T_OutStanding();
try
{
using (_objUnitOfWork = new UnitOfWork())
{
ObjOutStanding = _objUnitOfWork._T_OutStanding_Repository.GetById(Id);
}
}
catch (Exception ex)
{
throw ex;
}
return(ObjOutStanding);
}
public ActionResult Create(T_OutStanding ObjOutStanding)
{
try
{
bool bAnyError = false;
if (ModelState.IsValid)
{
var vNameExists = _blOutStanding.GetFirstOrDefault(ObjOutStanding);
if (vNameExists != null)
{
ViewBag.ErrorMsg = "Order No alreay exists in our system";
bAnyError = true;
}
if (bAnyError == false)
{
T_OutStanding _Obj_T_OutStanding = new T_OutStanding()
{
Id = Guid.NewGuid(),
EventDate = Convert.ToDateTime(ObjOutStanding.EventDate),
SalesOrderId = ObjOutStanding.SalesOrderId,
OSNumber = ObjOutStanding.OSNumber,
Amount = ObjOutStanding.Amount,
Name = ObjOutStanding.Name,
Remark = ObjOutStanding.Remark,
CreatedBy = _objAuthentication.UserName,
CreatedDate = DateTime.Now
};
var vReturnObj = _blOutStanding.Create(_Obj_T_OutStanding);
}
}
else
{
bAnyError = true;
}
if (bAnyError)
{
return(View(ObjOutStanding));
}
return(RedirectToAction("Index", "OutStanding"));
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult Edit(string Id)
{
var model = new T_OutStanding();
if (!string.IsNullOrWhiteSpace(Id))
{
var vDetails = _blOutStanding.GetById(new Guid(Id));
if (vDetails != null)
{
model.EventDate = Convert.ToDateTime(vDetails.EventDate);
model.OSNumber = vDetails.OSNumber;
model.Name = vDetails.Name;
model.Amount = vDetails.Amount;
model.Remark = vDetails.Remark;
}
}
return(View(model));
}
public ActionResult Delete(T_OutStanding ObjOutStanding)
{
try
{
if (!string.IsNullOrWhiteSpace(Convert.ToString(ObjOutStanding.Id)))
{
var vObj = _blOutStanding.GetById(ObjOutStanding.Id);
if (vObj != null)
{
_blOutStanding.Delete(ObjOutStanding);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Json(ObjOutStanding));
}
public ActionResult ExportToExcel(string Id)
{
if (string.IsNullOrWhiteSpace(Id))
{
return(null);
}
T_OutStanding ObjItem = new T_OutStanding()
{
Id = Guid.Parse(Id)
};
var vObjList = _blOutStanding.GetOutStandingDetailList(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());
}