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);
}
