public FileResult downloadExcel_CustomerAgent(ParmReportR04 parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
//char test = Convert.ToChar(64 + 9);
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("CustomerAgentData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["CustomerAgentData"];
sheet.View.TabSelected = true;
#region 取得客戶進貨數量
string date_range = "(All)";
var items = from x in db0.StockDetail
join y in db0.StockDetailQty
on x.stock_detail_id equals y.stock_detail_id
orderby x.Stock.y, x.Stock.m, x.product_id, y.customer_id
select (new CustomerAgent()
{
stock_detail_id = x.stock_detail_id,
stock_detail_qty_id = y.stock_detail_qty_id,
agent_id = x.Stock.agent_id,
agent_name = x.Stock.Agent.agent_name,
product_id = x.product_id,
product_name = x.Product.product_name,
customer_id = y.customer_id,
customer_name = y.Customer.customer_name,
qty = y.qty,
y = x.Stock.y,
m = x.Stock.m,
customer_type = y.Customer.customer_type,
channel_type = y.Customer.channel_type,
evaluate = y.Customer.evaluate,
store_type = y.Customer.store_type,
store_level = y.Customer.store_level,
area_id = y.Customer.area_id,
area_name = y.Customer.Area.area_name
});
//列印月份用
List<int> months = new List<int>();
if (parm.start_date != null && parm.end_date != null)
{
DateTime start = (DateTime)parm.start_date;
DateTime end = (DateTime)parm.end_date;
if (start.Year == end.Year)
{//同年
items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month);
months = startToEndMonth(start.Month, end.Month);
}
else {//不同年
List<int> start_m = startMonth(start.Month);
List<int> end_m = endMonth(end.Month);
months.AddRange(start_m);
months.AddRange(end_m);
items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m)));
}
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
if (parm.product_name != null)
{
items = items.Where(x => x.product_name.Contains(parm.product_name));
}
if (parm.customer_name != null)
{
items = items.Where(x => x.customer_name.Contains(parm.customer_name));
}
if (parm.customer_type != null)
{
items = items.Where(x => x.customer_type == parm.customer_type);
}
if (parm.channel_type != null)
{
items = items.Where(x => x.channel_type == parm.channel_type);
}
if (parm.evaluate != null)
{
items = items.Where(x => x.evaluate == parm.evaluate);
}
if (parm.store_type != null)
{
items = items.Where(x => x.store_type == parm.store_type);
}
if (parm.store_level != null)
{
items = items.Where(x => x.store_level == parm.store_level);
}
if (parm.area != null)
{
items = items.Where(x => x.area_id == parm.area);
}
//if (parm.months_p != null)
//{
// items = items.Where(x => parm.months_p.Contains(x.m));
//}
if (parm.ids != null)
{
items = items.Where(x => parm.ids.Contains(x.product_id));
}
var getTempVal = items.ToList();
#endregion
#region 整理報表列印格式
//取得每月進貨加總
var getSumMonth = from x in getTempVal
group x by new
{
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
x.m
} into g
select (new CustomerAgent()
{
product_id = g.Key.product_id,
product_name = g.Key.product_name,
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
m = g.Key.m,
qty = g.Sum(z => z.qty)
});
//取得不重複客戶資料
var getPrintVal = (from x in getTempVal
group x by new
{
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
x.customer_type,
x.channel_type,
x.evaluate,
x.store_type,
x.store_level,
x.area_name
} into g
orderby g.Key.product_id, g.Key.customer_id
select (new ExcleCustomerAgent()
{
product_id = g.Key.product_id,
product_name = g.Key.product_name,
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
customer_type = g.Key.customer_type,
channel_type = g.Key.channel_type,
evaluate = g.Key.evaluate,
store_type = g.Key.store_type,
store_level = g.Key.store_level,
area_name = g.Key.area_name
})).ToList();
foreach (var itemA in getPrintVal)
{
foreach (var itemB in getSumMonth)
{
if (itemA.customer_id == itemB.customer_id && itemA.product_id == itemB.product_id)
{
itemA.sum_qtys += itemB.qty;
switch (itemB.m)
{
case 1:
itemA.qty_1 = itemB.qty;
break;
case 2:
itemA.qty_2 = itemB.qty;
break;
case 3:
itemA.qty_3 = itemB.qty;
break;
case 4:
itemA.qty_4 = itemB.qty;
break;
case 5:
itemA.qty_5 = itemB.qty;
break;
case 6:
itemA.qty_6 = itemB.qty;
break;
case 7:
itemA.qty_7 = itemB.qty;
break;
case 8:
itemA.qty_8 = itemB.qty;
break;
case 9:
itemA.qty_9 = itemB.qty;
break;
case 10:
itemA.qty_10 = itemB.qty;
break;
case 11:
itemA.qty_11 = itemB.qty;
break;
case 12:
itemA.qty_12 = itemB.qty;
break;
default:
break;
}
}
}
}
#endregion
#region Excel Handle
int detail_row = 4;
#region 標題
sheet.Cells[1, 1].Value = "R05客戶進貨統計表(客戶-多經銷商)" + date_range;
//sheet.Cells[1, 1, 1, 8].Merge = true;
sheet.Cells[2, 1].Value = "[產品名稱]";
sheet.Cells[2, 2].Value = "[客戶名稱]";
sheet.Cells[2, 3].Value = "[區域\n群組]";
sheet.Cells[2, 4].Value = "[客戶\n類別]";
sheet.Cells[2, 5].Value = "[通路\n級別]";
sheet.Cells[2, 6].Value = "[客戶\n銷售等級]";
sheet.Cells[2, 7].Value = "[客戶\n型態]";
sheet.Cells[2, 8].Value = "[型態\n等級]";
//setMerge_label(sheet, 2, 3, 1, 8);//上下合併儲存格
setWrapText(sheet, 2, 3, 8);// \n換行設定
const int month_start = 9;
int month_end = month_start + months.Count() - 1;
int temp_index = month_start;
foreach (var i in months)
{
sheet.Cells[3, temp_index].Value = "[" + i + "月份]";
temp_index++;
}
sheet.Cells[3, temp_index].Value = "[加總]";
sheet.Cells[2, month_start].Value = date_range + "產品進貨數量(" + months[0] + "~" + months[months.Count() - 1] + "月)";
//sheet.Cells[2, month_start, 2, month_end].Merge = true;
setFontColor_LabelBord(sheet, 2, 1, month_end);//儲存格框線+藍字
setFontColor_LabelBord(sheet, 3, 1, month_end);
setFontColor_blue(sheet, 1, 1);
setFontColor_red(sheet, 3, month_end + 1);//紅字
sheet.Cells[3, month_end + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
#endregion
#region 內容
decimal[] row_sum = new decimal[12];//底部加總計算
decimal[] row_subtotal = new decimal[12];//每個產品小計計算
string subtotal_product_name = string.Empty;
foreach (var item in getPrintVal)
{
if (item.sum_qtys != 0)//如果1~12月都沒有進貨,就不顯示
{
#region 小計判斷
if (detail_row == 4)
{
subtotal_product_name = item.product_name;//第一筆資料
}
else if (subtotal_product_name != item.product_name)//產品變換時,做一次小計
{
#region 小計
#region 小計欄位,合併及文字顏色
sheet.Cells[detail_row, 8].Value = "[小計]";
setFontColor_red(sheet, detail_row, 8);
sheet.Cells[detail_row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 8].Merge = true;
#endregion
temp_index = month_start;
foreach (var i in months)
{
sheet.Cells[detail_row, temp_index].Value = row_subtotal[i - 1];
sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
temp_index++;
}
#endregion
#region 不同產品區分版面
setBroder_red(sheet, detail_row + 1, 1, month_end);
detail_row += 1;
#endregion
detail_row++;
row_subtotal = new decimal[12];//小計歸零
subtotal_product_name = item.product_name;//紀錄新產品
}
#endregion
sheet.Cells[detail_row, 1].Value = item.product_name;
sheet.Cells[detail_row, 2].Value = item.customer_name;
sheet.Cells[detail_row, 3].Value = item.area_name;
sheet.Cells[detail_row, 4].Value = CodeSheet.GetCustomerTypeVal(item.customer_type);
sheet.Cells[detail_row, 5].Value = CodeSheet.GetChannelTypeVal(item.channel_type);
sheet.Cells[detail_row, 6].Value = CodeSheet.GetEvaluateVal(item.evaluate);
sheet.Cells[detail_row, 7].Value = CodeSheet.GetStoreTypeVal(item.store_type);
sheet.Cells[detail_row, 8].Value = CodeSheet.GetStoreLevelVal(item.store_level);
temp_index = month_start;
foreach (var i in months)
{
#region getQtyVal
decimal temp_qyt = 0;
switch (i)
{
case 1:
temp_qyt = item.qty_1;
break;
case 2:
temp_qyt = item.qty_2;
break;
case 3:
temp_qyt = item.qty_3;
break;
case 4:
temp_qyt = item.qty_4;
break;
case 5:
temp_qyt = item.qty_5;
break;
case 6:
temp_qyt = item.qty_6;
break;
case 7:
temp_qyt = item.qty_7;
break;
case 8:
temp_qyt = item.qty_8;
break;
case 9:
temp_qyt = item.qty_9;
break;
case 10:
temp_qyt = item.qty_10;
break;
case 11:
temp_qyt = item.qty_11;
break;
case 12:
temp_qyt = item.qty_12;
break;
default:
break;
}
#endregion
sheet.Cells[detail_row, temp_index].Value = temp_qyt;
temp_index++;
}
sheet.Cells[detail_row, month_end + 1].Formula = string.Format("=SUM(I{0}:{1}{0})", detail_row, Convert.ToChar(64 + month_end));
#region 小計加總計算
row_subtotal[0] += item.qty_1;
row_subtotal[1] += item.qty_2;
row_subtotal[2] += item.qty_3;
row_subtotal[3] += item.qty_4;
row_subtotal[4] += item.qty_5;
row_subtotal[5] += item.qty_6;
row_subtotal[6] += item.qty_7;
row_subtotal[7] += item.qty_8;
row_subtotal[8] += item.qty_9;
row_subtotal[9] += item.qty_10;
row_subtotal[10] += item.qty_11;
row_subtotal[11] += item.qty_12;
#endregion
#region 底部加總計算
row_sum[0] += item.qty_1;
row_sum[1] += item.qty_2;
row_sum[2] += item.qty_3;
row_sum[3] += item.qty_4;
row_sum[4] += item.qty_5;
row_sum[5] += item.qty_6;
row_sum[6] += item.qty_7;
row_sum[7] += item.qty_8;
row_sum[8] += item.qty_9;
row_sum[9] += item.qty_10;
row_sum[10] += item.qty_11;
row_sum[11] += item.qty_12;
#endregion
detail_row++;
}
}
#region 最後一次小計
#region 小計欄位,合併及文字顏色
sheet.Cells[detail_row, 8].Value = "[小計]";
setFontColor_red(sheet, detail_row, 8);
sheet.Cells[detail_row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 8].Merge = true;
#endregion
temp_index = month_start;
foreach (var i in months)
{
sheet.Cells[detail_row, temp_index].Value = row_subtotal[i - 1];
sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
temp_index++;
}
#region 不同產品區分版面
setBroder_red(sheet, detail_row + 1, 1, month_end);
detail_row += 1;
#endregion
detail_row++;
#endregion
#region 底部加總
#region 加總欄位,合併及文字顏色
sheet.Cells[detail_row, 1].Value = "[加總]";
setFontColor_red(sheet, detail_row, 1);
sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 8].Merge = true;
#endregion
temp_index = month_start;
foreach (var i in months)
{
sheet.Cells[detail_row, temp_index].Value = row_sum[i - 1];
sheet.Cells[detail_row, temp_index].Style.Border.Top.Style = ExcelBorderStyle.Double;
sheet.Cells[detail_row, temp_index].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
temp_index++;
}
#endregion
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
sheet.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = "R05客戶進貨統計表(客戶-多經銷商)" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
catch (Exception ex)
{
Console.Write(ex.Message);
return null;
}
finally
{
db0.Dispose();
}
}
public FileResult downloadExcel_CustomerProduct(ParmReportR04 parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("CustomerProductData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["CustomerProductData"];
sheet.View.TabSelected = true;
#region 取得客戶進貨數量
string date_range = "(All)";
var items = from x in db0.StockDetail
join y in db0.StockDetailQty
on x.stock_detail_id equals y.stock_detail_id
orderby y.Customer.customer_name
where parm.ids.Contains(x.product_id)
select (new CustomerProduct()
{
agent_id = x.Stock.agent_id,
agent_name = x.Stock.Agent.agent_name,
product_id = x.product_id,
product_name = x.Product.product_name,
customer_id = y.customer_id,
customer_name = y.Customer.customer_name,
channel_type = y.Customer.channel_type,
customer_type = y.Customer.customer_type,
evaluate = y.Customer.evaluate,
store_type = y.Customer.store_type,
store_level = y.Customer.store_level,
area_id = y.Customer.area_id,
area_name = y.Customer.Area.area_name,
qty = y.qty,
y = x.Stock.y,
m = x.Stock.m
});
if (parm.start_date != null && parm.end_date != null)
{
DateTime start = (DateTime)parm.start_date;
DateTime end = (DateTime)parm.end_date;
if (start.Year == end.Year)
{//同年
items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month);
}
else {//不同年
List<int> start_m = startMonth(start.Month);
List<int> end_m = endMonth(end.Month);
items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m)));
}
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
if (parm.customer_name != null)
{
items = items.Where(x => x.customer_name.Contains(parm.customer_name));
}
if (parm.product_name != null)
{
items = items.Where(x => x.product_name.Contains(parm.product_name));
}
if (parm.customer_type != null)
{
items = items.Where(x => x.customer_type == parm.customer_type);
}
if (parm.channel_type != null)
{
items = items.Where(x => x.channel_type == parm.channel_type);
}
if (parm.evaluate != null)
{
items = items.Where(x => x.evaluate == parm.evaluate);
}
if (parm.store_type != null)
{
items = items.Where(x => x.store_type == parm.store_type);
}
if (parm.store_level != null)
{
items = items.Where(x => x.store_level == parm.store_level);
}
if (parm.area != null)
{
items = items.Where(x => x.area_id == parm.area);
}
var getTempVal = items.ToList();
foreach (var item in getTempVal)
{
if (item.qty > 0)
{
item.distributed = true;
}
}
#endregion
#region 整理報表列印格式
//取得進貨加總
var getSum = from x in getTempVal
group x by new
{
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
} into g
select (new CustomerProduct()
{
product_id = g.Key.product_id,
product_name = g.Key.product_name,
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
qty = g.Sum(z => z.qty)
});
//取得不重複客戶資料
var getPrintVal = (from x in getTempVal
group x by new
{
x.customer_id,
x.customer_name,
x.customer_type,
x.channel_type,
x.evaluate,
x.store_type,
x.store_level,
x.area_name
} into g
orderby g.Key.customer_id
select (new ExcelCustomerProduct()
{
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
customer_type = g.Key.customer_type,
channel_type = g.Key.channel_type,
evaluate = g.Key.evaluate,
store_type = g.Key.store_type,
store_level = g.Key.store_level,
area_name = g.Key.area_name
})).ToList();
foreach (var itemA in getPrintVal)
{
itemA.p_qtys = new List<PQList>();
#region 設定產品分部統計版型變數
foreach (var id in parm.ids)
{
itemA.p_qtys.Add(new PQList() { p_id = id, qty = 0, stock_qty = 0 });
}
#endregion
decimal sum_qty = 0;//加總判斷,如果加總為零就不顯示
foreach (var itemB in getSum)
{
if (itemA.customer_id == itemB.customer_id)
{
if (parm.ids.Contains(itemB.product_id))
{
//itemA.p_qtys.Add(itemB.qty);
//改為統計產品分布,不是進貨數量
if (itemB.qty != 0)
{
var getPQList = itemA.p_qtys.Where(x => x.p_id == itemB.product_id).First();
getPQList.qty = 1;
getPQList.stock_qty = itemB.qty;
}
sum_qty += itemB.qty;
}
}
}
if (sum_qty == 0)
{
itemA.is_hide = true;
}
}
#endregion
#region Excel Handle
int detail_row = 5;
#region 標題
sheet.Cells[1, 1].Value = "R03產品分佈統計表(客戶-產品)" + date_range;
//sheet.Cells[1, 1, 1, 7].Merge = true;
sheet.Cells[2, 1].Value = "[客戶名稱]";
sheet.Cells[2, 2].Value = "[區域\n群組]";
sheet.Cells[2, 3].Value = "[客戶\n類別]";
sheet.Cells[2, 4].Value = "[通路\n級別]";
sheet.Cells[2, 5].Value = "[客戶\n銷售等級]";
sheet.Cells[2, 6].Value = "[客戶\n型態]";
sheet.Cells[2, 7].Value = "[型態\n等級]";
setWrapText(sheet, 2, 2, 7);//換行設定
const int product_column = 8;//設定產品列起始列
int name_index = product_column;
foreach (var i in parm.names)
{
sheet.Cells[3, name_index].Value = "[" + i + "]";
//sheet.Cells[3, name_index, 3, name_index + 1].Merge = true;
sheet.Cells[4, name_index].Value = "分布";
sheet.Cells[4, name_index + 1].Value = "進貨量";
name_index += 2;
}
sheet.Cells[2, product_column].Value = "產品分布";
//sheet.Cells[2, product_column, 2, name_index - 1].Merge = true;
//setMerge_label(sheet, 2, 4, 1, 7);//合併上下儲存格 客戶名稱~型態等級
setFontColor_LabelBord(sheet, 2, 1, name_index - 1);//儲存格畫線+文字藍色
setFontColor_LabelBord(sheet, 3, 1, name_index - 1);
setFontColor_LabelBord(sheet, 4, 1, name_index - 1);
setFontColor_blue(sheet, 1, 1);
#endregion
#region 內容
decimal[] row_sum = new decimal[parm.ids.Count()];//計算底部加總_分布
decimal[] row_stock_sum = new decimal[parm.ids.Count()];//計算底部加總_進貨量
foreach (var item in getPrintVal)
{
if (!item.is_hide)//沒進貨量就不顯示
{
sheet.Cells[detail_row, 1].Value = item.customer_name;
sheet.Cells[detail_row, 2].Value = item.area_name;
sheet.Cells[detail_row, 3].Value = CodeSheet.GetCustomerTypeVal(item.customer_type);
sheet.Cells[detail_row, 4].Value = CodeSheet.GetChannelTypeVal(item.channel_type);
sheet.Cells[detail_row, 5].Value = CodeSheet.GetEvaluateVal(item.evaluate);
sheet.Cells[detail_row, 6].Value = CodeSheet.GetStoreTypeVal(item.store_type);
sheet.Cells[detail_row, 7].Value = CodeSheet.GetStoreLevelVal(item.store_level);
int qty_index = product_column;
foreach (var i in item.p_qtys)
{
sheet.Cells[detail_row, qty_index].Value = i.qty;//分布
sheet.Cells[detail_row, qty_index + 1].Value = i.stock_qty;//進貨量
row_sum[(qty_index - product_column) / 2] += i.qty;//分布加總
row_stock_sum[(qty_index - product_column) / 2] += i.stock_qty;//進貨量加總
qty_index += 2;
}
detail_row++;
}
}
#region 底部加總
sheet.Cells[detail_row, 1].Value = "[分布統計加總]";
setFontColor_red(sheet, detail_row, 1);
sheet.Cells[detail_row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 7].Merge = true;
for (var i = 0; i < parm.ids.Count(); i++)
{
//分布
sheet.Cells[detail_row, (i * 2) + product_column].Value = row_sum[i];
sheet.Cells[detail_row, (i * 2) + product_column].Style.Border.Top.Style = ExcelBorderStyle.Double;
sheet.Cells[detail_row, (i * 2) + product_column].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
//進貨量
sheet.Cells[detail_row, (i * 2) + product_column + 1].Value = row_stock_sum[i];
sheet.Cells[detail_row, (i * 2) + product_column + 1].Style.Border.Top.Style = ExcelBorderStyle.Double;
sheet.Cells[detail_row, (i * 2) + product_column + 1].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
}
#endregion
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = "R03產品分佈統計表(客戶-產品)" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
catch (Exception ex)
{
Console.Write(ex.Message);
return null;
}
finally
{
db0.Dispose();
}
}
public FileResult downloadExcel_ProductCustomer(ParmReportR04 parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("ProductCustomerData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["ProductCustomerData"];
sheet.View.TabSelected = true;
#region 取得客戶進貨數量
string date_range = "(All)";
var items = from x in db0.StockDetail
join y in db0.StockDetailQty
on x.stock_detail_id equals y.stock_detail_id
orderby x.Product.product_name
select (new CustomerProduct()
{
product_id = x.product_id,
product_name = x.Product.product_name,
customer_id = y.customer_id,
customer_name = y.Customer.customer_name,
channel_type = y.Customer.channel_type,
customer_type = y.Customer.customer_type,
evaluate = y.Customer.evaluate,
store_type = y.Customer.store_type,
store_level = y.Customer.store_level,
area_id = y.Customer.area_id,
area_name = y.Customer.Area.area_name,
qty = y.qty,
y = x.Stock.y,
m = x.Stock.m
});
if (parm.start_date != null && parm.end_date != null)
{
DateTime start = (DateTime)parm.start_date;
DateTime end = (DateTime)parm.end_date;
if (start.Year == end.Year)
{//同年
items = items.Where(x => x.y == start.Year && x.m >= start.Month && x.m <= end.Month);
}
else {//不同年
List<int> start_m = startMonth(start.Month);
List<int> end_m = endMonth(end.Month);
items = items.Where(x => (x.y == start.Year && start_m.Contains(x.m)) || (x.y == end.Year && end_m.Contains(x.m)));
}
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
if (parm.customer_name != null)
{
items = items.Where(x => x.customer_name.Contains(parm.customer_name));
}
if (parm.product_name != null)
{
items = items.Where(x => x.product_name.Contains(parm.product_name));
}
if (parm.customer_type != null)
{
items = items.Where(x => x.customer_type == parm.customer_type);
}
if (parm.channel_type != null)
{
items = items.Where(x => x.channel_type == parm.channel_type);
}
if (parm.evaluate != null)
{
items = items.Where(x => x.evaluate == parm.evaluate);
}
if (parm.store_type != null)
{
items = items.Where(x => x.store_type == parm.store_type);
}
if (parm.store_level != null)
{
items = items.Where(x => x.store_level == parm.store_level);
}
if (parm.area != null)
{
items = items.Where(x => x.area_id == parm.area);
}
if (parm.ids != null)
{
items = items.Where(x => parm.ids.Contains(x.product_id));
}
var getTempVal = items.ToList();
var getPrintVal = (from x in getTempVal
group x by new
{
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
x.customer_type,
x.channel_type,
x.evaluate,
x.store_type,
x.store_level,
x.area_name
} into g
select (new CustomerProduct()
{
product_id = g.Key.product_id,
product_name = g.Key.product_name,
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
customer_type = g.Key.customer_type,
channel_type = g.Key.channel_type,
evaluate = g.Key.evaluate,
store_type = g.Key.store_type,
store_level = g.Key.store_level,
area_name = g.Key.area_name,
qty = g.Sum(z => z.qty)
})).ToList();
foreach (var item in getPrintVal)
{
if (item.qty > 0)
{
item.distributed = true;
}
}
#endregion
#region Excel Handle
int detail_row = 3;
#region 標題
sheet.Cells[1, 1].Value = "R04產品分佈統計表(產品-客戶)" + date_range;
//sheet.Cells[1, 1, 1, 5].Merge = true;
sheet.Cells[2, 1].Value = "[產品名稱]";
sheet.Cells[2, 2].Value = "[客戶名稱]";
sheet.Cells[2, 3].Value = "[區域群組]";
sheet.Cells[2, 4].Value = "[是否分布]";
sheet.Cells[2, 5].Value = "[進貨量]";
sheet.Cells[2, 6].Value = "[客戶類別]";
sheet.Cells[2, 7].Value = "[通路級別]";
sheet.Cells[2, 8].Value = "[客戶銷售等級]";
sheet.Cells[2, 9].Value = "[客戶型態]";
sheet.Cells[2, 10].Value = "[型態等級]";
setFontColor_Label(sheet, 2, 1, 10);
setFontColor_blue(sheet, 1, 1);
#endregion
#region 內容
string subtotal_product_name = string.Empty;
int subtotal_distributed = 0;//分布 小計
decimal subtotal_qty = 0;//進貨量 小計
foreach (var item in getPrintVal)
{
if (item.distributed)//沒分布就不顯示
{
#region 小計判斷
if (detail_row == 3)
{
subtotal_product_name = item.product_name;//第一筆資料
}
else if (subtotal_product_name != item.product_name)//產品變換時,做一次小計
{
#region 小計
#region 小計欄位,合併及文字顏色
sheet.Cells[detail_row, 3].Value = "[小計]";
setFontColor_red(sheet, detail_row, 3);
sheet.Cells[detail_row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 2].Merge = true;
#endregion
//產品分布
sheet.Cells[detail_row, 4].Value = subtotal_distributed;
sheet.Cells[detail_row, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, 4].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
//進貨量
sheet.Cells[detail_row, 5].Value = subtotal_qty;
sheet.Cells[detail_row, 5].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, 5].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
#endregion
#region 不同產品區分版面
setBroder_red(sheet, detail_row + 1, 1, 10);
detail_row += 1;
#endregion
detail_row++;
subtotal_distributed = 0;//小計歸零
subtotal_qty = 0;//小計歸零
subtotal_product_name = item.product_name;//紀錄新產品
}
#endregion
sheet.Cells[detail_row, 1].Value = item.product_name;
sheet.Cells[detail_row, 2].Value = item.customer_name;
sheet.Cells[detail_row, 3].Value = item.area_name;
sheet.Cells[detail_row, 4].Value = item.distributed ? "Yes" : "No";
if (item.distributed) { setFontColor_red(sheet, detail_row, 4); }
sheet.Cells[detail_row, 5].Value = item.qty;
sheet.Cells[detail_row, 6].Value = CodeSheet.GetCustomerTypeVal(item.customer_type);
sheet.Cells[detail_row, 7].Value = CodeSheet.GetChannelTypeVal(item.channel_type);
sheet.Cells[detail_row, 8].Value = CodeSheet.GetEvaluateVal(item.evaluate);
sheet.Cells[detail_row, 9].Value = CodeSheet.GetStoreTypeVal(item.store_type);
sheet.Cells[detail_row, 10].Value = CodeSheet.GetStoreLevelVal(item.store_level);
#region 小計加總計算
subtotal_distributed++;
subtotal_qty += item.qty;
#endregion
detail_row++;
}
}
#region 最後一次小計
#region 小計欄位,合併及文字顏色
sheet.Cells[detail_row, 3].Value = "[小計]";
setFontColor_red(sheet, detail_row, 3);
sheet.Cells[detail_row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//sheet.Cells[detail_row, 1, detail_row, 2].Merge = true;
#endregion
//產品分布
sheet.Cells[detail_row, 4].Value = subtotal_distributed;
sheet.Cells[detail_row, 4].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, 4].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
//進貨量
sheet.Cells[detail_row, 5].Value = subtotal_qty;
sheet.Cells[detail_row, 5].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[detail_row, 5].Style.Border.Top.Color.SetColor(System.Drawing.Color.Red);
#region 不同產品區分版面
setBroder_red(sheet, detail_row + 1, 1, 10);
detail_row += 1;
#endregion
detail_row++;
#endregion
#endregion
#region excel排版
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int j = startColumn; j <= endColumn; j++)
{
//sheet.Column(j).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//靠左對齊
//sheet.Column(j).Width = 30;//固定寬度寫法
sheet.Column(j).AutoFit();//依內容fit寬度
}//End for
#endregion
sheet.Cells.Calculate(); //要對所以Cell做公計計算 否則樣版中的公式值是不會變的
#endregion
string filename = "R04產品分佈統計表(產品-客戶)" + "[" + DateTime.Now.ToString("yyyyMMddHHmm") + "].xlsx";
excel.Save();
fs.Position = 0;
return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
catch (Exception ex)
{
Console.Write(ex.Message);
return null;
}
finally
{
db0.Dispose();
}
}
