public ActionResult ExportExcel(string dateFrom, string dateTo, int sandId = 0, int customerId = 0, string filterText = "")
{
var dtExcel = new DataTable("DanhSachChuyen");
try
{
// get request parameters
var d1 = DateTime.ParseExact(dateFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture);
var d2 = DateTime.ParseExact(dateTo + " 23:59:59", "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
filterText = filterText.Trim().ToLower();
// get sand info
var sand = db.Sands.Where(s => s.SandId == sandId).FirstOrDefault();
if (sand == null)
{
return(Json(new { Error = 1, Message = "Sản phẩm cát #" + sandId + " không tồn tại trong hệ thống!" }));
}
// get sand type
Sand.SandTypes sandType = 0;
if (sand.SandName.ToLower().Contains("vàng") || sand.SandName.ToLower().Contains("vang") ||
sand.SandName.ToLower().Contains("trộn") || sand.SandName.ToLower().Contains("tron"))
{
sandType = Sand.SandTypes.GoldSand;
}
if (sand.SandName.ToLower().Contains("lấp") || sand.SandName.ToLower().Contains("lap"))
{
sandType = Sand.SandTypes.FillingSand;
}
// query orders
switch (sandType)
{
case Sand.SandTypes.FillingSand:
var query_1 = from t1 in db.OrderDetails
join t2 in db.Orders on t1.OrderId equals t2.OrderId
join t3 in db.Customers on t2.CustomerId equals t3.CustomerId
join t4 in db.Barges on t2.BargeId equals t4.BargeId
where
t2.OrderDate >= d1 &&
t2.OrderDate <= d2 &&
t2.SandId == sandId &&
(customerId == 0 || t2.CustomerId == customerId) &&
(string.IsNullOrEmpty(filterText) || t3.CustomerName.Contains(filterText) || t4.BargeCode.Contains(filterText))
orderby
t2.OrderDate descending,
t4.BargeCode ascending
group t1 by new
{
t1.OrderId,
t2.OrderDate,
t2.CustomerId,
t3.CustomerName,
t2.BargeId,
t4.BargeCode,
t2.VolumeRevenue,
t2.VolumePurchase,
t2.VolumePurchaseDecrease,
t2.CustomerPrice,
t2.ProviderPrice,
AmountRevenue = t2.VolumeRevenue * (double)t2.CustomerPrice,
AmountPurchase = (t2.VolumePurchase - t2.VolumePurchaseDecrease) * (double)t2.ProviderPrice
} into g
select new
{
g.Key.OrderId,
g.Key.OrderDate,
g.Key.CustomerId,
g.Key.CustomerName,
g.Key.BargeId,
g.Key.BargeCode,
g.Key.VolumeRevenue,
g.Key.VolumePurchase,
g.Key.VolumePurchaseDecrease,
VolumeTransport = g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume),
g.Key.CustomerPrice,
g.Key.ProviderPrice,
g.Key.AmountRevenue,
g.Key.AmountPurchase,
AmountTransport = g.Sum(s => (s.BoatVolume * s.TransportTimes + s.ExtraVolume) * (double)s.TransportPrice)
};
dtExcel.Columns.Add("Ngày", typeof(string));
dtExcel.Columns.Add("Đơn vị", typeof(string));
dtExcel.Columns.Add("Sà lan", typeof(string));
dtExcel.Columns.Add("Khối doanh số", typeof(string));
dtExcel.Columns.Add("Khối mỏ", typeof(string));
dtExcel.Columns.Add("Khối giảm", typeof(string));
dtExcel.Columns.Add("Khối ghe", typeof(string));
dtExcel.Columns.Add("Giá bán", typeof(string));
dtExcel.Columns.Add("Giá mua", typeof(string));
dtExcel.Columns.Add("Doanh số", typeof(string));
dtExcel.Columns.Add("Trả tiền mỏ", typeof(string));
dtExcel.Columns.Add("Trả tiền ghe", typeof(string));
dtExcel.Columns.Add("Lợi nhuận", typeof(string));
double sumVolumeRevenue_1 = 0;
double sumVolumePurchase_1 = 0;
double sumVolumePurchaseDescrease_1 = 0;
double sumVolumeTransport_1 = 0;
double sumAmountRevenue_1 = 0;
double sumAmountPurchase_1 = 0;
double sumAmountTransport_1 = 0;
double sumAmountProfit_1 = 0;
foreach (var item in query_1)
{
var row_1 = dtExcel.NewRow();
row_1["Ngày"] = item.OrderDate;
row_1["Đơn vị"] = item.CustomerName;
row_1["Sà lan"] = item.BargeCode;
row_1["Khối doanh số"] = item.VolumeRevenue;
row_1["Khối mỏ"] = item.VolumePurchase;
row_1["Khối giảm"] = item.VolumePurchaseDecrease;
row_1["Khối ghe"] = item.VolumeTransport;
row_1["Giá bán"] = item.CustomerPrice;
row_1["Giá mua"] = item.ProviderPrice;
row_1["Doanh số"] = item.AmountRevenue;
row_1["Trả tiền mỏ"] = item.AmountPurchase;
row_1["Trả tiền ghe"] = item.AmountTransport;
row_1["Lợi nhuận"] = item.AmountRevenue - item.AmountPurchase - item.AmountTransport;
sumVolumeRevenue_1 += item.VolumeRevenue;
sumVolumePurchase_1 += item.VolumePurchase;
sumVolumePurchaseDescrease_1 += item.VolumePurchaseDecrease;
sumVolumeTransport_1 += item.VolumeTransport;
sumAmountRevenue_1 += item.AmountRevenue;
sumAmountPurchase_1 += item.AmountPurchase;
sumAmountTransport_1 += item.AmountTransport;
sumAmountProfit_1 += item.AmountRevenue - item.AmountPurchase - item.AmountTransport;
dtExcel.Rows.Add(row_1);
}
var rowSummary_1 = dtExcel.NewRow();
rowSummary_1["Ngày"] = "Tổng cộng";
rowSummary_1["Đơn vị"] = string.Empty;
rowSummary_1["Sà lan"] = string.Empty;
rowSummary_1["Khối doanh số"] = sumVolumeRevenue_1;
rowSummary_1["Khối mỏ"] = sumVolumePurchase_1;
rowSummary_1["Khối giảm"] = sumVolumePurchaseDescrease_1;
rowSummary_1["Khối ghe"] = sumVolumeTransport_1;
rowSummary_1["Giá bán"] = string.Empty;
rowSummary_1["Giá mua"] = string.Empty;
rowSummary_1["Doanh số"] = sumAmountRevenue_1;
rowSummary_1["Trả tiền mỏ"] = sumAmountPurchase_1;
rowSummary_1["Trả tiền ghe"] = sumAmountTransport_1;
rowSummary_1["Lợi nhuận"] = sumAmountProfit_1;
dtExcel.Rows.Add(rowSummary_1);
break;
case Sand.SandTypes.GoldSand:
var query_2 = from t1 in db.OrderDetails
join t2 in db.Orders on t1.OrderId equals t2.OrderId
join t3 in db.Customers on t2.CustomerId equals t3.CustomerId
join t4 in db.Barges on t2.BargeId equals t4.BargeId
where
t2.OrderDate >= d1 &&
t2.OrderDate <= d2 &&
t2.SandId == sandId &&
(customerId == 0 || t2.CustomerId == customerId) &&
(string.IsNullOrEmpty(filterText) || t3.CustomerName.Contains(filterText) || t4.BargeCode.Contains(filterText))
orderby
t2.OrderDate descending,
t4.BargeCode ascending
group t1 by new
{
t1.OrderId,
t2.OrderDate,
t2.CustomerId,
t3.CustomerName,
t2.BargeId,
t4.BargeCode,
t2.VolumePurchase,
t2.VolumePurchaseDecrease,
t2.VolumePromotion,
t2.CustomerPrice,
t2.ProviderPrice,
t2.BaseTransportPrice,
AmountPurchase = (t2.VolumePurchase - t2.VolumePurchaseDecrease) * (double)t2.ProviderPrice
} into g
select new
{
g.Key.OrderId,
g.Key.OrderDate,
g.Key.CustomerId,
g.Key.CustomerName,
g.Key.BargeId,
g.Key.BargeCode,
g.Key.VolumePurchase,
g.Key.VolumePurchaseDecrease,
VolumeTransport = g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume),
g.Key.VolumePromotion,
g.Key.CustomerPrice,
g.Key.ProviderPrice,
g.Key.BaseTransportPrice,
AmountRevenue = (g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume) - g.Key.VolumePromotion) * (double)g.Key.CustomerPrice + g.Key.VolumePromotion * (double)g.Key.BaseTransportPrice,
g.Key.AmountPurchase,
AmountTransport = g.Sum(s => (s.BoatVolume * s.TransportTimes + s.ExtraVolume) * (double)s.TransportPrice)
};
dtExcel.Columns.Add("Ngày", typeof(string));
dtExcel.Columns.Add("Đơn vị", typeof(string));
dtExcel.Columns.Add("Sà lan", typeof(string));
dtExcel.Columns.Add("Khối mỏ", typeof(string));
dtExcel.Columns.Add("Khối giảm", typeof(string));
dtExcel.Columns.Add("Khối ghe", typeof(string));
dtExcel.Columns.Add("Khối gia công", typeof(string));
dtExcel.Columns.Add("Giá bán", typeof(string));
dtExcel.Columns.Add("Giá mua", typeof(string));
dtExcel.Columns.Add("Giá gia công", typeof(string));
dtExcel.Columns.Add("Doanh số", typeof(string));
dtExcel.Columns.Add("Trả tiền mỏ", typeof(string));
dtExcel.Columns.Add("Trả tiền ghe", typeof(string));
dtExcel.Columns.Add("Lợi nhuận", typeof(string));
double sumVolumePromotion_2 = 0;
double sumVolumePurchase_2 = 0;
double sumVolumePurchaseDescrease_2 = 0;
double sumVolumeTransport_2 = 0;
double sumAmountRevenue_2 = 0;
double sumAmountPurchase_2 = 0;
double sumAmountTransport_2 = 0;
double sumAmountProfit_2 = 0;
foreach (var item in query_2)
{
var row_2 = dtExcel.NewRow();
row_2["Ngày"] = item.OrderDate;
row_2["Đơn vị"] = item.CustomerName;
row_2["Sà lan"] = item.BargeCode;
row_2["Khối mỏ"] = item.VolumePurchase;
row_2["Khối giảm"] = item.VolumePurchaseDecrease;
row_2["Khối ghe"] = item.VolumeTransport;
row_2["Khối gia công"] = item.VolumePromotion;
row_2["Giá bán"] = item.CustomerPrice;
row_2["Giá mua"] = item.ProviderPrice;
row_2["Giá gia công"] = item.BaseTransportPrice;
row_2["Doanh số"] = item.AmountRevenue;
row_2["Trả tiền mỏ"] = item.AmountPurchase;
row_2["Trả tiền ghe"] = item.AmountTransport;
row_2["Lợi nhuận"] = item.AmountRevenue - item.AmountPurchase - item.AmountTransport;
sumVolumePurchase_2 += item.VolumePurchase;
sumVolumePurchaseDescrease_2 += item.VolumePurchaseDecrease;
sumVolumeTransport_2 += item.VolumeTransport;
sumVolumePromotion_2 += item.VolumePromotion;
sumAmountRevenue_2 += item.AmountRevenue;
sumAmountPurchase_2 += item.AmountPurchase;
sumAmountTransport_2 += item.AmountTransport;
sumAmountProfit_2 += item.AmountRevenue - item.AmountPurchase - item.AmountTransport;
dtExcel.Rows.Add(row_2);
}
var rowSummary_2 = dtExcel.NewRow();
rowSummary_2["Ngày"] = "Tổng cộng";
rowSummary_2["Đơn vị"] = string.Empty;
rowSummary_2["Sà lan"] = string.Empty;
rowSummary_2["Khối mỏ"] = sumVolumePurchase_2;
rowSummary_2["Khối giảm"] = sumVolumePurchaseDescrease_2;
rowSummary_2["Khối ghe"] = sumVolumeTransport_2;
rowSummary_2["Khối gia công"] = sumVolumePromotion_2;
rowSummary_2["Giá bán"] = string.Empty;
rowSummary_2["Giá mua"] = string.Empty;
rowSummary_2["Giá gia công"] = string.Empty;
rowSummary_2["Doanh số"] = sumAmountRevenue_2;
rowSummary_2["Trả tiền mỏ"] = sumAmountPurchase_2;
rowSummary_2["Trả tiền ghe"] = sumAmountTransport_2;
rowSummary_2["Lợi nhuận"] = sumAmountProfit_2;
dtExcel.Rows.Add(rowSummary_2);
break;
}
var grid = new GridView();
grid.DataSource = dtExcel;
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=BaoCao_DanhSach_Chuyen_" + DateTime.Now.ToString("yyyyMMdd") + ".xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
// Write action log
var actionLogData =
" d1=" + dateFrom +
", d2=" + dateTo +
", sand_id=" + sandId +
", customer_id=" + customerId +
", filter_text=" + filterText;
ActionLog.WriteLog(ActionLog.EXPORT_EXCEL_ORDER_REPORT, actionLogData, User.Identity.Name, Request.ServerVariables["REMOTE_ADDR"]);
return(View());
}
catch (Exception ex)
{
ex.ToString();
}
return(View());
}
public JsonResult GetOrders(string dateFrom, string dateTo, int sandId = 0, int customerId = 0, string filterText = "")
{
try
{
// parse date range
var d1 = DateTime.ParseExact(dateFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture);
var d2 = DateTime.ParseExact(dateTo + " 23:59:59", "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
filterText = filterText.Trim().ToLower();
// get sand info
var sand = db.Sands.Where(s => s.SandId == sandId).FirstOrDefault();
if (sand == null)
{
return(Json(new { Error = 1, Message = "Sản phẩm cát #" + sandId + " không tồn tại trong hệ thống!" }));
}
// get sand type
Sand.SandTypes sandType = 0;
if (sand.SandName.ToLower().Contains("vàng") || sand.SandName.ToLower().Contains("vang") ||
sand.SandName.ToLower().Contains("trộn") || sand.SandName.ToLower().Contains("tron"))
{
sandType = Sand.SandTypes.GoldSand;
}
if (sand.SandName.ToLower().Contains("lấp") || sand.SandName.ToLower().Contains("lap"))
{
sandType = Sand.SandTypes.FillingSand;
}
// query orders
switch (sandType)
{
case Sand.SandTypes.FillingSand:
var query_1 = from t1 in db.OrderDetails
join t2 in db.Orders on t1.OrderId equals t2.OrderId
join t3 in db.Customers on t2.CustomerId equals t3.CustomerId
join t4 in db.Barges on t2.BargeId equals t4.BargeId
where
t2.OrderDate >= d1 &&
t2.OrderDate <= d2 &&
t2.SandId == sandId &&
(customerId == 0 || t2.CustomerId == customerId) &&
(string.IsNullOrEmpty(filterText) || t3.CustomerName.Contains(filterText) || t4.BargeCode.Contains(filterText))
orderby
t2.OrderDate descending,
t4.BargeCode ascending
group t1 by new
{
t1.OrderId,
t2.OrderDate,
t2.CustomerId,
t3.CustomerName,
t2.BargeId,
t4.BargeCode,
t2.VolumeRevenue,
t2.VolumePurchase,
t2.VolumePurchaseDecrease,
t2.CustomerPrice,
t2.ProviderPrice,
AmountRevenue = t2.VolumeRevenue * (double)t2.CustomerPrice,
AmountPurchase = (t2.VolumePurchase - t2.VolumePurchaseDecrease) * (double)t2.ProviderPrice
} into g
select new
{
g.Key.OrderId,
g.Key.OrderDate,
g.Key.CustomerId,
g.Key.CustomerName,
g.Key.BargeId,
g.Key.BargeCode,
g.Key.VolumeRevenue,
g.Key.VolumePurchase,
g.Key.VolumePurchaseDecrease,
VolumeTransport = g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume),
g.Key.CustomerPrice,
g.Key.ProviderPrice,
g.Key.AmountRevenue,
g.Key.AmountPurchase,
AmountTransport = g.Sum(s => (s.BoatVolume * s.TransportTimes + s.ExtraVolume) * (double)s.TransportPrice)
};
return(Json(new { Error = 0, Message = "Success", Orders = query_1 }));
case Sand.SandTypes.GoldSand:
var query_2 = from t1 in db.OrderDetails
join t2 in db.Orders on t1.OrderId equals t2.OrderId
join t3 in db.Customers on t2.CustomerId equals t3.CustomerId
join t4 in db.Barges on t2.BargeId equals t4.BargeId
where
t2.OrderDate >= d1 &&
t2.OrderDate <= d2 &&
t2.SandId == sandId &&
(customerId == 0 || t2.CustomerId == customerId) &&
(string.IsNullOrEmpty(filterText) || t3.CustomerName.Contains(filterText) || t4.BargeCode.Contains(filterText))
orderby
t2.OrderDate descending,
t4.BargeCode ascending
group t1 by new
{
t1.OrderId,
t2.OrderDate,
t2.CustomerId,
t3.CustomerName,
t2.BargeId,
t4.BargeCode,
t2.VolumePurchase,
t2.VolumePurchaseDecrease,
t2.VolumePromotion,
t2.CustomerPrice,
t2.ProviderPrice,
t2.BaseTransportPrice,
AmountPurchase = (t2.VolumePurchase - t2.VolumePurchaseDecrease) * (double)t2.ProviderPrice
} into g
select new
{
g.Key.OrderId,
g.Key.OrderDate,
g.Key.CustomerId,
g.Key.CustomerName,
g.Key.BargeId,
g.Key.BargeCode,
g.Key.VolumePurchase,
g.Key.VolumePurchaseDecrease,
VolumeTransport = g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume),
g.Key.VolumePromotion,
g.Key.CustomerPrice,
g.Key.ProviderPrice,
g.Key.BaseTransportPrice,
AmountRevenue = (g.Sum(s => s.BoatVolume * s.TransportTimes + s.ExtraVolume) - g.Key.VolumePromotion) * (double)g.Key.CustomerPrice + g.Key.VolumePromotion * (double)g.Key.BaseTransportPrice,
g.Key.AmountPurchase,
AmountTransport = g.Sum(s => (s.BoatVolume * s.TransportTimes + s.ExtraVolume) * (double)s.TransportPrice)
};
return(Json(new { Error = 0, Message = "Success", Orders = query_2 }));
}
return(Json(new { Error = 1, Message = "Loại cát không phù hợp!" }));
}
catch (Exception ex)
{
return(Json(new { Error = 1, Message = ex.Message }));
}
}
