public String getReport1Title(ReportSetViewModel model)
{
string ret = "";
var qry = db.WBSEventC.Where(x => x.CreateDateTime.Value.CompareTo(model.CreateDTStart) >= 0 && x.CreateDateTime.Value.CompareTo(model.CreateDTEnd) <= 0);
if (model.CGroupID != "" && model.CGroupID != null) //客戶群
{
ret += db.CustomerGroup.Where(x => x.CGroupID == model.CGroupID).Select(x => x.CGroupShortName).First() + "-";
}
if (model.CustomerRef != "" && model.CustomerRef != null) //客戶簡稱
{
int temp = Int32.Parse(model.CustomerRef);
ret += db.Customer.Where(x => x.CustomerRef == temp).Select(x => x.ShortName).First() + "-";
}
ret += "客戶叫修服務報表";
if (model.RType == 1)
{
ret += "-內部檢核";
}
ret += "(叫修日期:" + String.Format("{0:00}", model.CreateDTStart.Year) + "/" + String.Format("{0:00}", model.CreateDTStart.Month) + "/" + String.Format("{0:00}", model.CreateDTStart.Day)
+ "~" + String.Format("{0:00}", model.CreateDTEnd.Year) + "/" + String.Format("{0:00}", model.CreateDTEnd.Month) + "/" + String.Format("{0:00}", model.CreateDTEnd.Day) + ")";
return ret;
}
public List<Report1ViewModel> getReport1(ReportSetViewModel model)
{
List<Report1ViewModel> ret = new List<Report1ViewModel>();
model.CreateDTEnd = model.CreateDTEnd.AddHours(23).AddMinutes(59).AddSeconds(59);
var qry = db.WBSEventC.Where(x => x.CreateDateTime.Value.CompareTo(model.CreateDTStart) >= 0 && x.CreateDateTime.Value.CompareTo(model.CreateDTEnd) <= 0);
if(model.EventClass == "C01&M01")
qry = qry.Where(x => x.EventClass == "C01" || x.EventClass == "M01");
else
qry = qry.Where(x => x.EventClass == model.EventClass);
if (model.CaseClass == 1) //只找母單
qry = qry.Where(x => x.ChildCaseID == 1);
if (model.CGroupID != "" && model.CGroupID != null) //客戶群
{
qry = qry.Where(x => x.WBSEventM.Customer1.GroupID == model.CGroupID);
}
if (model.CloseSel == 1) //結案檢核:已結案
{
qry = qry.Where(x => x.WBSEventM.CaseMStatus == "9");
}
else if (model.CloseSel == 2) //結案檢核:未結案
{
qry = qry.Where(x => x.WBSEventM.CaseMStatus != "9");
}
if (model.CustomerRef != "" && model.CustomerRef != null) //客戶簡稱
{
int temp = Int32.Parse(model.CustomerRef);
qry = qry.Where(x => x.WBSEventM.CustomerRef == temp);
}
if (model.ArriveSel == 1) //到達檢核:準時
{
}
else if (model.ArriveSel == 2) //到達檢核:逾時
{
}
if (model.ProductClassID != null && model.ProductClassID != 0) //客戶簡稱
{
qry = qry.Where(x => x.ProductClass == model.ProductClassID);
}
foreach (WBSEventC c in qry)
{
Report1ViewModel v = new Report1ViewModel();
v.CustomerSName = c.WBSEventM.Customer1.ShortName;
v.CustomerRef = c.WBSEventM.CustomerRef;
v.CustomerMNum = "";
v.CreateDate = String.Format("{0:00}", c.CreateDateTime.Value.Year) + "/" + String.Format("{0:00}", c.CreateDateTime.Value.Month) + "/" + String.Format("{0:00}", c.CreateDateTime.Value.Day);
v.CreateTime = String.Format("{0:00}", c.CreateDateTime.Value.Hour) + ":" + String.Format("{0:00}", c.CreateDateTime.Value.Minute);
//v.CreateDate = c.CreateDateTime.Value.ToString("yyyy/MM/dd");
//v.CreateTime = c.CreateDateTime.Value.ToString("HH:mm");
if (c.ExpectArriveSW == "1")
v.ExpectArriveSWStr = "合約規定";
else if (c.ExpectArriveSW == "2")
v.ExpectArriveSWStr = "建議期望";
else if (c.ExpectArriveSW == "3")
v.ExpectArriveSWStr = "客戶指定";
else if (c.ExpectArriveSW == "4")
v.ExpectArriveSWStr = "自派新單";
else
{
v.ExpectArriveSWStr = "自行約定";
}
v.ProductNote = c.ProductClass1.Note;
v.ProductClass = c.ProductClass;
v.ItemNote = c.ItemClass1.Note;
v.ItemClass = c.ItemClass;
v.QuestionDescription = c.QuestionDescription;
v.QuestionNote = c.QuestionClass1.Note;
v.QuestionClass = c.QuestionClass;
v.ServiceReport = c.ServiceReport;
if(c.ServiceCreateUserID != null)
v.ServiceUserName = c.UserData8.UserName;
v.WBSCaseNumberM = c.WBSEventM.CaseClass + c.WBSEventM.CaseDate + "-";
if (c.WBSEventM.CaseCode < 10)
v.WBSCaseNumberM += "0000" + c.WBSEventM.CaseCode.ToString();
else if (c.WBSEventM.CaseCode < 100 && c.WBSEventM.CaseCode >= 10)
v.WBSCaseNumberM += "000" + c.WBSEventM.CaseCode.ToString();
else if (c.WBSEventM.CaseCode < 1000 && c.WBSEventM.CaseCode >= 100)
v.WBSCaseNumberM += "00" + c.WBSEventM.CaseCode.ToString();
else if (c.WBSEventM.CaseCode < 10000 && c.WBSEventM.CaseCode >= 1000)
v.WBSCaseNumberM += "0" + c.WBSEventM.CaseCode.ToString();
else
v.WBSCaseNumberM += c.WBSEventM.CaseCode.ToString();
v.WBSCaseNumberC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString();
v.isClosedStr = "未結案";
v.ClosedMinute = "";
if (c.WBSEventM.CaseMStatus == "9") {
v.isClosedStr = "已結案";
System.TimeSpan diff1 = c.WBSEventM.CloseDT.Value.Subtract(c.CreateDateTime.Value);
int d = diff1.Days;
int h = d * 24 + diff1.Hours;
int m = h * 60 + diff1.Minutes;
v.ClosedMinute = m.ToString();
//int min = Int32.Parse(diff1.ToString("%d"));
}
v.ArriveDate = "";
v.ArriveTime = "";
v.ArriveMinute = "";
if (c.ServiceArriveDT != null)
{
v.ArriveDate = String.Format("{0:00}", c.ServiceArriveDT.Value.Year) + "/" + String.Format("{0:00}", c.ServiceArriveDT.Value.Month) + "/" + String.Format("{0:00}", c.ServiceArriveDT.Value.Day);
v.ArriveTime = String.Format("{0:00}", c.ServiceArriveDT.Value.Hour) + ":" + String.Format("{0:00}", c.ServiceArriveDT.Value.Minute);
//v.ArriveDate = c.ServiceArriveDT.Value.ToString("yyyy/MM/dd");
//v.ArriveTime = c.ServiceArriveDT.Value.ToString("HH:mm");
System.TimeSpan diff1 = c.ServiceArriveDT.Value.Subtract(c.CreateDateTime.Value);
int d = diff1.Days;
int h = d * 24 + diff1.Hours;
int m = h * 60 + diff1.Minutes;
//if (diff1.Days > 0)
// v.ArriveMinute += diff1.Days.ToString() + "日";
//if (diff1.Days > 0 || diff1.Hours > 0)
// v.ArriveMinute += diff1.Hours.ToString() + "時";
//v.ArriveMinute += diff1.Minutes.ToString() + "分";
v.ArriveMinute = m.ToString();
}
v.LeaveDate = "";
v.LeaveTime = "";
v.LeaveMinute = "";
if (c.ServiceLeaveDT != null)
{
v.LeaveDate = String.Format("{0:00}", c.ServiceLeaveDT.Value.Year) + "/" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Month) + "/" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Day);
v.LeaveTime = String.Format("{0:00}", c.ServiceLeaveDT.Value.Hour) + ":" + String.Format("{0:00}", c.ServiceLeaveDT.Value.Minute);
//v.LeaveDate = c.ServiceLeaveDT.Value.ToString("yyyy/MM/dd");
//v.LeaveTime = c.ServiceLeaveDT.Value.ToString("HH:mm");
System.TimeSpan diff1 = c.ServiceLeaveDT.Value.Subtract(c.CreateDateTime.Value);
int d = diff1.Days;
int h = d * 24 + diff1.Hours;
int m = h * 60 + diff1.Minutes;
v.LeaveMinute = m.ToString();
}
//v.ArriveOnTimeRequirement = "";
v.isArriveOnTime = "";
v.isLeaveOnTime = "";
//v.LeaveOnTimeRequirement = "";
if (db.SystemSetting.Where(x => x.EventClass == model.EventClass).Count() > 0)
{
var qry2 = db.SystemSetting.Where(x => x.EventClass == model.EventClass).First();
v.ArriveOnTimeRequirement = model.EventClass + "-" + qry2.SugArriveMin.ToString() + "分";
v.LeaveOnTimeRequirement = model.EventClass + "-" + qry2.SugFinishMin.ToString() + "分";
if(v.ArriveMinute != "")
{
if(Int32.Parse(v.ArriveMinute) <= qry2.SugArriveMin)
{
v.isArriveOnTime = "系統準時";
if (model.ArriveSel == 2)
continue;
}
else
{
v.isArriveOnTime = "系統未準時";
if (model.ArriveSel == 1)
continue;
}
}
if (v.LeaveMinute != "")
{
if (Int32.Parse(v.LeaveMinute) <= qry2.SugFinishMin)
{
v.isLeaveOnTime = "系統準時";
if (model.LeaveSel == 2)
continue;
}
else
{
v.isLeaveOnTime = "系統未準時";
if (model.LeaveSel == 1)
continue;
}
}
}
else
{
var qry2 = db.SystemSetting.Where(x => x.isSysDefault == true).First();
v.ArriveOnTimeRequirement = "系統-" + qry2.SugArriveMin.ToString() + "分";
v.LeaveOnTimeRequirement = "系統-" + qry2.SugFinishMin.ToString() + "分";
if (v.ArriveMinute != "")
{
if (Int32.Parse(v.ArriveMinute) <= qry2.SugArriveMin)
{
v.isArriveOnTime = "系統準時";
if (model.ArriveSel == 2)
continue;
}
else
{
v.isArriveOnTime = "系統未準時";
if (model.ArriveSel == 1)
continue;
}
}
if (v.LeaveMinute != "")
{
if (Int32.Parse(v.LeaveMinute) <= qry2.SugFinishMin)
{
v.isLeaveOnTime = "系統準時";
if (model.LeaveSel == 2)
continue;
}
else
{
v.isLeaveOnTime = "系統未準時";
if (model.LeaveSel == 1)
continue;
}
}
}
if (model.ArriveSel != 0)
{
if (v.ArriveMinute == "")
continue;
}
if (model.LeaveSel != 0)
{
if(v.LeaveMinute == "")
continue;
}
v.ClosedOnLineStr = "否"; //線上結案
if (c.ServiceLeaveDT == null && c.QuestionFinishSW == "Y") {
v.ClosedOnLineStr = "是";
v.isLeaveOnTime = "系統準時";
v.LeaveMinute = "0";
v.LeaveTime = v.CreateTime;
v.LeaveDate = v.CreateDate;
v.ServiceUserName = c.UserData.UserName;
}
ret.Add(v);
}
return ret;
}
//public ActionResult Report_Create(ReportSetViewModel model, string ReportIndex)
public void Report_Create(ReportSetViewModel model, string ReportIndex)
{
ViewBag.ReportIndex = ReportIndex;
string ReportNo = "ReportTemplate1";
if (ReportIndex.Equals("1"))
{
ReportNo = "ReportTemplate1";
}
string strHtml = "";
string RepName = "";
var wb = new XLWorkbook();
switch (ReportIndex)
{
#region "客戶叫修服務報表"
case "1":
string RegStatusName = "";
if (model.RType == 0)
{
RegStatusName = "";
}
else
{
RegStatusName = "-內部檢核";
}
RepName = "客戶叫修服務報表" + RegStatusName;
wb = Report1_Create(model, RepName);
break;
#endregion
default:
return;
}
// 匯出檔名
var browser = Request.Browser.Browser;
var exportFileName = browser.Equals("Firefox", StringComparison.OrdinalIgnoreCase)
? RepName + ".xls"
: HttpUtility.UrlEncode(RepName + ".xls", Encoding.UTF8);
Response.AddHeader(
"Content-Disposition",
string.Format("attachment;filename={0}", exportFileName));
//wb.SaveAs(Response.OutputStream);
wb.SaveAs(Response.OutputStream);
//return wb.SaveAs(Response.OutputStream);
//return File(Response.OutputStream, "application/vnd.ms-excel", RepName + ".xls");
}
private XLWorkbook Report1_Create(ReportSetViewModel model, string RepName)
{
var workbook = new XLWorkbook();
var Data = reportRespositiory.getReport1(model);
#region 叫-叫修(工作表1)
var sheet = workbook.Worksheets.Add("叫-叫修");
int row = 3;
int col = 15;
double[] Colwidth = { 5, 11, 15, 20, 20, 11, 10, 11, 10, 11,
10, 11, 5, 16, 16};
double[] Colwidth2 = { 5, 11, 15, 20, 20, 11, 10, 11, 10, 11,
10, 11, 5, 16, 16, 5, 11, 16, 15, 15,
15, 12, 15, 19, 14, 19, 12, 7, 15, 20};
if(model.RType == 0)
{
for (int i = 0; i < Colwidth.Length; i++)
{
sheet.Column(i + 1).Width = Colwidth[i];
}
}
else
{
for (int i = 0; i < Colwidth2.Length; i++)
{
sheet.Column(i + 1).Width = Colwidth2[i];
}
}
sheet.Style.Font.FontName = "微軟正黑體";
sheet.Style.Font.FontSize = 12;
sheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
sheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet.PageSetup.SetRowsToRepeatAtTop(1, 4);
sheet.PageSetup.SetColumnsToRepeatAtLeft(1, 11);
var row1 = sheet.Row(1);
var row2 = sheet.Row(2);
//var row3 = sheet.Row(3);
row1.Style.Font.FontSize = 14;
row1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
row1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
row2.Style.Font.FontSize = 14;
row2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
row2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//row3.Style.Font.FontSize = 14;
//row3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//row3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet.PageSetup.PageOrientation = XLPageOrientation.Portrait;
sheet.PageSetup.PaperSize = XLPaperSize.A4Paper;
sheet.PageSetup.Margins.Bottom = 0.4;
sheet.PageSetup.Margins.Top = 0.4;
sheet.PageSetup.Margins.Left = 0.2;
sheet.PageSetup.Margins.Right = 0.2;
sheet.PageSetup.CenterHorizontally = true;
sheet.Row(3).Height = 50;
sheet.Cell(3, 1).Value = "序號";
sheet.Cell(3, 2).Value = "客戶簡稱";
//sheet.Cell(3, 3).Style.Alignment.WrapText = true;
sheet.Cell(3, 3).Value = "產品大類";
sheet.Cell(3, 4).Value = "問題描述";
sheet.Cell(3, 5).Value = "處理說明";
sheet.Cell(3, 6).Value = "叫修日期";
sheet.Cell(3, 7).Value = "叫修時間";
sheet.Cell(3, 8).Value = "到修日期";
sheet.Cell(3, 9).Value = "到修時間";
sheet.Cell(3, 10).Value = "修復日期";
sheet.Cell(3, 11).Value = "修復時間";
sheet.Cell(3, 12).Value = "維護人員";
sheet.Cell(3, 13).Value = "";
sheet.Cell(3, 14).Style.Alignment.WrapText = true;
sheet.Cell(3, 14).Value = "叫修-到場時間\r\n(分)(開單-到達)";
sheet.Cell(3, 15).Style.Alignment.WrapText = true;
sheet.Cell(3, 15).Value = "叫修-完修時間\r\n(分)(開單-解決)";
if (model.RType != 0)
{
sheet.Cell(3, 16).Value = "";
sheet.Cell(3, 17).Value = "線上排除";
sheet.Cell(3, 18).Value = "客戶維修編號";
sheet.Cell(3, 19).Value = "產品大類";
sheet.Cell(3, 20).Value = "設備分類";
sheet.Cell(3, 21).Value = "問題分類";
sheet.Cell(3, 22).Style.Alignment.WrapText = true;
sheet.Cell(3, 22).Value = "到達方式\r\n(自行約定)";
sheet.Cell(3, 23).Style.Alignment.WrapText = true;
sheet.Cell(3, 23).Value = "是否準時到場\r\n(1.合約2.系統)";
sheet.Cell(3, 24).Style.Alignment.WrapText = true;
sheet.Cell(3, 24).Value = "準時到場判斷基礎\r\n(開單-到達 01單)";
sheet.Cell(3, 25).Value = "是否準時修復";
sheet.Cell(3, 26).Style.Alignment.WrapText = true;
sheet.Cell(3, 26).Value = "準時完修判斷基礎\r\n(開單-解決 01單)";
sheet.Cell(3, 27).Value = "單號";
sheet.Cell(3, 28).Value = "子單";
sheet.Cell(3, 29).Value = "結案否(內控)";
sheet.Cell(3, 30).Style.Alignment.WrapText = true;
sheet.Cell(3, 30).Value = "結案所需時間(分)\r\n(內控)(開單-結案)";
col = 30;
}
if (Data.Count() == 0)
{
var row4 = sheet.Row(4);
row4.Style.Font.FontSize = 14;
row4.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row4.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet.Range(4, 1, 4, col).Merge(true);
sheet.Cell(4, 1).Value = "查無資料";
row++;
}
else
{
int c = 1;
foreach (var o in Data)
{
row++;
sheet.Cell(row, 1).Value = c;
sheet.Cell(row, 2).Value = o.CustomerSName;
sheet.Cell(row, 3).Value = o.ProductNote;
sheet.Cell(row, 4).Value = o.QuestionDescription;
sheet.Cell(row, 5).Value = o.ServiceReport;
sheet.Cell(row, 6).Value = o.CreateDate;
sheet.Cell(row, 7).Style.DateFormat.Format = "hh:mm";
sheet.Cell(row, 7).Value = o.CreateTime;
sheet.Cell(row, 8).Value = o.ArriveDate;
sheet.Cell(row, 9).Style.DateFormat.Format = "hh:mm";
sheet.Cell(row, 9).Value = o.ArriveTime;
sheet.Cell(row, 10).Value = o.LeaveDate;
sheet.Cell(row, 11).Style.DateFormat.Format = "hh:mm";
sheet.Cell(row, 11).Value = o.LeaveTime;
sheet.Cell(row, 12).Value = o.ServiceUserName;
sheet.Cell(row, 13).Value = "";
sheet.Cell(row, 14).Value = o.ArriveMinute;
sheet.Cell(row, 15).Value = o.LeaveMinute;
if (model.RType != 0)
{
sheet.Cell(row, 16).Value = "";
sheet.Cell(row, 17).Value = o.ClosedOnLineStr;
sheet.Cell(row, 18).Value = o.CustomerMNum;
sheet.Cell(row, 19).Value = o.ProductNote;
sheet.Cell(row, 20).Value = o.ItemNote;
sheet.Cell(row, 21).Value = o.QuestionNote;
sheet.Cell(row, 22).Value = o.ExpectArriveSWStr;
sheet.Cell(row, 23).Value = o.isArriveOnTime;
sheet.Cell(row, 24).Value = o.ArriveOnTimeRequirement;
sheet.Cell(row, 25).Value = o.isLeaveOnTime;
sheet.Cell(row, 26).Value = o.LeaveOnTimeRequirement;
sheet.Cell(row, 27).Value = o.WBSCaseNumberM;
sheet.Cell(row, 28).Value = o.WBSCaseNumberC;
sheet.Cell(row, 29).Value = o.isClosedStr;
sheet.Cell(row, 30).Value = o.ClosedMinute;
}
c++;
}
var range3 = sheet.Range(4, 1, row, col);
range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
range3.Style.Alignment.WrapText = true;
}
var range2 = sheet.Range(3, 1, row, col);
range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick;
range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet.Range(1, 1, 1, col).Merge(true);
sheet.Range(2, 1, 2, col).Merge(true);
//sheet.Range(3, 1, 3, col).Merge(true);
//sheet.Cell(3, 1).Value = data.ProgramScript;
sheet.Range(3, 1, 3, col).Style.Fill.BackgroundColor = XLColor.Yellow;
sheet.Range(3, 16, 3, col).Style.Fill.BackgroundColor = XLColor.GreenPigment;
sheet.Cell(1, 1).Value = "NSMMS-客戶叫修服務系統";
string title = "";
title = RepName;
sheet.Cell(2, 1).Value = reportRespositiory.getReport1Title(model);
//sheet.Cell(3, 1).Value = data.RegScript;
string str = "製表日期:" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
sheet.Range(row + 1, 1, row + 1, col).Merge(true);
sheet.Cell(row + 1, 1).Value = str;
sheet.Row(row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
#endregion
#region 叫號維護統計(工作表2)
var Data2 = reportRespositiory.getReport1S2(Data);
var sheet2 = workbook.Worksheets.Add("叫號維護統計");
int s2row = 4;
int s2col = 7;
double[] s2Colwidth = { 16, 16, 20, 6, 6, 6, 6};
for (int i = 0; i < s2Colwidth.Length; i++)
{
sheet2.Column(i + 1).Width = s2Colwidth[i];
}
sheet2.Style.Font.FontName = "微軟正黑體";
sheet2.Style.Font.FontSize = 12;
sheet2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
sheet2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet2.PageSetup.SetRowsToRepeatAtTop(1, 4);
sheet2.PageSetup.SetColumnsToRepeatAtLeft(1, 11);
var s2row1 = sheet2.Row(1);
var s2row2 = sheet2.Row(2);
var s2row3 = sheet2.Row(3);
s2row1.Style.Font.FontSize = 14;
s2row1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
s2row1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
s2row2.Style.Font.FontSize = 14;
s2row2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
s2row2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
s2row3.Style.Font.FontSize = 14;
s2row3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
s2row3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet2.PageSetup.PageOrientation = XLPageOrientation.Portrait;
sheet2.PageSetup.PaperSize = XLPaperSize.A4Paper;
sheet2.PageSetup.Margins.Bottom = 0.4;
sheet2.PageSetup.Margins.Top = 0.4;
sheet2.PageSetup.Margins.Left = 0.2;
sheet2.PageSetup.Margins.Right = 0.2;
sheet2.PageSetup.CenterHorizontally = true;
//sheet.Row(4).Height = 31.5;
sheet2.Cell(4, 1).Value = "產品大類";
sheet2.Cell(4, 2).Value = "設備分類";
//sheet.Cell(4, 3).Style.Alignment.WrapText = true;
sheet2.Cell(4, 3).Value = "問題分類";
sheet2.Cell(4, 4).Value = "數量";
sheet2.Cell(4, 5).Value = "小計";
sheet2.Cell(4, 6).Value = "合計";
sheet2.Cell(4, 7).Value = "總計";
if (Data2.Count() == 0)
{
var row5 = sheet2.Row(5);
row5.Style.Font.FontSize = 14;
row5.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row5.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet2.Range(5, 1, 5, s2col).Merge(true);
sheet2.Cell(5, 1).Value = "查無資料";
s2row++;
}
else
{
int c = 1;
foreach (var o in Data2)
{
s2row++;
sheet2.Cell(s2row, 1).Value = o.ProductNote;
sheet2.Cell(s2row, 2).Value = o.ItemNote;
sheet2.Cell(s2row, 3).Value = o.QuestionNote;
sheet2.Cell(s2row, 4).Value = o.Quantity;
sheet2.Cell(s2row, 5).Value = o.Sum1;
sheet2.Cell(s2row, 6).Value = o.Sum2;
sheet2.Cell(s2row, 7).Value = o.Sum3;
c++;
}
var range3 = sheet2.Range(5, 1, s2row, s2col);
range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
range3.Style.Alignment.WrapText = true;
}
var s2range2 = sheet2.Range(4, 1, s2row, s2col);
s2range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
s2range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick;
s2range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
s2range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet2.Range(1, 1, 1, s2col).Merge(true);
sheet2.Range(2, 1, 2, s2col).Merge(true);
sheet2.Range(3, 1, 3, s2col).Merge(true);
//sheet.Cell(3, 1).Value = data.ProgramScript;
sheet2.Range(4, 1, 4, s2col).Style.Fill.BackgroundColor = XLColor.Gray;
sheet2.Cell(1, 1).Value = "依設定出來 (08:00-19:00 11HR * 22天)";
sheet2.Cell(2, 1).Value =
String.Format("{0:00}", model.CreateDTStart.Year) + "/" + String.Format("{0:00}", model.CreateDTStart.Month) + "/" + String.Format("{0:00}", model.CreateDTStart.Day)
+ "~" + String.Format("{0:00}", model.CreateDTEnd.Year) + "/" + String.Format("{0:00}", model.CreateDTEnd.Month) + "/" + String.Format("{0:00}", model.CreateDTEnd.Day)
+ "多媒體系統叫修狀況統計表";
sheet2.Cell(3, 1).Value = "(TOT:開單-解決)";
//str = "製表日期:" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
sheet2.Range(s2row + 1, 1, s2row + 1, s2col).Merge(true);
sheet2.Cell(s2row + 1, 1).Value = str;
sheet2.Row(s2row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
#endregion
#region 統計(工作表3)
var Data3 = reportRespositiory.getReport1S3(Data);
var sheet3 = workbook.Worksheets.Add("統計");
int s3row = 1;
int s3col = 1;
List<String> ProductList = Data.OrderBy(x=>x.ProductClass).Select(x => x.ProductNote).Distinct().ToList();
//double[] s2Colwidth = { 16, 16, 20, 6, 6, 6, 6 };
sheet3.Column(1).Width = 22;
for (int i = 1; i < ProductList.Count() + 1; i++)
{
sheet3.Column(i + 1).Width = 16;
s3col++;
}
//sheet3.Style.Font.FontName = "微軟正黑體";
//sheet3.Style.Font.FontSize = 12;
sheet3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
sheet3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet3.PageSetup.SetRowsToRepeatAtTop(1, 4);
sheet3.PageSetup.SetColumnsToRepeatAtLeft(1, 11);
sheet3.PageSetup.PageOrientation = XLPageOrientation.Portrait;
sheet3.PageSetup.PaperSize = XLPaperSize.A4Paper;
sheet3.PageSetup.Margins.Bottom = 0.4;
sheet3.PageSetup.Margins.Top = 0.4;
sheet3.PageSetup.Margins.Left = 0.2;
sheet3.PageSetup.Margins.Right = 0.2;
sheet3.PageSetup.CenterHorizontally = true;
sheet3.Cell(1, 1).Value = " 產品大類\r\n客戶簡稱";
sheet3.Cell(1, 1).Style.Alignment.WrapText = true;
for (int i = 1; i < ProductList.Count() + 1; i++)
{
sheet3.Cell(1, i + 1).Value = ProductList[i - 1].ToString() + "\r\n叫修";
sheet3.Cell(1, i + 1).Style.Alignment.WrapText = true;
}
if (Data3.Count() == 0)
{
var row6 = sheet3.Row(2);
row6.Style.Font.FontSize = 14;
row6.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
row6.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet3.Range(2, 1, 2, s3col).Merge(true);
sheet3.Cell(2, 1).Value = "查無資料";
s3row++;
}
else
{
int c = 1;
foreach (var o in Data3)
{
s3row++;
sheet3.Cell(s3row, 1).Value = o.CustomerSName;
for (int m = 0, n = 2; m < o.sum.Count(); m++, n++)
{
sheet3.Cell(s3row, n).Value = o.sum[m];
}
c++;
}
var range3 = sheet3.Range(2, 1, s3row, s3col);
range3.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range3.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
range3.Style.Alignment.WrapText = true;
}
var s3range2 = sheet3.Range(1, 1, s3row, s3col);
s3range2.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
s3range2.Style.Border.InsideBorder = XLBorderStyleValues.Thick;
s3range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
s3range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
sheet3.Cell(1, 1).Style.Border.DiagonalBorder = XLBorderStyleValues.Thin;
sheet3.Cell(1, 1).Style.Border.DiagonalDown = true;
sheet3.Cell(1, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
sheet3.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
sheet3.Range(s3row + 1, 1, s3row + 1, s3col).Merge(true);
sheet3.Cell(s3row + 1, 1).Value = str;
sheet3.Row(s3row + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
#endregion
return workbook;
}
public ActionResult Report1_Read([DataSourceRequest] DataSourceRequest request,
string CustomerRef, string CGroupID, DateTime CreateDTStart, DateTime CreateDTEnd, string ProductClassID,
string EventClass, int ArriveSel, int LeaveSel, int CloseSel, int CaseClass, int RType)
{
ReportSetViewModel v = new ReportSetViewModel();
v.ArriveSel = ArriveSel;
v.CaseClass = CaseClass;
v.CGroupID = CGroupID;
v.CloseSel = CloseSel;
//if (CreateDTEnd != null)
v.CreateDTEnd = CreateDTEnd;
//if (CreateDTStart != null)
v.CreateDTStart = CreateDTStart;
v.CustomerRef = CustomerRef;
v.EventClass = EventClass;
v.LeaveSel = LeaveSel;
if (ProductClassID == "")
v.ProductClassID = 0;
else
v.ProductClassID = Int32.Parse(ProductClassID);
v.RType = RType;
return Json(reportRespositiory.getReport1(v).ToDataSourceResult(request));
}