public ViewResult Bills(BillFilterCriteria filterCriteria, string SentDateFrom, string SentDateTo)
{
filterCriteria.SentDateFrom = SetDateTime(SentDateFrom);
filterCriteria.SentDateTo = SetDateTime(SentDateTo);
Session[SiteConfig.BillSearchCriteriaSession] = filterCriteria;
var customers = new List <Customer>();
var bills = SearchBill(filterCriteria, out customers);
var paging = PagingHelper.Paging(bills.Count, 30, 1);
ViewBag.Paging = paging;
ViewBag.Customers = new SelectList(customers, "Id", "CustomerName", filterCriteria.CustomerId);
return(View(bills.GetRange(paging.StartRecord - 1, paging.EndRecord + 1 - paging.StartRecord)));
}
private BinaryResult ExportBillList(IEnumerable <BillOfLading> bills, BillFilterCriteria filterCriteria)
{
var result = new MemoryStream();
var template = new ExcelFile();
template.LoadXlsx(Server.MapPath("~/Content/Documents/BillList.xlsx"), XlsxOptions.None);
var worksheet = template.Worksheets[0];
worksheet.Cells["B3"].Value = filterCriteria.CustomerId.HasValue ? filterCriteria.CustomerName : string.Empty;
worksheet.Cells["B4"].Value = filterCriteria.SentDateFrom.HasValue ?
filterCriteria.SentDateFrom.Value.ToString("dd/MM/yyyy") : string.Empty;
worksheet.Cells["B5"].Value = filterCriteria.SentDateTo.HasValue ?
filterCriteria.SentDateTo.Value.ToString("dd/MM/yyyy") : string.Empty;
var startRow = 7;
var currentRow = startRow;
var startCol = 0;
foreach (var bill in bills)
{
worksheet.Rows[currentRow].Cells[startCol].Value = bill.BillCode;
worksheet.Rows[currentRow].Cells[startCol + 1].Value = bill.Sender.ToUpperInvariant();
worksheet.Rows[currentRow].Cells[startCol + 2].Value = bill.SentDate.ToString("dd/MM/yyyy");
worksheet.Rows[currentRow].Cells[startCol + 3].Value = string.IsNullOrEmpty(bill.Receiver) ?
string.Empty : bill.Receiver.ToUpperInvariant();
worksheet.Rows[currentRow].Cells[startCol + 4].Value = bill.ReceivedDate.HasValue ?
bill.ReceivedDate.Value.ToString("dd/MM/yyyy HH:mm") :
string.Empty;
worksheet.Rows[currentRow].Cells[startCol + 5].Value = string.IsNullOrEmpty(bill.ActualReceiver) ?
string.Empty : bill.ActualReceiver.ToUpperInvariant();
worksheet.Rows[currentRow].Cells[startCol + 6].Value = string.IsNullOrEmpty(bill.DeliveryStaff) ?
string.Empty : bill.DeliveryStaff.ToUpperInvariant();
currentRow++;
}
CellBorder(worksheet, startRow, currentRow - 1, startCol, 6);
template.SaveXlsx(result);
return(new BinaryResult
{
IsAttachment = true,
Data = result.ToArray(),
ContentType = "application/vnd.ms-excel",
FileName = string.Format("BillList-{0}.xlsx", DateTime.Now.ToString("dd-MM-yyyy"))
});
}
private List <BillOfLading> SearchBill(BillFilterCriteria filterCriteria, out List <Customer> customers)
{
using (var context = new WebsiteDBEntities())
{
var bills = context.BillOfLadings
.Include("Customer1")
.Where(i => (filterCriteria.BillId == null || i.BillCode.Contains(filterCriteria.BillId)) &&
(filterCriteria.SentDateFrom == null || i.SentDate >= filterCriteria.SentDateFrom) &&
(filterCriteria.SentDateTo == null || i.SentDate <= filterCriteria.SentDateTo) &&
(filterCriteria.ReceivedDateFrom == null || i.ReceivedDate >= filterCriteria.ReceivedDateFrom) &&
(filterCriteria.ReceivedDateTo == null || i.ReceivedDate <= filterCriteria.ReceivedDateTo) &&
(filterCriteria.CustomerId == null || i.Customer.Value == filterCriteria.CustomerId.Value))
.OrderByDescending(i => i.SentDate).ToList();
filterCriteria.CustomerName = filterCriteria.CustomerId.HasValue ?
context.Customers
.SingleOrDefault(i => i.Id == filterCriteria.CustomerId.Value)
.CustomerName :
string.Empty;
customers = context.Customers.ToList();
return(bills);
}
}
public ActionResult ExportBill(BillFilterCriteria filterCriteria, string SentDateFrom, string SentDateTo,
string ReceivedDateFrom, string ReceivedDateTo, string btnExport, int page = 1)
{
filterCriteria.SentDateFrom = SetDateTime(SentDateFrom);
filterCriteria.SentDateTo = SetDateTime(SentDateTo);
filterCriteria.ReceivedDateFrom = SetDateTime(ReceivedDateFrom);
filterCriteria.ReceivedDateTo = SetDateTime(ReceivedDateTo);
Session[SiteConfig.BillFilterCriteriaSession] = filterCriteria;
var customers = new List <Customer>();
var bills = SearchBill(filterCriteria, out customers);
ViewBag.Customers = new SelectList(customers, "Id", "CustomerName", filterCriteria.CustomerId);
if (btnExport != null)
{
return(ExportBillList(bills, filterCriteria));
}
var paging = PagingHelper.Paging(bills.Count, 30, page);
ViewBag.Paging = paging;
return(View(bills.GetRange(paging.StartRecord - 1, paging.EndRecord + 1 - paging.StartRecord)));
}
