private System.Web.Mvc.FileResult ReportSalesOrderExtract(SalesOrderSearchModel searchModel)
{
int rowId = 0;
int colId = 0;
var list = GetSalesOrderReport(searchModel).ToList();
var dir = Server.MapPath(string.Format("~/{0}", Constants.ProductExcelTemplateDir));
var fileNameTemplate = string.Format("{0}{1}", Constants.ReportSOTemplate, ".xlsx");
var path = System.IO.Path.Combine(dir, fileNameTemplate);
var fileNameGenerated = string.Format("{0}{1}", Constants.ReportPerSalesOrder, ".xlsx");
var contentType = "application/vnd.ms-excel";
var templateFile = new FileInfo(path);
//var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(path);
var package = new ExcelPackage(templateFile);
var workSheet = package.Workbook.Worksheets[1];
rowId = 2;
foreach (var detail in list)
{
var salesOrderList = _orderService.GetAllSalesOrderDetail(detail.SalesOrderId).ToList();
foreach (var salesDetail in salesOrderList)
{
workSheet.Cells["A" + rowId.ToString()].Value = detail.SalesNo;
workSheet.Cells["B" + rowId.ToString()].Value = detail.customer.CustomerDropDownDisplay;
workSheet.Cells["C" + rowId.ToString()].Value = salesDetail.product.ProductInfoDisplay;
workSheet.Cells["D" + rowId.ToString()].Value = salesDetail.UnitPrice;
workSheet.Cells["E" + rowId.ToString()].Value = salesDetail.SalesPrice;
workSheet.Cells["F" + rowId.ToString()].Value = salesDetail.Quantity;
workSheet.Cells["G" + rowId.ToString()].Value = detail.DateCreatedWithFormat;
rowId++;
}
}
var memoryStream = new MemoryStream();
//package.Save();
package.SaveAs(memoryStream);
memoryStream.Position = 0;
return(File(memoryStream, contentType, fileNameGenerated));
}
private IQueryable <ReportSalesOrderReceiptDto> GetSalesOrderReceiptReport(SalesOrderSearchModel searchModel)
{
IQueryable <ReportSalesOrderReceiptDto> result = null;
if ((searchModel.DateFrom.IsNull() && searchModel.DateTo.IsNull() && searchModel.CustomerId == 0 && searchModel.SalesNo.IsNull()))
{
result = _orderService.GetAllSalesOrderReceiptReport().Where(s => s.IsPrinted && !s.IsCorrected && s.CustomerId != Constants.CustomerIdAdmin && DbFunctions.TruncateTime(s.DateCreated) == DbFunctions.TruncateTime(DateTime.Now));
}
else
{
var predicate = PredicateBuilder.True <ReportSalesOrderReceiptDto>();
if (searchModel.CustomerId != 0)
{
predicate = predicate.And(p => p.CustomerId == searchModel.CustomerId);
}
if (!searchModel.SalesNo.IsNull())
{
predicate = predicate.And(p => p.SalesNo.Contains(searchModel.SalesNo));
}
if (!searchModel.DateFrom.IsNull() && searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) >= DbFunctions.TruncateTime(searchModel.DateFrom));
}
else if (searchModel.DateFrom.IsNull() && !searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) <= DbFunctions.TruncateTime(searchModel.DateTo));
}
else if (!searchModel.DateFrom.IsNull() && !searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) >= DbFunctions.TruncateTime(searchModel.DateFrom) && DbFunctions.TruncateTime(a.DateCreated) <= DbFunctions.TruncateTime(searchModel.DateTo));
}
predicate = predicate.And(s => s.IsPrinted && !s.IsCorrected && s.CustomerId != Constants.CustomerIdAdmin);
result = _orderService.GetAllSalesOrderReceiptReport().AsExpandable().Where(predicate);
}
return(result);
}
public async Task <IHttpActionResult> GetSalesOrderSearch([FromUri] SalesOrderSearchModel model)
{
List <SalesOrderHeader> items = await _svc.Value.GetAllSalesOrderHeaderAsync(model?.CustomerName, model?.DueDateEnd, model?.DueDateStart, model?.OrderDateEnd, model?.OrderDateStart, model?.ShipDateEnd, model?.ShipDateStart);
return(Ok(items));
}
public virtual ActionResult GetSOReceiptReport([DataSourceRequest] DataSourceRequest request, SalesOrderSearchModel searchModel)
{
var list = GetSalesOrderReceiptReport(searchModel);
return(Json(list.ToDataSourceResult(request), JsonRequestBehavior.AllowGet));
}
public virtual ActionResult ExportSOExcel(SalesOrderSearchModel searchModel)
{
return(ReportSalesOrderExtract(searchModel));
}
