private MemoryStream stmPurchase(Api.PurchaseController.queryParam q)
{
MemoryStream outputStream = new MemoryStream();
try
{
db0 = getDB0();
XLWorkbook excel = new XLWorkbook();
IXLWorksheet getSheet = excel.Worksheets.Add("訂單資料維護");
#region 取得資料
var items = getPurchaseData(q);
#endregion
#region Excel Handle
makePurchase(items, getSheet);
//getSheet.Cell(1, 1).Value = "Hello world";
#endregion
excel.SaveAs(outputStream);
outputStream.Position = 0;
excel.Dispose();
return(outputStream);
}
catch (Exception ex)
{
//logger.Error(ex);
return(null);
}
}
public FileResult Excel_Purchase(Api.PurchaseController.queryParam q)
{
var outputStream = stmPurchase(q);
string setFileName = "訂單資料維護-" + Guid.NewGuid().ToString() + ".xlsx";
return(File(outputStream, "application/vnd.ms-excel", setFileName));
}
private List <m_Purchase> getPurchaseData(Api.PurchaseController.queryParam q)
{
List <m_Purchase> res = new List <m_Purchase>();
using (var db0 = getDB0())
{
#region getdata
var predicate = PredicateBuilder.True <Purchase>();
if (q.keyword != null)
{
predicate = predicate.And(x => x.purchase_no.Contains(q.keyword) ||
x.receive_name.Contains(q.keyword));
}
if (q.order_date != null)
{
DateTime start = (DateTime)q.order_date;
DateTime end = ((DateTime)q.order_date).AddDays(1);
predicate = predicate.And(x => x.order_date >= start & x.order_date < end);
}
if (q.pay_date != null)
{
DateTime start = (DateTime)q.pay_date;
DateTime end = ((DateTime)q.pay_date).AddDays(1);
predicate = predicate.And(x => x.remit_date >= start & x.remit_date < end);
}
if (q.type != null)
{
if (q.type == 1)
{//pay_state
predicate = predicate.And(x => x.pay_state == q.type_val);
}
else if (q.type == 2)
{//ship_state
predicate = predicate.And(x => x.ship_state == q.type_val);
}
}
res = db0.Purchase.AsExpandable().Where(predicate)
.Select(x => new m_Purchase
{
purchase_no = x.purchase_no,
customer_id = x.customer_id,
customer_name = x.Customer.c_name,
pay_type = x.pay_type,
pay_state = x.pay_state,
ship_state = x.ship_state,
order_date = x.order_date,
ship_fee = x.ship_fee,
bank_charges = x.bank_charges,
total = x.total,
receive_name = x.receive_name,
receive_tel = x.receive_tel,
receive_mobile = x.receive_mobile,
receive_zip = x.receive_zip,
receive_address = x.receive_address,
receive_memo = x.receive_memo,
Deatil = x.PurchaseDetail.ToList()
}).ToList();
#endregion
}
return(res);
}
