public virtual ActionResult Index()
{
LogI("Index");
var model = new DhlInvoiceFilterViewModel();
return(View(model));
}
public virtual ActionResult GetAll([DataSourceRequest] DataSourceRequest request,
string orderNumber,
DateTime?dateFrom,
DateTime?dateTo,
int?status)
{
LogI("GetAll, orderNumber=" + orderNumber
+ ", status=" + status
+ ", dateFrom=" + dateFrom
+ ", dateTo=" + dateTo);
var searchFilter = new DhlInvoiceFilterViewModel()
{
OrderNumber = orderNumber,
DateFrom = dateFrom,
DateTo = dateTo,
Status = status,
};
var items = DhlInvoiceViewModel.GetAll(Db, Time, searchFilter);
var dataSource = items.ToDataSourceResult(request);
return(Json(dataSource, JsonRequestBehavior.AllowGet));
}
public virtual ActionResult ExportToExcel(string orderNumber,
DateTime?dateFrom,
DateTime?dateTo,
int?status)
{
LogI("ExportToExcel, orderNumber=" + orderNumber
+ ", status=" + status
+ ", dateFrom=" + dateFrom
+ ", dateTo=" + dateTo);
var searchFilter = new DhlInvoiceFilterViewModel()
{
OrderNumber = orderNumber,
DateFrom = dateFrom,
DateTo = dateTo,
Status = status,
};
var invoices = DhlInvoiceViewModel.GetAll(Db, Time, searchFilter).ToList();
return(File(DhlInvoiceViewModel.BuildReport(invoices).ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DhlInvoiceReport.xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
public static IQueryable <DhlInvoiceViewModel> GetAll(IUnitOfWork db,
ITime time,
DhlInvoiceFilterViewModel filter)
{
var weightQuery = from i in db.OrderItems.GetAllAsDto()
join l in db.Listings.GetViewListings() on i.ListingId equals l.Id
group new { i, l } by i.OrderId
into byOrder
select new
{
OrderId = byOrder.Key,
Weight = byOrder.Sum(i => i.i.QuantityOrdered * (i.l.Weight ?? 0))
};
var query = from n in db.DhlInvoices.GetAllAsDto()
join o in db.Orders.GetAll() on n.OrderNumber equals o.AmazonIdentifier
join rc in db.DhlRateCodes.GetAllAsDto() on o.ShippingCountry equals rc.CountryCode into withRC
from rc in withRC.DefaultIfEmpty()
join oi in weightQuery on o.Id equals oi.OrderId
select new DhlInvoiceViewModel
{
Id = n.Id,
OrderNumber = n.OrderNumber,
OrderDate = o.OrderDate,
Market = o.Market,
MarketplaceId = o.MarketplaceId,
Status = n.Status,
WeightUsed = oi.Weight,
Country = o.ShippingCountry,
RateCode = rc.RateCode,
Dimensions = n.Dimensions,
BillNumber = n.BillNumber,
Charged = n.ChargedSummary,
Estimated = n.Estimated,
InvoiceDate = n.InvoiceDate,
InvoiceNumber = n.InvoiceNumber,
};
if (!String.IsNullOrEmpty(filter.OrderNumber))
{
query = query.Where(n => n.OrderNumber == filter.OrderNumber);
}
if (filter.DateFrom.HasValue)
{
query = query.Where(n => n.InvoiceDate >= filter.DateFrom.Value);
}
if (filter.DateTo.HasValue)
{
query = query.Where(n => n.InvoiceDate <= filter.DateTo.Value);
}
if (filter.Status.HasValue)
{
query = query.Where(n => n.Status == filter.Status.Value);
}
var invoiceList = query.ToList();
var rateList = db.DhlRateCodePrices.GetAllAsDto().ToList();
foreach (var invoice in invoiceList)
{
var weight = invoice.WeightUsed.HasValue ? Math.Ceiling(invoice.WeightUsed.Value / (double)16) : 0;
var rateCodes = rateList.Where(r => r.RateCode == invoice.RateCode && r.ProductCode == "P").OrderBy(r => r.Weight).ToList();
var rate = rateCodes.FirstOrDefault(r => r.Weight == weight);
if (rate != null)
{
invoice.RatePrice = rate.Price;
}
}
return(invoiceList.AsQueryable());
}
