public Task <ExportResultResponse> GetExportExcel(PagedOrderResultRequestDto input)
{
var result = GetAll(input).Result.Data.Items;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var products = _productTypeRepository.GetAll().ToList();
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Orders");
int row = 1, col = 1;
worksheet.Cells[row, col++].Value = L("Company");
worksheet.Cells[row, col++].Value = L("Serial");
worksheet.Cells[row, col++].Value = L("DateBooked");
worksheet.Cells[row, col++].Value = L("Sgi");
worksheet.Cells[row, col++].Value = L("SalesRep");
worksheet.Cells[row, col++].Value = L("RepEmail");
worksheet.Cells[row, col++].Value = L("CustomerFirstName");
worksheet.Cells[row, col++].Value = L("CustomerLastName");
worksheet.Cells[row, col++].Value = L("ContactPhone");
worksheet.Cells[row, col++].Value = L("Email");
worksheet.Cells[row, col++].Value = L("DateOfBirth");
worksheet.Cells[row, col++].Value = L("FirstIdentification");
worksheet.Cells[row, col++].Value = L("SecondIdentification");
worksheet.Cells[row, col++].Value = L("ExistingAccountNo");
worksheet.Cells[row, col++].Value = L("StreetNo");
worksheet.Cells[row, col++].Value = L("CustomerAddress");
worksheet.Cells[row, col++].Value = L("Unit");
worksheet.Cells[row, col++].Value = L("City");
worksheet.Cells[row, col++].Value = L("PostalCode");
worksheet.Cells[row, col++].Value = L("PromoDetails");
foreach (var product in products)
{
worksheet.Cells[row, col++].Value = product.Name;
}
worksheet.Cells[row, col++].Value = L("TimeSlot");
worksheet.Cells[row, col++].Value = L("Notes");
worksheet.Cells[row, col++].Value = L("OrderNo");
worksheet.Cells[row, col++].Value = L("AccountNo");
worksheet.Cells[row, col++].Value = L("InstallDate");
worksheet.Cells[row, col++].Value = L("OrderState");
worksheet.Cells[row, col++].Value = L("Remarks");
worksheet.Cells[row, col++].Value = L("Followed");
worksheet.Cells[row, col++].Value = L("Explanation");
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Invoice))
{
worksheet.Cells[row, col++].Value = L("PaymentStatus");
worksheet.Cells[row, col++].Value = L("InvoiceNo");
}
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Ready))
{
worksheet.Cells[row, col++].Value = L("IsReady");
}
worksheet.Cells[ExcelRange.GetAddress(1, 1, 1, col)].Style.Font.Bold = true;
foreach (var item in result.ToList())
{
row++;
col = 1;
worksheet.Cells[row, col++].Value = item.Company.Name;
worksheet.Cells[row, col++].Value = item.Serial;
worksheet.Cells[row, col++].Value = item.DateBooked;
worksheet.Cells[row, col++].Value = item.Sgi;
worksheet.Cells[row, col++].Value = item.SalesRep.Name;
worksheet.Cells[row, col++].Value = item.SalesRep.EmailAddress;
worksheet.Cells[row, col++].Value = item.CustomerFirstName;
worksheet.Cells[row, col++].Value = item.CustomerLastName;
worksheet.Cells[row, col++].Value = item.ContactPhone;
worksheet.Cells[row, col++].Value = item.Email;
worksheet.Cells[row, col++].Value = item.DateOfBirth;
worksheet.Cells[row, col++].Value = item.FirstIdentification.Name;
worksheet.Cells[row, col++].Value = item.SecondIdentification.Name;
worksheet.Cells[row, col++].Value = item.ExistingAccountNo;
worksheet.Cells[row, col++].Value = item.StreetNo;
worksheet.Cells[row, col++].Value = item.CustomerAddress;
worksheet.Cells[row, col++].Value = item.Unit;
worksheet.Cells[row, col++].Value = item.City;
worksheet.Cells[row, col++].Value = item.PostalCode;
worksheet.Cells[row, col++].Value = item.PromoDetails;
var dic = item.OrdersProductType.ToList().ToDictionary(t => t.ProductTypeId, t => t.ProductTypeId);
foreach (var product in products)
{
worksheet.Cells[row, col++].Value = dic.ContainsKey(product.Id) ? "1": "0";
}
worksheet.Cells[row, col++].Value = item.TimeSlot.Name;
worksheet.Cells[row, col++].Value = item.Notes;
worksheet.Cells[row, col++].Value = item.OrderNo;
worksheet.Cells[row, col++].Value = item.AccountNo;
worksheet.Cells[row, col++].Value = item.InstallDate;
worksheet.Cells[row, col++].Value = item.OrderState.Name;
worksheet.Cells[row, col++].Value = item.Remarks;
worksheet.Cells[row, col++].Value = item.Followed;
worksheet.Cells[row, col++].Value = item.Explanation;
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Invoice))
{
worksheet.Cells[row, col++].Value = item.PaymentStatus.Name;
worksheet.Cells[row, col++].Value = item.InvoiceNo;
}
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Ready))
{
worksheet.Cells[row, col++].Value = item.IsReady;
}
}
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
var response = Task.FromResult(new ExportResultResponse {
FileName = "Orders.xlsx",
Data = package.GetAsByteArray()
});
return(response);
}
}
public async Task <PagedOrderResultResponseDto> GetAll(PagedOrderResultRequestDto input)
{
var userId = _session.UserId.GetValueOrDefault();
var query = _orderRepository.GetAll();
//////Filters by page
if (input.DateFrom.HasValue)//Filter by DateFrom
{
query = query.Where(x => x.DateBooked.Date >= input.DateFrom.Value.Date);
}
if (input.DateTo.HasValue)//Filter by DateTo
{
query = query.Where(x => x.DateBooked.Date <= input.DateTo.Value.Date);
}
if (!input.Followed.IsNullOrEmpty())//Filter by Followed
{
query = query.Where(x => x.Followed == input.Followed);
}
int[] arrayCompany = ConvertToArrayInt(input.CompanyId);//Filter by company
if (arrayCompany.Length >= 1)
{
query = query.Where(x => arrayCompany.Contains(x.CompanyId));
}
int[] arrayOrderState = ConvertToArrayInt(input.OrderStateId);//Filter by order state
if (arrayOrderState.Length >= 1)
{
query = query.Where(x => arrayOrderState.Contains(x.OrderStateId));
}
if (!input.Sgi.IsNullOrEmpty())//Filter by Sgi
{
query = query.Where(x => x.Sgi == input.Sgi);
}
if (!input.InvoiceNo.IsNullOrEmpty())//Filter by InvoiceNo
{
query = query.Where(x => x.InvoiceNo == input.InvoiceNo);
}
if (input.PaymentStatusId != 0)//Filter by InvoiceNo
{
query = query.Where(x => x.PaymentStatusId == input.PaymentStatusId);
}
//////Filters by page
var permissionOrderSeeAll = PermissionChecker.IsGranted(PermissionNames.Order_See_All); //validation for see all orders
if (!permissionOrderSeeAll)
{
var listSubSalesRep = _subSalesRepRepository.GetAll()//validation for sub salesRep
.Where(t => t.SalesRepId == userId).Select(t => t.SubSalesRepr.Id).ToList();
if (listSubSalesRep.Count() >= 1)
{
listSubSalesRep.Add(userId);
query = query.Where(x => listSubSalesRep.Contains(x.SalesRepId));
}
else
{
query = query.Where(x => x.SalesRepId == userId);
}
}
var listCompany = _salesRepCompanyRepository.GetAll()//validation for see only company assigned
.Where(t => t.SalesRepId == userId).Select(t => t.Company.Id).ToArray();
query = query.Where(x => listCompany.Contains(x.Company.Id));
var permissionOrderReady = PermissionChecker.IsGranted(PermissionNames.Order_Ready);
var permissionOrderAdminReady = PermissionChecker.IsGranted(PermissionNames.Order_Admin_Ready);
if (permissionOrderReady && !permissionOrderAdminReady)//validation for order ready to booking
{
query = query.Where(x => x.IsReady == true);
}
var ordersList = query
.Include(t => t.OrderState)
.Include(t => t.Company)
.Include(t => t.SalesRep)
.Include(t => t.TimeSlot)
.Include(t => t.FirstIdentification)
.Include(t => t.SecondIdentification)
.Include(t => t.OrdersProductType)
.Include(t => t.PaymentStatus)
.OrderByDescending(t => t.Id)
.ToList();
int count = ordersList.Count();
var newList = ordersList.Skip(input.SkipCount).Take(input.MaxResultCount);
var product = _productTypeRepository.GetAll().ToList();
List <ProductTypeDto> productTypeDto = new List <ProductTypeDto>();
productTypeDto = ObjectMapper.Map(product, productTypeDto);
PagedOrderResultResponseDto pagedOrderResultResponseDto = new PagedOrderResultResponseDto();
pagedOrderResultResponseDto.TotalCount = count;
pagedOrderResultResponseDto.ProductType = productTypeDto;
pagedOrderResultResponseDto.Data = new ListResultDto <OrderListDto>(
ObjectMapper.Map <List <OrderListDto> >(newList));
return(pagedOrderResultResponseDto);
}
public PagedResultDto <OrderListDto> GetOrders(PagedOrderResultRequestDto inputDto)
{
var query = from o in _orderRepository.GetAll()
select o;
if (IsGranted(PermissionNames.Data_Admin))
{
}
else if (IsGranted(PermissionNames.Data_Taobao))
{
query = query.Where(q => q.CreatorUserId == AbpSession.UserId);
}
else if (IsGranted(PermissionNames.Data_Usa))
{
query = query.Where(q => q.AccountId == AbpSession.UserId);
}
if (string.IsNullOrEmpty(inputDto.Key) == false)
{
query = query.Where(q => q.TaobaoShop.Contains(inputDto.Key) ||
q.ProductName.Contains(inputDto.Key) ||
q.TransportNumber == inputDto.Key ||
q.UsaLogisticsNumber == inputDto.Key ||
q.ChinaLogisticsNumber == inputDto.Key
);
}
query = query.WhereIf(Convert.ToInt16(inputDto.OrderType) > -1, q => q.State == inputDto.OrderType)
.WhereIf(inputDto.DwonTo.HasValue, q => q.ChinaDateTime <= inputDto.DwonTo)
.WhereIf(inputDto.DwonFrom.HasValue, q => q.ChinaDateTime >= inputDto.DwonFrom)
.WhereIf(inputDto.ComplateFrom.HasValue, q => q.LastModificationTime >= inputDto.ComplateFrom && q.State == OrderType.交易成功)
.WhereIf(inputDto.ComplateTo.HasValue, q => q.LastModificationTime <= inputDto.ComplateTo && q.State == OrderType.交易成功);
query.OrderBy(q => q.State).OrderByDescending(q => q.LastModificationTime).OrderBy(q => q.TransportNumber);
var orders = query.Skip(inputDto.SkipCount).Take(inputDto.MaxResultCount).ToList();
var userIds = orders.Select(q => q.AccountId).ToList();
userIds.AddRange(orders.Select(q => q.CreatorUserId ?? 0).ToList());
userIds = userIds.Distinct().ToList();
var users = _userRepository.GetAll().Where(q => userIds.Contains(q.Id)).ToList();
foreach (var item in orders)
{
var account = users.Where(q => q.Id == item.AccountId).FirstOrDefault();
var create = users.Where(q => q.Id == item.CreatorUserId).FirstOrDefault();
if (account != null)
{
item.Account = account.Name;
}
if (create != null)
{
item.TaobaoShop = create.Name;
}
if (item.OtherDesc != null)
{
item.OtherDesc = item.OtherDesc.Trim('\n').Trim('\r').Replace("\n", "<br/>");
}
}
return(new PagedResultDto <OrderListDto>(query.Count(), ObjectMapper.Map <List <OrderListDto> >(orders)));
}
