private MemoryStream stmShip(Api.PurchaseController.ShipParam q) { MemoryStream outputStream = new MemoryStream(); try { db0 = getDB0(); XLWorkbook excel = new XLWorkbook(); IXLWorksheet getSheet = excel.Worksheets.Add("訂單未出貨統計"); #region 取得資料 var items = getShipData(q); #endregion #region Excel Handle makeShip(items, getSheet); #endregion excel.SaveAs(outputStream); outputStream.Position = 0; excel.Dispose(); return(outputStream); } catch (Exception ex) { //logger.Error(ex); return(null); } }
public FileResult Excel_Ship(Api.PurchaseController.ShipParam q) { var outputStream = stmShip(q); string setFileName = "訂單未出貨統計-" + Guid.NewGuid().ToString() + ".xlsx"; return(File(outputStream, "application/vnd.ms-excel", setFileName)); }
private List <Api.PurchaseController.ShipData> getShipData(Api.PurchaseController.ShipParam q) { List <Api.PurchaseController.ShipData> res = new List <Api.PurchaseController.ShipData>(); using (var db0 = getDB0()) { #region getdata var predicate = PredicateBuilder.True <PurchaseDetail>(); predicate = predicate.And(x => x.Purchase.ship_state == (int)IShipState.unshipped & ((x.Purchase.pay_type == (int)IPayType.Remit & x.Purchase.pay_state == (int)IPayState.paid) || (x.Purchase.pay_type == (int)IPayType.CashOnDelivery))); if (q.keyword != null) { predicate = predicate.And(x => x.purchase_no.Contains(q.keyword) || x.Purchase.receive_name.Contains(q.keyword) || x.p_name.Contains(q.keyword)); } if (q.order_start != null & q.order_end != null) { DateTime start = (DateTime)q.order_start; DateTime end = ((DateTime)q.order_end).AddDays(1); predicate = predicate.And(x => x.Purchase.order_date >= start & x.Purchase.order_date <= end); } res = db0.PurchaseDetail.AsExpandable().Where(predicate) .GroupBy(x => x.p_name) .Select(x => new Api.PurchaseController.ShipData() { p_name = x.Key, Detail = x.Select(y => new Api.PurchaseController.ShipPD() { purchase_no = y.purchase_no, purchase_detail_id = y.purchase_detail_id, //訂單明細-編號 product_detail_id = y.product_detail_id, //產品明細-編號 p_d_pack_name = y.p_d_pack_name, //產品包裝 order_date = y.Purchase.order_date, //下單日期 receive_name = y.Purchase.receive_name, //收件人 customer_name = y.Purchase.Customer.c_name, //購買人 weight = y.ProductDetail.weight, //重量 qty = y.qty //數量 }).ToList() }) .ToList(); #endregion } return(res); }