public FileResult downloadExcel_VisitProduct(ParmGetCustomerVisit parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("VisitProductData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["VisitProductData"];
sheet.View.TabSelected = true;
#region 取得拜訪產品分布
string date_range = "(All)";
var items = from x in db0.VisitDetailProduct
orderby x.i_InsertDateTime, x.users_id, x.customer_id
select (new VisitProduct()
{
customer_id = x.customer_id,
customer_name = x.Customer.customer_name,
users_id = x.users_id,
user_name = "",
product_id = x.product_id,
product_name = x.Product.product_name,
price = x.price,
visit_date = x.VisitDetail.Visit.visit_date,
description = x.description,
area_id = x.Customer.area_id
});
#region 驗證業務端只能看到自己的資料
var getRoles = db0.AspNetUsers.FirstOrDefault(x => x.Id == this.UserId).AspNetRoles.Select(x => x.Name);
if (!getRoles.Contains("Admins") & !getRoles.Contains("Managers"))
{
items = items.Where(x => x.users_id == this.UserId);
}
#endregion
if (parm.start_date != null && parm.end_date != null)
{
DateTime end = ((DateTime)parm.end_date).AddDays(1);
items = items.Where(x => x.visit_date >= parm.start_date && x.visit_date < end);
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
if (parm.users_id != null)
{
items = items.Where(x => x.users_id == parm.users_id);
}
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.area != null)
{
items = items.Where(x => x.area_id == parm.area);
}
var getPrintVal = items.ToList();
foreach (var item in getPrintVal)
{
string User_Name = db0.AspNetUsers.FirstOrDefault(x => x.Id == item.users_id).user_name_c;
item.user_name = User_Name;
if (item.price > 0)
{
item.distributed = true;
}
}
#endregion
#region Excel Handle
int detail_row = 3;
#region 標題
sheet.Cells[1, 1].Value = "R02業務拜訪產品分佈統計表" + date_range;
sheet.Cells[1, 1, 1, 6].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 = "[備註]";
setFontColor_Label(sheet, 2, 1, 7);
setFontColor_blue(sheet, 1, 1);
#endregion
#region 內容
foreach (var item in getPrintVal)
{
if (item.distributed)//沒分布就不顯示
{
sheet.Cells[detail_row, 1].Value = item.user_name;
sheet.Cells[detail_row, 2].Value = item.customer_name;
sheet.Cells[detail_row, 3].Value = item.product_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.price;
sheet.Cells[detail_row, 6].Value = item.visit_date.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 7].Value = item.description;
detail_row++;
}
}
#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 = "R02業務拜訪產品統計表" + "[" + 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();
}
}
/// <summary>
/// R05報表-2015/7/28修改將經銷商移除但保留之前程式碼
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public FileResult downloadExcel_CustomerAgent_old(ParmGetCustomerVisit parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
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.Stock.agent_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
});
if (parm.start_date != null && parm.end_date != null)
{
items = items.Where(x => x.y >= ((DateTime)parm.start_date).Year && x.m >= ((DateTime)parm.start_date).Month);
items = items.Where(x => x.y <= ((DateTime)parm.end_date).Year && x.m <= ((DateTime)parm.end_date).Month);
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));
}
var getTempVal = items.ToList();
#endregion
#region 整理報表列印格式
//取得每月進貨加總
var getSumMonth = from x in getTempVal
group x by new
{
x.agent_id,
x.agent_name,
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
x.m
} into g
select (new CustomerAgent()
{
agent_id = g.Key.agent_id,
agent_name = g.Key.agent_name,
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.agent_id,
x.agent_name,
x.product_id,
x.product_name,
x.customer_id,
x.customer_name,
} into g
orderby g.Key.agent_id, g.Key.customer_id, g.Key.product_id
select (new ExcleCustomerAgent()
{
agent_id = g.Key.agent_id,
agent_name = g.Key.agent_name,
product_id = g.Key.product_id,
product_name = g.Key.product_name,
customer_id = g.Key.customer_id,
customer_name = g.Key.customer_name,
})).ToList();
foreach (var itemA in getPrintVal)
{
foreach (var itemB in getSumMonth)
{
if (itemA.agent_id == itemB.agent_id && itemA.customer_id == itemB.customer_id && itemA.product_id == itemB.product_id)
{
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, 4].Merge = true;
sheet.Cells[2, 1].Value = "[經銷商名稱]";
sheet.Cells[2, 2].Value = "[客戶名稱]";
sheet.Cells[2, 3].Value = "[產品名稱]";
setMerge_label(sheet, 2, 3, 1, 3);
sheet.Cells[3, 4].Value = "[1月份]";
sheet.Cells[3, 5].Value = "[2月份]";
sheet.Cells[3, 6].Value = "[3月份]";
sheet.Cells[3, 7].Value = "[4月份]";
sheet.Cells[3, 8].Value = "[5月份]";
sheet.Cells[3, 9].Value = "[6月份]";
sheet.Cells[3, 10].Value = "[7月份]";
sheet.Cells[3, 11].Value = "[8月份]";
sheet.Cells[3, 12].Value = "[9月份]";
sheet.Cells[3, 13].Value = "[10月份]";
sheet.Cells[3, 14].Value = "[11月份]";
sheet.Cells[3, 15].Value = "[12月份]";
sheet.Cells[3, 16].Value = "[加總]";
sheet.Cells[2, 4].Value = "產品進貨數量(1~12月)";
sheet.Cells[2, 4, 2, 15].Merge = true;
setFontColor_LabelBord(sheet, 2, 1, 15);
setFontColor_LabelBord(sheet, 3, 1, 15);
setFontColor_blue(sheet, 1, 1);
setFontColor_red(sheet, 3, 16);
sheet.Cells[3, 16].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
#endregion
#region 內容
decimal[] row_sum = new decimal[12];
foreach (var item in getPrintVal)
{
sheet.Cells[detail_row, 1].Value = item.agent_name;
sheet.Cells[detail_row, 2].Value = item.customer_name;
sheet.Cells[detail_row, 3].Value = item.product_name;
sheet.Cells[detail_row, 4].Value = item.qty_1;
sheet.Cells[detail_row, 5].Value = item.qty_2;
sheet.Cells[detail_row, 6].Value = item.qty_3;
sheet.Cells[detail_row, 7].Value = item.qty_4;
sheet.Cells[detail_row, 8].Value = item.qty_5;
sheet.Cells[detail_row, 9].Value = item.qty_6;
sheet.Cells[detail_row, 10].Value = item.qty_7;
sheet.Cells[detail_row, 11].Value = item.qty_8;
sheet.Cells[detail_row, 12].Value = item.qty_9;
sheet.Cells[detail_row, 13].Value = item.qty_10;
sheet.Cells[detail_row, 14].Value = item.qty_11;
sheet.Cells[detail_row, 15].Value = item.qty_12;
sheet.Cells[detail_row, 16].Formula = string.Format("=SUM(D{0}:O{0})", detail_row);
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;
detail_row++;
}
#region 底部加總
int start_row = 4;
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, 3].Merge = true;
string[] row_eng = new string[] { "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O" };
for (var i = 0; i < 12; i++)
{
//sheet.Cells[detail_row, i + 4].Value = string.Format("SUM({0}{1}:{0}{2})", row_eng[i], start_row, detail_row - 1);
sheet.Cells[detail_row, i + 4].Value = row_sum[i];
sheet.Cells[detail_row, i + 4].Style.Border.Top.Style = ExcelBorderStyle.Double;
sheet.Cells[detail_row, i + 4].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.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();
}
}
// GET: ExcelReport
public FileResult downloadExcel_CustomerVisit(ParmGetCustomerVisit parm)
{
ExcelPackage excel = null;
MemoryStream fs = null;
var db0 = getDB0();
try
{
fs = new MemoryStream();
excel = new ExcelPackage(fs);
excel.Workbook.Worksheets.Add("CustomerVisitData");
ExcelWorksheet sheet = excel.Workbook.Worksheets["CustomerVisitData"];
sheet.View.TabSelected = true;
#region 取得客戶拜訪紀錄
string date_range = "(All)";
var items = (from x in db0.VisitDetail
orderby x.start_time, x.customer_id
select (new CustomerVisit()
{
customer_name = x.Customer.customer_name,
customer_id = x.customer_id,
visit_date = x.Visit.visit_date,
state = x.state,
visit_start = x.start_time,
visit_end = x.end_time,
cumulative_time = x.cumulative_time,
users_id = x.users_id,
user_name = "",
checkInsert = false,
memo = x.memo,
area_id = x.Customer.area_id
}));
#region 驗證業務端只能看到自己的資料
var getRoles = db0.AspNetUsers.FirstOrDefault(x => x.Id == this.UserId).AspNetRoles.Select(x => x.Name);
if (!getRoles.Contains("Admins") & !getRoles.Contains("Managers"))
{
items = items.Where(x => x.users_id == this.UserId);
}
#endregion
if (parm.start_date != null && parm.end_date != null)
{
DateTime end = ((DateTime)parm.end_date).AddDays(1);
items = items.Where(x => x.visit_date >= parm.start_date && x.visit_date < end);
date_range = "(" + ((DateTime)parm.start_date).ToString("yyyy/MM/dd") + "~" + ((DateTime)parm.end_date).ToString("yyyy/MM/dd") + ")";
}
if (parm.users_id != null)
{
items = items.Where(x => x.users_id == parm.users_id);
}
if (parm.customer_name != null)
{
items = items.Where(x => x.customer_name.Contains(parm.customer_name));
}
if (parm.area != null)
{
items = items.Where(x => x.area_id == parm.area);
}
var getPrintVal = items.ToList();
foreach (var item in getPrintVal)
{
string User_Name = db0.AspNetUsers.FirstOrDefault(x => x.Id == item.users_id).user_name_c;
item.user_name = User_Name;
DateTime? CustomerInsertDate = db0.Customer.FirstOrDefault(x => x.customer_id == item.customer_id).i_InsertDateTime;
if (CustomerInsertDate != null && ((DateTime)CustomerInsertDate).Year == item.visit_date.Year && ((DateTime)CustomerInsertDate).Month == item.visit_date.Month)
{
item.checkInsert = true;
}
}
#endregion
#region Excel Handle
int detail_row = 3;
#region 標題
sheet.Cells[1, 1].Value = "R01客戶拜訪紀錄_月報表" + 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 = "[拜訪日期]";
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 = "[備註]";
setFontColor_Label(sheet, 2, 1, 9);
setFontColor_blue(sheet, 1, 1);
#endregion
#region 內容
foreach (var item in getPrintVal)
{
sheet.Cells[detail_row, 1].Value = item.user_name;
sheet.Cells[detail_row, 2].Value = item.customer_name;
sheet.Cells[detail_row, 3].Value = item.visit_date.ToString("yyyy/MM/dd");
sheet.Cells[detail_row, 4].Value = item.visit_start != null ? ((DateTime)item.visit_start).ToString("tt HH:mm") : "";
sheet.Cells[detail_row, 5].Value = item.visit_end != null ? ((DateTime)item.visit_end).ToString("tt HH:mm") : "";
sheet.Cells[detail_row, 6].Value = item.cumulative_time;
sheet.Cells[detail_row, 7].Value = CodeSheet.GetStateVal(item.state);
sheet.Cells[detail_row, 8].Value = item.checkInsert ? "Yes" : "";
if (item.checkInsert) { setFontColor_red(sheet, detail_row, 8); }
sheet.Cells[detail_row, 9].Value = item.memo;
detail_row++;
}
#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 = "R01客戶拜訪紀錄月報表" + "[" + 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();
}
}
