public byte[] GetSummaryOrderByIndividualPVReport(WorkingUser staff, FileStream template,
OrderAdminSearchCriteria searchCriteria)
{
searchCriteria.OrderType = OrderType.PV;
var orderDetails = GetReportQueriedOrderDetails(staff, searchCriteria);
var sessionEndDate = searchCriteria.SessionEndDate.Value.Date;
var hrManager = GetHRManager(orderDetails);
var hrAdmin = GetHRAdmin(orderDetails);
var warehouser = GetWarehouser(orderDetails, searchCriteria);
var tbl = GetSummaryOrderByIndividualPVReportTable(orderDetails);
IDictionary <string, string> additionalCells = new Dictionary <string, string>();
additionalCells.Add("B5", hrAdmin?.Name);
additionalCells.Add("B6", hrAdmin?.FormattedActionDate);
additionalCells.Add("D5", hrManager?.Name);
additionalCells.Add("D6", hrManager?.FormattedActionDate);
additionalCells.Add("G5", warehouser?.Names);
additionalCells.Add("G6", warehouser?.ActionDates);
additionalCells.Add("G2", sessionEndDate.ToString("MM.yyyy") + "/PVEntitlement");
var summaryCols = new List <string> {
"Quantity", "UnitPrice", "TotalPrice", "PV", "TotalPV"
};
var summaryRowColor = "#C6E0B4";
var rawData = _exportManager.ExportToExcelFromDataTable(tbl, additionalCells, summaryCols, summaryRowColor,
template, "A9", staff.IsInRole(UserRole.Warehouse));
return(rawData);
}
private List <OrderDetail> GetQueriedOrderDetails(WorkingUser staff,
OrderAdminSearchCriteria searchCriteria)
{
var query = GetIncludedOrderDetail();
searchCriteria.SessionEndDate = searchCriteria.SessionEndDate ?? GetNearestOrderSessionEndDate();
IQueryable <OrderDetail> orderDetails = null;
if (staff.IsInRole(UserRole.HRAdmin) || staff.IsInRole(UserRole.HRManager))
{
orderDetails = GetFilteredReportOrderDetail(query, searchCriteria);
}
else if (staff.IsInRole(UserRole.Warehouse))
{
orderDetails = GetFilteredReportOrderDetail(query, new OrderAdminSearchCriteria
{
SessionEndDate = searchCriteria.SessionEndDate,
DepartmentId = searchCriteria.DepartmentId,
LocationId = staff.LocationId,
OrderStatus = searchCriteria.OrderStatus ?? OrderStatus.Packaged,
OrderBatchStatus = searchCriteria.OrderBatchStatus,
OrderType = searchCriteria.OrderType
});
}
return(orderDetails.ToList());
}
public SummaryOrderByIndividualPVModel SearchSummaryOrderByIndividualPVModels(
[FromUri] OrderAdminSearchCriteria searchCriteria, [FromUri] PaginationOptions paginationOptions)
{
var tbl = _orderReportService.SearchSummaryOrderByIndividualPV(_workContext.User, paginationOptions,
searchCriteria);
var dataRows = tbl.AsEnumerable().Skip((paginationOptions.PageIndex - 1) * paginationOptions.PageSize).Take(paginationOptions.PageSize);
var model = dataRows.Select(Mapper.Map <DataRow, SummaryOrderByIndividualPVRowModel>).ToList();
var summaryTotalAmount = 0;
decimal summaryTotalPV = 0;
decimal summaryTotalPrice = 0;
if (model.Count > 0)
{
summaryTotalAmount = Convert.ToInt32(tbl.Compute("Sum(Quantity)", ""));
summaryTotalPV = Convert.ToDecimal(tbl.Compute("Sum(TotalPV)", ""));
summaryTotalPrice = Convert.ToDecimal(tbl.Compute("Sum(TotalPrice)", ""));
}
return(new SummaryOrderByIndividualPVModel()
{
Data = model,
TotalItems = tbl.Rows.Count,
SummaryTotalAmount = summaryTotalAmount,
SummaryTotalPV = summaryTotalPV,
SummaryTotalPrice = summaryTotalPrice
});
}
public IHttpActionResult DownloadSummaryDiscountProductReport([FromUri] OrderAdminSearchCriteria searchCriteria)
{
try
{
var path = System.Web.Hosting.HostingEnvironment.MapPath(_appSettings.SummaryDiscountProductTemplate);
byte[] content;
var isWarehouse = _workContext.User.IsInRole(UserRole.Warehouse);
using (FileStream file = File.Open(path, FileMode.Open))
{
content = _orderReportService.GetSummaryDiscountProductReport(
_workContext.User,
file,
searchCriteria);
}
var stream = new MemoryStream(content);
var result = new FileActionResult(stream,
$"SummaryDiscountProduct_{searchCriteria.OrderStatus}_{DateTime.Now.ToString("dd-MM-yyyy")}.xlsx");
return(result);
}
catch (StaffingPurchaseException ex)
{
return(new StaffPurchaseExceptionActionResult(ex.Message));
}
catch (Exception ex)
{
_logger.Error("Failed to download Summary Discount Product Report", ex);
return(new StaffPurchaseExceptionActionResult(_resourceManager.GetString("OrderReport.FailedToReport")));
}
}
private List <OrderDetail> GetReportQueriedOrderDetails(WorkingUser staff,
OrderAdminSearchCriteria searchCriteria)
{
var orderDetails = GetQueriedOrderDetails(staff, searchCriteria);
if (orderDetails == null || !orderDetails.Any())
{
throw new StaffingPurchaseException(_resourceManager.GetString("OrderReport.NoDataToReport"));
}
return(orderDetails);
}
public SummaryDiscountProductModel SearchDiscountOrders([FromUri] OrderAdminSearchCriteria searchCriteria, [FromUri] PaginationOptions paginationOptions)
{
if (_workContext.User?.LocationId == null)
{
return(new SummaryDiscountProductModel());
}
searchCriteria.OrderStatus = OrderStatus.Approved;
var summaryDiscountProductModel = GetDiscountOrders(searchCriteria, paginationOptions);
return(summaryDiscountProductModel);
}
public InternalRequisitionFormModel SearchPVOrders([FromUri] OrderAdminSearchCriteria searchCriteria, [FromUri] PaginationOptions paginationOptions)
{
if (_workContext.User?.LocationId == null)
{
return(new InternalRequisitionFormModel());
}
searchCriteria.OrderStatus = OrderStatus.Approved;
var internalRequisitionFormModel = GetPVOrders(searchCriteria, paginationOptions);
return(internalRequisitionFormModel);
}
public DataTable SearchSummaryOrderByIndividualDiscount(WorkingUser staff, PaginationOptions paginationOptions,
OrderAdminSearchCriteria searchCriteria)
{
searchCriteria.OrderType = OrderType.Cash;
var orderDetails = GetQueriedOrderDetails(staff, searchCriteria);
var tbl = GetSummaryOrderByIndividualDiscountReportTable(orderDetails);
if (!string.IsNullOrWhiteSpace(paginationOptions.SortExpression))
{
tbl.DefaultView.Sort = paginationOptions.SortExpression;
tbl = tbl.DefaultView.ToTable();
}
return(tbl);
}
public DataTable SearchInternalRequisitionForm(WorkingUser staff, PaginationOptions paginationOptions,
OrderAdminSearchCriteria searchCriteria)
{
searchCriteria.OrderType = OrderType.PV;
var orderDetails = GetQueriedOrderDetails(staff, searchCriteria);
var tbl = GetInternalRequisitionFormReportTable(orderDetails);
if (!string.IsNullOrWhiteSpace(paginationOptions.SortExpression))
{
tbl.DefaultView.Sort = paginationOptions.SortExpression;
tbl = tbl.DefaultView.ToTable();
}
return(tbl);
}
private ReportUsers GetWarehouser(List <OrderDetail> orderDetails, OrderAdminSearchCriteria searchCriteria)
{
ReportUsers warehousers = null;
if (searchCriteria.OrderStatus.GetValueOrDefault() != OrderStatus.Packaged)
{
return(null);
}
try
{
var packageLogs = orderDetails.GroupBy(c => c.Order.PackageLogId).Select(c =>
c.FirstOrDefault()?.Order.PackageLog).ToList();
if (packageLogs.Count >= 1)
{
warehousers = new ReportUsers();
var names = new StringBuilder();
var actionDates = new StringBuilder();
for (int i = 0; i < packageLogs.Count; i++)
{
string splitChar;
if (packageLogs.Count == 1 || i == packageLogs.Count - 1)
{
splitChar = "";
}
else
{
splitChar = " | ";
}
var packageLog = packageLogs[i];
names.Append(packageLog.WarehouseUser.FullName + splitChar);
actionDates.Append(packageLog.PackedDate.ToString("HH:mm dd/MM/yyyy") + splitChar);
}
warehousers.Names = names.ToString();
warehousers.ActionDates = actionDates.ToString();
}
}
catch (Exception ex)
{
_logger.Error("GetWarehouses will be ignored although warehouse cannot be retrieved", ex);
}
return(warehousers);
}
public IHttpActionResult DownloadWarehousePackageReport([FromUri] OrderAdminSearchCriteria searchCriteria, [FromUri] OrderType orderType)
{
try
{
var template = orderType == OrderType.Cash
? _appSettings.WarehousePackageDiscountOrderTemplate
: _appSettings.WarehousePackagePVOrderTemplate;
var path = System.Web.Hosting.HostingEnvironment.MapPath(template);
byte[] content = { };
using (var file = File.Open(path, FileMode.Open))
{
searchCriteria.OrderStatus = OrderStatus.Packaged;
switch (orderType)
{
case OrderType.PV:
content = _orderReportService.GetWarehousePackagePVOrderReport(_workContext.User, file,
searchCriteria);
break;
case OrderType.Cash:
content = _orderReportService.GetWarehousePackageDiscountOrderReport(_workContext.User, file,
searchCriteria);
break;
default:
throw new ArgumentOutOfRangeException(nameof(orderType), orderType, null);
}
}
var stream = new MemoryStream(content);
var result = new HttpExtension.FileActionResult(stream, $"WarehousePackageOrderReport_{orderType}_{DateTime.Now.ToString("dd-MM-yyyy")}.xlsx");
return(result);
}
catch (StaffingPurchaseException ex)
{
return(new HttpExtension.StaffPurchaseExceptionActionResult(ex.Message));
}
catch (Exception ex)
{
_logger.Error("Failed to download Warehouse Package Report", ex);
return(new HttpExtension.StaffPurchaseExceptionActionResult(_resourceManager.GetString("OrderReport.FailedToReport")));
}
}
private static IQueryable <OrderDetail> GetFilteredReportOrderDetail(IQueryable <OrderDetail> query,
OrderAdminSearchCriteria searchCriteria)
{
var isEmptyUserName = string.IsNullOrWhiteSpace(searchCriteria.UserName);
var isEmptySku = string.IsNullOrWhiteSpace(searchCriteria.Sku);
if (searchCriteria.DepartmentId.HasValue)
{
query = query.Where(c => c.Order.DepartmentId == searchCriteria.DepartmentId.Value);
}
if (searchCriteria.LocationId.HasValue)
{
query = query.Where(c => c.Order.LocationId == searchCriteria.LocationId.Value);
}
if (searchCriteria.OrderType.HasValue)
{
query = query.Where(c => c.Order.TypeId == (int)searchCriteria.OrderType.Value);
}
if (searchCriteria.OrderBatchStatus.HasValue)
{
query = query.Where(c => c.Order.OrderBatch.StatusId == (int)searchCriteria.OrderBatchStatus);
}
if (!isEmptyUserName)
{
query = query.Where(c => c.Order.User.UserName == searchCriteria.UserName);
}
if (!isEmptySku)
{
query = query.Where(c => c.Product.Sku == searchCriteria.Sku);
}
return(query.Where(c =>
DbFunctions.TruncateTime(c.Order.OrderBatch.EndDate) ==
DbFunctions.TruncateTime(searchCriteria.SessionEndDate.Value) &&
c.Order.StatusId == (int)searchCriteria.OrderStatus));
}
private SummaryDiscountProductModel GetDiscountOrders(OrderAdminSearchCriteria searchCriteria,
PaginationOptions paginationOptions)
{
searchCriteria.OrderBatchStatus = OrderBatchStatus.Approved;
var tbl = _orderReportService.SearchSummaryDiscountProduct(_workContext.User, paginationOptions,
searchCriteria);
var dataRows =
tbl.AsEnumerable()
.Skip((paginationOptions.PageIndex - 1) * paginationOptions.PageSize)
.Take(paginationOptions.PageSize);
var model = dataRows.Select(Mapper.Map <DataRow, SummaryDiscountProductRowModel>).ToList();
int summaryTotalAmount = 0;
decimal summaryTotalDiscountedPrice = 0;
decimal summaryTotalPrice = 0;
if (model.Count > 0)
{
summaryTotalAmount = Convert.ToInt32(tbl.Compute("Sum(Quantity)", ""));
summaryTotalDiscountedPrice = Convert.ToDecimal(tbl.Compute("Sum(TotalDiscountedPrice)", ""));
summaryTotalPrice = Convert.ToDecimal(tbl.Compute("Sum(TotalPrice)", ""));
}
var summaryDiscountProductModel = new SummaryDiscountProductModel()
{
Data = model,
SummaryTotalPrice = summaryTotalPrice,
SummaryTotalAmount = summaryTotalAmount,
SummaryTotalDiscountedPrice = summaryTotalDiscountedPrice,
TotalItems = tbl.Rows.Count
};
return(summaryDiscountProductModel);
}
public byte[] GetSummaryDiscountProductReport(WorkingUser staff, FileStream template,
OrderAdminSearchCriteria searchCriteria)
{
searchCriteria.OrderType = OrderType.Cash;
var orderDetails = GetReportQueriedOrderDetails(staff, searchCriteria);
var sessionEndDate = searchCriteria.SessionEndDate.Value.Date;
var hrManager = GetHRManager(orderDetails);
var hrAdmin = GetHRAdmin(orderDetails);
var warehouser = GetWarehouser(orderDetails, searchCriteria);
var tbl = GetSummaryDiscountProductFormTable(orderDetails);
IDictionary <string, string> additionalCells = new Dictionary <string, string>();
additionalCells.Add("C5", hrAdmin?.Name);
additionalCells.Add("C6", hrAdmin?.FormattedActionDate);
additionalCells.Add("E5", hrManager?.Name);
additionalCells.Add("E6", hrManager?.FormattedActionDate);
additionalCells.Add("H5", warehouser?.Names);
additionalCells.Add("H6", warehouser?.ActionDates);
additionalCells.Add("I1", sessionEndDate.ToString("MM.yyyy") + "/DiscountedProduct");
var summaryCols = new List <string>
{
"Quantity",
"UnitPrice",
"TotalPrice",
"UnitDiscountedPrice",
"TotalDiscountedPrice"
};
var summaryRowColor = "#8EA9DB";
var rawData = _exportManager.ExportToExcelFromDataTable(tbl, additionalCells, summaryCols, summaryRowColor,
template, "A9", staff.IsInRole(UserRole.Warehouse));
return(rawData);
}
public byte[] GetWarehousePackageReport(WorkingUser staff, FileStream template,
OrderAdminSearchCriteria searchCriteria)
{
var orderDetails = GetReportQueriedOrderDetails(staff, searchCriteria);
var sessionStartDate = searchCriteria.SessionEndDate.Value.Date.AddMonths(-2);
var hrManager = GetHRManager(orderDetails);
var hrAdmin = GetHRAdmin(orderDetails);
var isWarehouse = staff.IsInRole(UserRole.Warehouse);
var internRequisitionFormDataSource = new ExcelWorkSheetDataSource
{
Table = GetInternalRequisitionFormReportTable(orderDetails),
StartingCell = "A13",
SummaryColumnHeaders = new List <string> {
"Quantity", "UnitPrice", "TotalPrice", "PV", "TotalPV"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#C6E0B4",
AdditionalCells = new Dictionary <string, string>
{
{ "C5", hrAdmin?.Name },
{ "E5", hrManager?.Name },
{ "I1", sessionStartDate.ToString("MM.yyyy") + "/PVEntitlement" }
},
WorkSheetName = "InternalRequisitionForm"
};
var summaryDiscountProductDataSource = new ExcelWorkSheetDataSource
{
Table = GetSummaryDiscountProductFormTable(orderDetails),
StartingCell = "A9",
SummaryColumnHeaders =
new List <string>
{
"Quantity",
"UnitPrice",
"TotalPrice",
"UnitDiscountedPrice",
"TotalDiscountedPrice"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#8EA9DB",
AdditionalCells = new Dictionary <string, string>
{
{ "C5", hrAdmin?.Name },
{ "E5", hrManager?.Name },
{ "I1", sessionStartDate.ToString("MM.yyyy") + "/DiscountedProduct" }
},
WorkSheetName = "SummaryDiscountProduct"
};
var orderByIndividualPVDataSource = new ExcelWorkSheetDataSource
{
Table = GetSummaryOrderByIndividualPVReportTable(orderDetails),
StartingCell = "A9",
SummaryColumnHeaders = new List <string> {
"Quantity", "UnitPrice", "TotalPrice", "PV", "TotalPV"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#C6E0B4",
AdditionalCells = new Dictionary <string, string>
{
{ "B5", hrAdmin?.Name },
{ "D5", hrManager?.Name },
{ "G2", sessionStartDate.ToString("MM.yyyy") + "/PVEntitlement" }
},
WorkSheetName = "OrderByIndividualPV"
};
var orderByIndividualDiscountDataSource = new ExcelWorkSheetDataSource
{
Table = GetSummaryOrderByIndividualDiscountReportTable(orderDetails),
StartingCell = "A9",
SummaryColumnHeaders =
new List <string>
{
"Quantity",
"UnitPrice",
"TotalPrice",
"UnitDiscountedPrice",
"TotalDiscountedPrice"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#8EA9DB",
AdditionalCells = new Dictionary <string, string>
{
{ "B5", hrAdmin?.Name },
{ "D5", hrManager?.Name },
{ "G2", sessionStartDate.ToString("MM.yyyy") + "/DiscountedProduct" }
},
WorkSheetName = "OrderByIndividualDiscount"
};
var worksheetDataSources = new List <ExcelWorkSheetDataSource>
{
internRequisitionFormDataSource,
orderByIndividualPVDataSource,
summaryDiscountProductDataSource,
orderByIndividualDiscountDataSource
};
var bin = _exportManager.ExportToExcel(worksheetDataSources, template);
return(bin);
}
public byte[] GetWarehousePackageDiscountOrderReport(WorkingUser staff, FileStream template,
OrderAdminSearchCriteria searchCriteria, bool isPreview = false)
{
searchCriteria.OrderType = OrderType.Cash;
var orderDetails = GetReportQueriedOrderDetails(staff, searchCriteria);
var sessionEndDate = searchCriteria.SessionEndDate.Value.Date;
var hrManager = GetHRManager(orderDetails);
var hrAdmin = GetHRAdmin(orderDetails);
var warehouser = GetWarehouser(orderDetails, searchCriteria);
var isWarehouse = staff.IsInRole(UserRole.Warehouse);
var summaryDiscountProductDataSource = new ExcelWorkSheetDataSource
{
Table = GetSummaryDiscountProductFormTable(orderDetails),
StartingCell = "A9",
SummaryColumnHeaders =
new List <string>
{
"Quantity",
"UnitPrice",
"TotalPrice",
"UnitDiscountedPrice",
"TotalDiscountedPrice"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#8EA9DB",
AdditionalCells = !isPreview ? new Dictionary <string, string>
{
{ "C5", hrAdmin?.Name },
{ "C6", hrAdmin?.FormattedActionDate },
{ "E5", hrManager?.Name },
{ "E6", hrManager?.FormattedActionDate },
{ "H5", warehouser?.Names },
{ "H6", warehouser?.ActionDates },
{ "I1", sessionEndDate.ToString("MM.yyyy") + "/DiscountedProduct" }
} : new Dictionary <string, string>
{
{ "I1", sessionEndDate.ToString("MM.yyyy") + "/DiscountedProduct" }
},
WorkSheetName = "SummaryDiscountProduct"
};
var orderByIndividualDiscountDataSource = new ExcelWorkSheetDataSource
{
Table = GetSummaryOrderByIndividualDiscountReportTable(orderDetails),
StartingCell = "A9",
SummaryColumnHeaders =
new List <string>
{
"Quantity",
"UnitPrice",
"TotalPrice",
"UnitDiscountedPrice",
"TotalDiscountedPrice"
},
IsProtectedWithPassword = isWarehouse,
SummaryCellColor = "#8EA9DB",
AdditionalCells = !isPreview ? new Dictionary <string, string>
{
{ "B5", hrAdmin?.Name },
{ "B6", hrAdmin?.FormattedActionDate },
{ "D5", hrManager?.Name },
{ "D6", hrManager?.FormattedActionDate },
{ "G5", warehouser?.Names },
{ "G6", warehouser?.ActionDates },
{ "G2", sessionEndDate.ToString("MM.yyyy") + "/DiscountedProduct" }
} : new Dictionary <string, string>
{
{ "G2", sessionEndDate.ToString("MM.yyyy") + "/DiscountedProduct" }
},
WorkSheetName = "OrderByIndividualDiscount"
};
var worksheetDataSources = new List <ExcelWorkSheetDataSource>
{
summaryDiscountProductDataSource,
orderByIndividualDiscountDataSource
};
var bin = _exportManager.ExportToExcel(worksheetDataSources, template);
return(bin);
}
