public async Task<string> MailNoPass(IList<string> CustId, string LoginId) { Log.Write("CustId:" + (CustId == null).ToString()); Log.WriteToFile(); AjaxResult r = new AjaxResult(); r.result = true; var db = new EnergyNetEntities(); var i = 0; foreach (var id in CustId) { #region Mail var t1 = await db.BuileBase.FindAsync(new object[] { id, 2015 }); var t2 = await db.Tb_Login.FindAsync(id); var t3 = await db.評核對應表2015V1.FindAsync(id); var t4 = await GetGradList(id, 2015); Log.Write("t1:" + (t1 == null).ToString()); Log.Write("t2:" + (t2 == null).ToString()); Log.Write("t3:" + (t3 == null).ToString()); Log.WriteToFile(); if (t1 != null && t2 != null && t3 != null && !string.IsNullOrEmpty(t1.ManagerMail)) { var file_path = Server.MapPath("~/MailTpl/StateB.html"); var txt = System.IO.File.ReadAllText(file_path); var mailtxt = txt; mailtxt = mailtxt.Replace("$user", t1.CustName); mailtxt = mailtxt.Replace("$checker", t3.checker); mailtxt = mailtxt.Replace("$Ext", t3.EXT); mailtxt = mailtxt.Replace("$CheckerEmail", t3.email); mailtxt = mailtxt.Replace("$account", t2.LoginID); mailtxt = mailtxt.Replace("$password", t2.LoginPW); if (t3.checker == "浦念理") { mailtxt = mailtxt.Replace("$contact2", ""); } else { mailtxt = mailtxt.Replace("$contact2", "浦念理 審查人員(分機734,Email:[email protected])<br />"); } if (t3.checker == "王秋霖") { mailtxt = mailtxt.Replace("$contact3", ""); } else { mailtxt = mailtxt.Replace("$contact3", "王秋霖 審查人員(分機741,Email:[email protected])<br />"); } var rpt_option = ReportList.GradOption(); var rpt_name = ReportList.GradRpt(); var t5 = JsonConvert.DeserializeObject<IList<RptGradHead>>(t4); string html = string.Empty; string tr = string.Empty; foreach (var get_rpt_item in t5) { var td_1 = string.Empty; var td_2 = string.Empty; var ul = string.Empty; var need_opt_item = get_rpt_item.data.Where(x => x.opt_id != "p00"); if (need_opt_item.Count() > 0) { var rptName = string.Empty; if (rpt_name.Any(x => x.Key == get_rpt_item.repord_id)) { rptName = rpt_name[get_rpt_item.repord_id]; td_1 += "<td style=\"vertical-align:top\">" + rptName + "</td>"; foreach (var opts in need_opt_item) { if (rpt_option.Any(x => x.Key == get_rpt_item.repord_id)) { var optobj = rpt_option[get_rpt_item.repord_id]; if (optobj.Any(x => x.Key == opts.opt_id)) { var opt_name = rpt_option[get_rpt_item.repord_id][opts.opt_id]; ul += "<li>" + opt_name + opts.mem + "</li>"; } } } td_2 = "<td><ul>" + ul + "</ul></td>"; tr += "<tr>" + td_1 + td_2 + "</tr>"; } } } html = "<table border=\"1\"><tr><td>申報表內容</td><td>審查說明</td></tr>" + tr + "</table>"; mailtxt = mailtxt.Replace("$list", html); var MailFrom = "*****@*****.**"; var MailTos = new string[] { t1.ManagerMail }; var MailBcc = new string[] { "*****@*****.**", "*****@*****.**", "*****@*****.**", t3.email }; //var MailTos = new string[] { "*****@*****.**" }; //var MailBcc = new string[] { }; var MailSub = "未達核備標準通知"; var sch = new Mail_Schedule(); sch.make_login_id = LoginId; sch.make_time = DateTime.Now; sch.isSend = false; sch.tos = string.Join(";", MailTos); sch.bcc = string.Join(";", MailBcc); sch.froms = MailFrom; sch.body = mailtxt; sch.subject = MailSub; db.Mail_Schedule.Add(sch); i++; } #endregion } await db.SaveChangesAsync(); r.result = true; r.message = i.ToString(); return defJSON(r); }
public FileResult xls_10_1_All(string CustId, int YY) { var db = new EnergyNetEntities(); var gid = Guid.NewGuid().ToString(); var path = Server.MapPath("~/zip/" + gid); string zipPath = path + gid + ".zip"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } var items = db.SaveEnergyResult_2015.Where(x => x.YY == YY && x.CustID == CustId).Select(x => new { x.CustID, x.YY, x.item }).ToList(); foreach (var item in items) { var outputStream = Stream_10_1(CustId, YY, item.item); string setFileName = "表十之一、104年節約能源改善方案具體成效分析表-項次[" + item.item + "]" + ".xls"; var xlsfile = path + "/" + setFileName; var f = new FileStream(xlsfile, FileMode.Create, FileAccess.Write); outputStream.WriteTo(f); f.Close(); outputStream.Close(); } ZipFile.CreateFromDirectory(path, zipPath); var filebyte = System.IO.File.ReadAllBytes(zipPath); Directory.Delete(path, true); System.IO.File.Delete(zipPath); string AllFileName = "表十之一、104年節約能源改善方案具體成效分析表(全)-" + Guid.NewGuid().ToString() + ".zip"; OutPutForIE8(filebyte, AllFileName); return File(filebyte, "application/zip", AllFileName); }
public FileResult xls_10_3(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r10_3.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("表十之三、104年執行計畫之平均年節電率"); var data = db.SaveEnergyAnalysis.Find(new object[] { CustId, YY }); if (data != null) { SetCellValue(getSheet, "C5", (double)data.ElecSave); SetCellValue(getSheet, "E7", (double)data.elec_amt_year_total); SetCellValue(getSheet, "E17", (double)data.elec_amt_use_total); SetCellValue(getSheet, "C18", (double)data.elec_save_rate + "%"); #region code if (data.elec_amt_no1 != null) { SetCellValue(getSheet, "C9", Y); SetCellValue(getSheet, "E9", (double)data.elec_amt_no1); } else { SetCellValue(getSheet, "C9", N); } if (data.elec_amt_no2 != null) { SetCellValue(getSheet, "C10", Y); SetCellValue(getSheet, "E10", (double)data.elec_amt_no2); } else { SetCellValue(getSheet, "C10", N); } if (data.elec_amt_no3 != null) { SetCellValue(getSheet, "C11", Y); SetCellValue(getSheet, "E11", (double)data.elec_amt_no3); } else { SetCellValue(getSheet, "C11", N); } if (data.elec_amt_no4 != null) { SetCellValue(getSheet, "C12", Y); SetCellValue(getSheet, "E12", (double)data.elec_amt_no4); } else { SetCellValue(getSheet, "C12", N); } if (data.elec_amt_no5 != null) { SetCellValue(getSheet, "C13", Y); SetCellValue(getSheet, "E13", (double)data.elec_amt_no5); } else { SetCellValue(getSheet, "C13", N); } if (data.elec_amt_no6 != null) { SetCellValue(getSheet, "C14", Y); SetCellValue(getSheet, "E14", (double)data.elec_amt_no6); } else { SetCellValue(getSheet, "C14", N); } if (data.elec_amt_no7 != null) { SetCellValue(getSheet, "C15", Y); SetCellValue(getSheet, "E15", (double)data.elec_amt_no7); } else { SetCellValue(getSheet, "C15", N); } if (data.elec_amt_no8 != null) { SetCellValue(getSheet, "C16", Y); SetCellValue(getSheet, "E16", (double)data.elec_amt_no8); } else { SetCellValue(getSheet, "C16", N); } #endregion } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十之三、104年執行計畫之平均年節電率-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
private MemoryStream Stream_10_1(string CustId, int YY, int item) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r10_1.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("表十之一、104年節約能源改善方案具體成效分析表"); var data = db.SaveEnergyResult_2015.Find(new object[] { CustId, YY, item }); if (data != null) { SetCellValue(getSheet, "B5", data.item); if (data.save_src == "1") { SetCellValue(getSheet, "C5", "跨年度"); } if (data.save_src == "0") { SetCellValue(getSheet, "C5", "當年度"); } //SetCellValue(getSheet, "D5", data.save_code); SetCellValue(getSheet, "D5", data.ECMCTypeCode + data.ECMCECID + data.ECMMECID); var eng_name = data.SaveEnergyResult_2015_Eng_Type.eng_name; SetCellValue(getSheet, "E5", eng_name); //能源種類 SetCellValue(getSheet, "F5", data.act_exec); //已執行之節約能源措施 SetCellValue(getSheet, "G5", "(1) 實施區域:" + data.act_area); SetCellValue(getSheet, "G17", "(2) 施行對象(設備或器具):" + data.act_target); SetCellValue(getSheet, "G28", "(3) 具體作法:" + data.act_text); SetCellValue(getSheet, "H5", data.chg_before); SetCellValue(getSheet, "I5", data.chg_after); SetCellValue(getSheet, "K6", data.y1 + "年" + data.m1 + "月~" + data.y2 + "年" + data.m2 + "月止"); SetCellValue(getSheet, "K11", data.fma_no21_1_B1 + "(能源單位)×" + data.fma_no21_1_B2 + "(%)×" + data.fma_no21_1_B3 + "=" + data.fma_no21_1_B4 + "(能源單位)"); SetCellValue(getSheet, "K12", data.fma_no21_2_B1 + "(能源單位)×" + data.fma_no21_2_B2 + "(%)×" + data.fma_no21_2_B3 + "=" + data.fma_no21_2_B4 + "(能源單位)"); SetCellValue(getSheet, "K15", data.fma_no22_C1 + "(kw)×" + data.fma_no22_C2 + "(台)×" + data.fma_no22_C3 + "(小時)×" + data.fma_no22_C4 + "(%)×" + data.fma_no22_C5 + "(%)=" + data.fma_no22_C6 + "(能源單位)"); SetCellValue(getSheet, "K17", data.fma_no22_D1 + "(kw)×" + data.fma_no22_D2 + "(台)×" + data.fma_no22_D3 + "(小時)×" + data.fma_no22_D4 + "(%)×" + data.fma_no22_D5 + "(%)=" + data.fma_no22_D6 + "(能源單位)"); SetCellValue(getSheet, "K19", "節能量:" + data.fma_no22_C6_Frm + "(能源單位)-" + data.fma_no22_D6_Frm + "(能源單位)=" + data.fma_no22_E + "(能源單位)"); SetCellValue(getSheet, "K21", data.fma_no23); SetCellValue(getSheet, "O22", data.fma_no3_F.ToString()); SetCellValue(getSheet, "K25", data.eff_no11_1_g1 + "(能源單位)×" + data.eff_no11_1_g2 + "(元/能源單位)=" + data.eff_no11_1_g3 + "(元)"); SetCellValue(getSheet, "K26", data.eff_no11_2_g1 + "(能源單位)×" + data.eff_no11_2_g2 + "(元/能源單位)=" + data.eff_no11_2_g3 + "(元)"); SetCellValue(getSheet, "K28", data.eff_other); //二、其他節能效益說明與計算 SetCellValue(getSheet, "O29", data.eff_save_money.ToString()); //三、本項措施總節能效益金額 SetCellValue(getSheet, "K32", data.inves_no1_1_text + ":" + data.inves_no1_1_i1 + "(kw/台或RT/台)×" + data.inves_no1_1_i2 + "(元/kw或元/RT)×" + data.inves_no1_1_i3 + "(台)=" + data.inves_no1_1_i4); SetCellValue(getSheet, "K33", data.inves_no1_2_text + ":" + data.inves_no1_2_i1 + "(kw/台或RT/台)×" + data.inves_no1_2_i2 + "(元/kw或元/RT)×" + data.inves_no1_2_i3 + "(台)=" + data.inves_no1_2_i4); SetCellValue(getSheet, "K36", data.inves_no2_1_text + ":" + data.inves_no2_1_i5 + "(元)"); SetCellValue(getSheet, "K37", data.inves_no2_2_text + ":" + data.inves_no2_2_i6 + "(元)"); SetCellValue(getSheet, "O38", data.inves_total + "(元)。(註8)"); } wbXLS.Write(outputStream); outputStream.Position = 0; return outputStream; } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public FileResult GetGridHisForJerry(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var sql = @"Select A.CustID,D.CustName,A.DeliverDate,M.FixDate,M.CheckDate,P.LoginName As CheckerNme,A.GreenCheckState,C.repord_id,C.opt_id,C.mem,M.examine_report_record_id from ExamineReport_Master As A Inner Join ExamineReport_Record As M On A.CustID=M.CustID And A.YY=M.YY Inner Join Grad As B On M.examine_report_record_id = B.examine_report_record_id Inner Join Graid_Detail As C On B.grad_id = C.graid_id Inner Join BuileBase As D On A.YY =D.YY And A.CustID = D.CustID Left Join Tb_Login As P On A.CheckerId = P.loginid Left Join Tb_Login As T On M.AtuCheckerId = T.loginid Where M.CheckDate Is Not Null And A.CustID !='AAA' And C.opt_id!='p00' Order by A.CustID,M.examine_report_record_id,C.repord_id,C.opt_id"; var items = db.Database.SqlQuery<GetGridHisForJerryModal>(sql); #endregion var rpt_option = ReportList.GradOption(); var rpt_name = ReportList.GradRpt(); IList<SetGridHisForJerryModal> mdl = new List<SetGridHisForJerryModal>(); var gp1 = items.GroupBy(x => x.CustID); foreach (var _gp1 in gp1) { var gp2 = _gp1.GroupBy(x => x.examine_report_record_id); var max_id = gp2.Max(x => x.Key); foreach (var _gp2 in gp2) { if (_gp2.Key == max_id) { var gp3 = _gp2.GroupBy(x => new { x.repord_id, x.CustID, x.CustName, x.DeliverDate, x.FixDate, x.CheckDate, x.GreenCheckState, x.CheckerNme }); foreach (var _gp3 in gp3) { #region MyRegion var md = new SetGridHisForJerryModal(); md.CustID = _gp3.Key.CustID; md.CustName = _gp3.Key.CustName; md.DeliverDate = _gp3.Key.DeliverDate; md.FixDate = _gp3.Key.FixDate; md.CheckDate = _gp3.Key.CheckDate; md.GreenCheckState = _gp3.Key.GreenCheckState; md.repord_id = _gp3.Key.repord_id; md.repord_name = rpt_name[_gp3.Key.repord_id]; md.CheckerNme = _gp3.Key.CheckerNme; var options = _gp3.Select(x => x.opt_id); IList<string> option_name = new List<string>(); foreach (var option in options) { if (rpt_option.Any(x => x.Key == _gp3.Key.repord_id)) { var get_rpt_option_name = rpt_option[_gp3.Key.repord_id]; var get_name = get_rpt_option_name.Any(x => x.Key == option) ? get_rpt_option_name[option] : ""; if (option == "pth") get_name = "其他:" + _gp3.FirstOrDefault(x => x.opt_id == "pth").mem; option_name.Add(get_name); } } md.option_context = string.Join("、", option_name); mdl.Add(md); //Console.WriteLine(_gp3.Key.CustID); #endregion } } } } FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/審核情形view.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["工作表1"]; int i = 3; int n = 8; var pos = new Dictionary<string, int> { { "r01",n+0}, { "r02",n+1}, { "r03",n+2}, { "r41",n+3}, { "r42",n+4}, { "r51",n+5}, { "r52",n+6}, { "r53",n+7}, { "r54",n+8}, { "r55",n+9}, { "r06",n+10}, { "r07",n+11}, { "r08",n+12}, { "r91",n+13}, { "r92",n+14}, { "r93",n+15}, { "r94",n+16}, { "r95",n+17}, { "rA1",n+18}, { "rA3",n+19}, { "rA4",n+20}, { "rB1",n+21}, { "rB3",n+22}, { "rB4",n+23} }; var now_repord_id = string.Empty; var now_custid = string.Empty; foreach (var item in mdl) { if (item.CustID != now_custid) { i++; } now_custid = item.CustID; sheet.Cells[i + 1, 1].Value = item.CustID; sheet.Cells[i + 1, 2].Value = item.CustName; sheet.Cells[i + 1, 3].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 4].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 5].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 6].Value = item.CheckerNme; sheet.Cells[i + 1, 7].Value = fam_GreenState(item.GreenCheckState); sheet.Cells[i + 1, pos[item.repord_id]].Value = item.option_context; //i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審查歷程紀錄表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public FileResult xls_4_2(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); fileStream = new FileStream(Server.MapPath("~/xls/r4_2.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("能源管理政策推動情形"); var data = db.EnergyPushState.Find(new object[] { CustId, YY }); Log.Write("Start Report", CustId, YY.ToString()); if (data != null) { #region code if (data.push_no_1 == "Y") { SetCellValue(getSheet, "C4", Y + "是"); SetCellValue(getSheet, "C6", N + "否"); var s = string.Empty; if (data.push_no_1_year != null) { s = "通過年份:民國" + data.push_no_1_year + "年"; } if (data.push_no_1_near_year != null) { s += ";最近一次通過民國" + data.push_no_1_near_year + "年。"; } SetCellValue(getSheet, "C5", s); } if (data.push_no_1 == "N") { SetCellValue(getSheet, "C4", N + "是"); SetCellValue(getSheet, "C6", Y + "否"); } #endregion #region code if (data.push_no_2 == "Y") { SetCellValue(getSheet, "C7", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_2 == "N") { SetCellValue(getSheet, "C7", N + "已推動" + " " + Y + "規劃推動"); } if (data.push_no_3 == "Y") { SetCellValue(getSheet, "C8", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_3 == "N") { SetCellValue(getSheet, "C8", N + "已推動" + " " + Y + "規劃推動"); } if (data.push_no_4 == "Y") { SetCellValue(getSheet, "C9", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_4 == "N") { SetCellValue(getSheet, "C9", N + "已推動" + " " + Y + "規劃推動"); } if (data.push_no_5 == "Y") { SetCellValue(getSheet, "C10", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_5 == "N") { SetCellValue(getSheet, "C10", N + "已推動" + " " + Y + "規劃推動"); } //SetCellValue(getSheet, "B10", "其他:" + data.push_no_10_memo); if (data.push_no_6 == "Y") { SetCellValue(getSheet, "C11", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_6 == "N") { SetCellValue(getSheet, "C11", N + "已推動" + " " + Y + "規劃推動"); } //SetCellValue(getSheet, "B11", "其他:" + data.push_no_11_memo); if (data.push_no_7 == "Y") { SetCellValue(getSheet, "C12", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_7 == "N") { SetCellValue(getSheet, "C12", N + "已推動" + " " + Y + "規劃推動"); } //SetCellValue(getSheet, "B12", "其他:" + data.push_no_12_memo); if (data.push_no_8 == "Y") { SetCellValue(getSheet, "C13", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_8 == "N") { SetCellValue(getSheet, "C13", N + "已推動" + " " + Y + "規劃推動"); } //SetCellValue(getSheet, "B13", "其他:" + data.push_no_13_memo); if (data.push_no_9 == "Y") { SetCellValue(getSheet, "C14", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_9 == "N") { SetCellValue(getSheet, "C14", N + "已推動" + " " + Y + "規劃推動"); } SetCellValue(getSheet, "B15", "其他:" + data.push_no_10_memo); if (data.push_no_10 == "Y") { SetCellValue(getSheet, "C15", Y + "已推動" + " " + N + "規劃推動"); } if (data.push_no_10 == "N") { SetCellValue(getSheet, "C15", N + "已推動" + " " + Y + "規劃推動"); } if (data.push_no_11 == "Y") { SetCellValue(getSheet, "C16", Y + "已推動" + " " + N + "規劃推動"); SetCellValue(getSheet, "B16", "其他:" + data.push_no_11_memo); } if (data.push_no_11 == "N") { SetCellValue(getSheet, "C16", N + "已推動" + " " + Y + "規劃推動"); SetCellValue(getSheet, "B16", "其他:" + data.push_no_11_memo); } if (data.push_no_12 == "Y") { SetCellValue(getSheet, "C17", Y + "已推動" + " " + N + "規劃推動"); SetCellValue(getSheet, "B17", "其他:" + data.push_no_12_memo); } if (data.push_no_12 == "N") { SetCellValue(getSheet, "C17", N + "已推動" + " " + Y + "規劃推動"); SetCellValue(getSheet, "B17", "其他:" + data.push_no_12_memo); } if (data.push_no_13 == "Y") { SetCellValue(getSheet, "C18", Y + "已推動" + " " + N + "規劃推動"); SetCellValue(getSheet, "B18", "其他:" + data.push_no_13_memo); } if (data.push_no_13 == "N") { SetCellValue(getSheet, "C18", N + "已推動" + " " + Y + "規劃推動"); SetCellValue(getSheet, "B18", "其他:" + data.push_no_13_memo); } if (data.push_no_14 == "Y") { SetCellValue(getSheet, "C19", Y + "已推動" + " " + N + "規劃推動"); SetCellValue(getSheet, "B19", "其他:" + data.push_no_14_memo); } if (data.push_no_14 == "N") { SetCellValue(getSheet, "C19", N + "已推動" + " " + Y + "規劃推動"); SetCellValue(getSheet, "B19", "其他:" + data.push_no_14_memo); } if (data.push_no_15 == "Y") { SetCellValue(getSheet, "C120", Y + "已推動" + " " + N + "規劃推動"); SetCellValue(getSheet, "B20", "其他:" + data.push_no_15_memo); } if (data.push_no_15 == "N") { SetCellValue(getSheet, "C20", N + "已推動" + " " + Y + "規劃推動"); SetCellValue(getSheet, "B20", "其他:" + data.push_no_15_memo); } #endregion } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表四之二、能源管理政策推動情形-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public FileResult GetExam(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var predicate = PredicateBuilder.True<ExamineReport_Master>(); predicate = predicate.And(x => x.YY == 2015); predicate = predicate.And(x => x.IsReject == false); if (param.CustId != null) predicate = predicate.And(x => x.CustID == param.CustId); if (param.CheckerId != null) predicate = predicate.And(x => x.CheckerId == param.CheckerId); if (param.AtuCheckerId != null) predicate = predicate.And(x => x.AtuCheckerId == param.AtuCheckerId); if (param.DeliverDate_Start != null) predicate = predicate.And(x => x.DeliverDate >= param.DeliverDate_Start); if (param.DeliverDate_End != null) predicate = predicate.And(x => x.DeliverDate <= param.DeliverDate_End); if (param.FixDate_Start != null) predicate = predicate.And(x => x.FixDate >= param.FixDate_Start); if (param.FixDate_End != null) predicate = predicate.And(x => x.FixDate <= param.FixDate_End); if (param.GreenCheckState != null) predicate = predicate.And(x => x.GreenCheckState == param.GreenCheckState); if (param.EconCheckState != null) predicate = predicate.And(x => x.EconCheckState == param.EconCheckState); var items = db.ExamineReport_Master.AsExpandable() .Where(predicate) .OrderByDescending(x => x.DeliverDate) .Select(x => new { x.YY, x.CustID, x.DeliverDate, x.FixDate, x.GreenCheckState, x.EconCheckState, x.CheckerId, CheckerName = x.Tb_Login_CheckerId.LoginName, x.CheckDate, x.CheckCount, x.InitPassDate, x.EconPassDate, x.IsReject, x.AtuCheckerId, CustName = x.BuileBase.CustName, AtuName = x.Tb_Login_AtuCheckerId.LoginName }).ToList(); #endregion FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/ExamQuery.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["Query"]; int i = 1; foreach (var item in items) { sheet.Cells[i + 1, 1].Value = i; sheet.Cells[i + 1, 2].Value = item.CustID; sheet.Cells[i + 1, 3].Value = item.CustName; sheet.Cells[i + 1, 4].Value = item.CheckerName; sheet.Cells[i + 1, 5].Value = item.AtuName; sheet.Cells[i + 1, 6].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 7].Value = fam_GreenCheckState(item.GreenCheckState); sheet.Cells[i + 1, 8].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 9].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 10].Value = item.CheckCount; sheet.Cells[i + 1, 11].Value = fma_date(item.InitPassDate); sheet.Cells[i + 1, 12].Value = fam_EconCheckState(item.EconCheckState); sheet.Cells[i + 1, 13].Value = fma_date(item.EconPassDate); i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審核-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public async Task<string> GetFloorElecQAll(string CustId, int YY) { var db = new EnergyNetEntities(); var item = await db.FloorElecQ .Where(x => x.CustID == CustId && x.YY == YY) .OrderBy(x => x.QNO).ToListAsync(); return defJSONNull(item); }
public string GetAdminSelectUser() { var db = new EnergyNetEntities(); IEnumerable<CUS> query = null; var sql = @"Select distinct CustID,CustName=(select top 1 CustName from BuileBase B where B.CustID=BuileBase.CustID order by yy desc ) from BuileBase where IsControl = 'Y' order by CustID"; query = db.Database.SqlQuery<CUS>(sql); return defJSON(query); }
public async Task<string> UptFloorElecItem(IEnumerable<FloorElec> mds) { var db = new EnergyNetEntities(); AjaxResult r = new AjaxResult(); try { string CustID = string.Empty; int YY = 0; foreach (var md in mds) { var item = await db.FloorElec.FindAsync(md.CustID, md.YY, md.month); item.p_floor = md.p_floor; item.n_floor = md.n_floor; item.p_eui = md.p_eui; item.n_eui = md.n_eui; if (CustID == string.Empty) { CustID = md.CustID; YY = md.YY; var pass_state = StateCheck(CustID); if (pass_state == 1) { r.result = false; r.message = "初審已通過不可更新!"; return defJSON(r); } } } var g_item = mds.GroupBy(x => (x.month - 1) / 3).Select( y => new { qno = y.Key, sum_p_eui_amt = y.Sum(x => x.p_eui_amt), sum_n_eui_amt = y.Sum(x => x.n_eui_amt), avg_p_floor = y.Average(x => x.p_floor), avg_n_floor = y.Average(x => x.n_floor) }); var q_item_data = await db.FloorElecQ.Where(x => x.CustID == CustID && x.YY == YY).OrderBy(x => x.QNO).ToListAsync(); foreach (var item in g_item) { var q_p_value = item.avg_p_floor == 0 ? 0 : item.sum_p_eui_amt / item.avg_p_floor; var q_n_value = item.sum_n_eui_amt / item.avg_n_floor; var q_item = q_item_data.Where(x => x.QNO == (item.qno + 1)).FirstOrDefault(); if (q_p_value == 0) { q_item.diff_result = 0; } else { q_item.diff_result = ((q_n_value - q_p_value) / q_p_value) * 100; } } await db.SaveChangesAsync(); RecordReportList(CustID, YY, "J54"); r.result = true; return defJSON(r); } catch (Exception ex) { r.result = false; r.message = ex.Message; return defJSON(r); } finally { db.Dispose(); } }
public async Task<string> GetFloorElecQ(string CustId, int YY, int QNo) { var db = new EnergyNetEntities(); var item = await db.FloorElecQ .FindAsync(CustId, YY, QNo); var s = defJSONNull(item); return s; }
//經濟部核備通過發Mail public string EncoPassSendMail() { var db = new EnergyNetEntities(); var sql = @"Select A.CustName,A.ManagerMail From BuileBase As A Inner Join EncoPass_2015 As B On A.CustID=B.custid And A.YY=B.yy Where A.YY=2015;"; var items = db.Database.SqlQuery<EncoPassMail>(sql); //var mailBody = @""; return defJSONNull(items); }
public async Task<string> PostMailScheduleIsSend(int id) { AjaxResult r = new AjaxResult(); var db = new EnergyNetEntities(); var item = await db.Mail_Schedule.FindAsync(id); if (item != null) { item.isSend = true; item.sendtime = DateTime.Now; await db.SaveChangesAsync(); r.result = true; } return defJSONNull(r); }
public async Task<string> GetMailSchedule() { var db = new EnergyNetEntities(); var item = await db.Mail_Schedule.Where(x => x.isSend == false).ToListAsync(); return defJSONNull(item); }
public FileResult xls_11_4(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r11_4.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); HSSFSheet getSheet = (HSSFSheet)wbXLS.GetSheet("表十一之四、105年執行計畫年度節電率未達1%說明"); var data = db.PlanNotGoal.Find(new object[] { CustId, YY }); var data_detail = db.PlanNotGoal_No4.Where(x => x.CustID == CustId && x.YY == YY).OrderByDescending(x => x.item); if (data != null) { SetCellValue(getSheet, "E4", "說明:" + data.no0_text); SetCellValue(getSheet, "E5", "104年啟用之新建築或新設備之用電量:" + data.no0_year_amt + "度"); SetCellValue(getSheet, "D6", "說明:" + data.no1_text); SetCellValue(getSheet, "D7", "說明:" + data.no2_text); SetCellValue(getSheet, "D9", data.no3_fam_text); //SetCellValue(getSheet, "F12", data.no3_fam_select); if (data.no3_fam_select == "1") { SetCellValue(getSheet, "F12", "來客數"); SetCellValue(getSheet, "E16", "來客數"); } if (data.no3_fam_select == "2") { SetCellValue(getSheet, "F12", "貨櫃數"); SetCellValue(getSheet, "E16", "貨櫃數"); } if (data.no3_fam_select == "3") { SetCellValue(getSheet, "F12", "營業額"); SetCellValue(getSheet, "E16", "營業額"); } if (data.no3_fam_select == "4") { SetCellValue(getSheet, "F12", "樓地版面積"); SetCellValue(getSheet, "E16", "樓地版面積"); } if (data.no3_fam_select == "5") { SetCellValue(getSheet, "F12", "其他:" + data.no3_fam_select_5_memo); SetCellValue(getSheet, "E16", "其他"); } SetCellValue(getSheet, "F15", data.elec_use_100.ToString()); SetCellValue(getSheet, "G15", data.elec_use_101.ToString()); SetCellValue(getSheet, "H15", data.elec_use_102.ToString()); SetCellValue(getSheet, "I15", data.elec_use_103.ToString()); SetCellValue(getSheet, "J15", data.elec_use_104.ToString()); SetCellValue(getSheet, "F16", data.sour_use_100.ToString()); SetCellValue(getSheet, "G16", data.sour_use_101.ToString()); SetCellValue(getSheet, "H16", data.sour_use_102.ToString()); SetCellValue(getSheet, "I16", data.sour_use_103.ToString()); SetCellValue(getSheet, "J16", data.sour_use_104.ToString()); SetCellValue(getSheet, "F17", data.no3_eff_100.ToString()); SetCellValue(getSheet, "G17", data.no3_eff_101.ToString()); SetCellValue(getSheet, "H17", data.no3_eff_102.ToString()); SetCellValue(getSheet, "I17", data.no3_eff_103.ToString()); SetCellValue(getSheet, "J17", data.no3_eff_104.ToString()); SetCellValue(getSheet, "E18", "電力使用效率指標(%):" + data.no3_eff_rate + "%"); SetCellValue(getSheet, "D21", data.no3_save_text); SetCellValue(getSheet, "F24", data.no3_save_amt_100.ToString()); SetCellValue(getSheet, "G24", data.no3_save_amt_101.ToString()); SetCellValue(getSheet, "H24", data.no3_save_amt_102.ToString()); SetCellValue(getSheet, "I24", data.no3_save_amt_103.ToString()); SetCellValue(getSheet, "J24", data.no3_save_amt_104.ToString()); SetCellValue(getSheet, "F25", data.no3_use_amt_100.ToString()); SetCellValue(getSheet, "G25", data.no3_use_amt_101.ToString()); SetCellValue(getSheet, "H25", data.no3_use_amt_102.ToString()); SetCellValue(getSheet, "I25", data.no3_use_amt_103.ToString()); SetCellValue(getSheet, "J25", data.no3_use_amt_104.ToString()); SetCellValue(getSheet, "E26", "平均年節電率(%):" + data.no3_save_rate.ToString() + "%"); SetCellValue(getSheet, "D29", data.no5_text_1); SetCellValue(getSheet, "F29", data.no5_text_2); SetCellValue(getSheet, "H29", data.no5_text_3); SetCellValue(getSheet, "J29", data.no5_text_4); SetCellValue(getSheet, "D30", data.no6_text); SetCellValue(getSheet, "D31", data.no7_text); } //int pos = 25; //int i = 7; var ct = data_detail.Count(); for (int i = 0; i < ct; i++) { InsertRows(getSheet, 27, 1); var item = data_detail.Skip(i).FirstOrDefault(); SetCellValue(getSheet, "D28", item.item); SetCellValue(getSheet, "E28", item.y1 + "年~" + item.y2 + "年"); SetCellValue(getSheet, "F28", item.target); SetCellValue(getSheet, "G28", item.doing); SetCellValue(getSheet, "H28", item.fam_text); SetCellValue(getSheet, "I28", item.save_amt.ToString()); SetCellValue(getSheet, "J28", item.save_money.ToString()); SetCellValue(getSheet, "K28", item.invest.ToString()); } getSheet.AddMergedRegion(new CellRangeAddress(26, 26 + ct, 1, 1)); //合併儲存格 getSheet.AddMergedRegion(new CellRangeAddress(26, 26 + ct, 2, 2)); //合併儲存格 wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十一之四、105年執行計畫年度節電率未達1%說明-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { var m = getErrorMessage(ex); Log.Write(m); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public async Task<string> GetFloorElecYear(string CustId, int YY) { var db = new EnergyNetEntities(); var item = await db.FloorElecYear.FindAsync(new object[] { CustId, YY }); return defJSONNull(item); }
public FileResult xls_11_5(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r11_5.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); HSSFSheet getSheet = (HSSFSheet)wbXLS.GetSheet("表十一之五、歷年預計執行之節電計畫年度節電率總表"); var data = db.PlanYearPlane_2015.Find(new object[] { CustId, YY }); if (data != null) { SetCellValue(getSheet, "D4", data.S_104.ToString()); SetCellValue(getSheet, "D5", data.C_104.ToString()); SetCellValue(getSheet, "D6", data.R_104.ToString()); SetCellValue(getSheet, "D7", data.I_104.ToString()); SetCellValue(getSheet, "F4", data.S_105.ToString()); SetCellValue(getSheet, "F5", data.C_105.ToString()); SetCellValue(getSheet, "F6", data.R_105.ToString()); SetCellValue(getSheet, "F7", data.I_105.ToString()); SetCellValue(getSheet, "H4", data.S_106.ToString()); SetCellValue(getSheet, "H5", data.C_106.ToString()); SetCellValue(getSheet, "H6", data.R_106.ToString()); SetCellValue(getSheet, "H7", data.I_106.ToString()); SetCellValue(getSheet, "J4", data.S_107.ToString()); SetCellValue(getSheet, "J5", data.C_107.ToString()); SetCellValue(getSheet, "J6", data.R_107.ToString()); SetCellValue(getSheet, "J7", data.I_107.ToString()); SetCellValue(getSheet, "L4", data.S_108.ToString()); SetCellValue(getSheet, "L5", data.C_108.ToString()); SetCellValue(getSheet, "L6", data.R_108.ToString()); SetCellValue(getSheet, "L7", data.I_108.ToString()); } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十一之五、歷年預計執行之節電計畫年度節電率總表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public async Task<string> UptFloorElecYear(UptUptFloorElecYearParm obj) { var db = new EnergyNetEntities(); AjaxResult r = new AjaxResult(); var pass_state = StateCheck(obj.CustId); if (pass_state == 1) { r.result = false; r.message = "初審已通過不可更新!"; return defJSON(r); } try { var e = await db.FloorElecYear .AnyAsync(x => x.CustID == obj.CustId && x.YY == obj.YY); if (e) { var item = await db.FloorElecYear.FindAsync(obj.CustId, obj.YY); var md = obj.md; item.d_q_no1 = md.d_q_no1; item.d_q_no2 = md.d_q_no2; item.d_q_no3 = md.d_q_no3; item.d_q_no4 = md.d_q_no4; item.d_q_no5 = md.d_q_no5; item.d_q_no6 = md.d_q_no6; item.d_q_no7 = md.d_q_no7; item.d_q_no7_memo = md.d_q_no7_memo; item.i_q_no1 = md.i_q_no1; item.i_q_no2 = md.i_q_no2; item.i_q_no3 = md.i_q_no3; item.i_q_no4 = md.i_q_no4; item.i_q_no5 = md.i_q_no5; item.i_q_no6 = md.i_q_no6; item.i_q_no7 = md.i_q_no7; item.i_q_no7_memo = md.i_q_no7_memo; item.reason = md.reason; item.i_q_no8 = md.i_q_no8; item.i_q_no9 = md.i_q_no9; } else { //db.FloorElecQ.Add(obj.md); } await db.SaveChangesAsync(); r.result = true; return defJSON(r); } catch (Exception ex) { r.result = false; r.message = ex.Message; return defJSON(r); } finally { db.Dispose(); } }
public FileResult xls_5_5(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r5_5.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("5.5、熱能績效自我評比表"); SetCellValue(getSheet, "B4", (CYY - 1) + "年室內樓地板面積"); SetCellValue(getSheet, "C4", CYY + "年室內樓地板面積"); SetCellValue(getSheet, "D4", (CYY - 1) + "年EUI"); SetCellValue(getSheet, "E4", CYY + "年EUI"); var m_data = db.FloorHeat.Where(x => x.CustID == CustId && x.YY == YY); int i = 6; foreach (var data in m_data) { SetCellValue(getSheet, "B" + i, (double)data.p_floor); SetCellValue(getSheet, "C" + i, (double)data.n_floor); SetCellValue(getSheet, "D" + i, data.p_eui == null ? 0 : (double)data.p_eui); SetCellValue(getSheet, "E" + i, data.n_eui == null ? 0 : (double)data.n_eui); i++; } var q_data = db.FloorHeatQ.Where(x => x.CustID == CustId && x.YY == YY); int j = 6; foreach (var data in q_data) { string r = string.Empty; IList<string> reason = new List<string>(); if (data.diff_result > 0) { r = "增加" + data.diff_result + "%"; #region code select if (data.i_q_no1 == true) { reason.Add(Y + "營運未滿一年"); } if (data.i_q_no2 == true) { reason.Add(Y + "營運時間增加"); } if (data.i_q_no3 == true) { reason.Add(Y + "設備增加"); } if (data.i_q_no4 == true) { reason.Add(Y + "設備運轉時數增加"); } if (data.i_q_no5 == true) { reason.Add(Y + "營運規模擴大"); } if (data.i_q_no6 == true) { reason.Add(Y + "未定期保養"); } if (data.i_q_no7 == true) { reason.Add(Y + "其他:" + data.i_q_no7_memo); } #endregion } else { decimal v = data.diff_result == null ? 0 : (decimal)data.diff_result; r = "減少" + Math.Abs(v) + "%"; #region code select if (data.d_q_no1 == true) { reason.Add(Y + "營運時間減少"); } if (data.d_q_no2 == true) { reason.Add(Y + "設備減少"); } if (data.d_q_no3 == true) { reason.Add(Y + "設備運轉時數減少"); } if (data.d_q_no4 == true) { reason.Add(Y + "營運規模縮小"); } if (data.d_q_no5 == true) { reason.Add(Y + "改採熱泵"); } if (data.d_q_no6 == true) { reason.Add(Y + "定期保養"); } if (data.d_q_no7 == true) { reason.Add(Y + "其他:" + data.d_q_no7_memo); } #endregion } SetCellValue(getSheet, "F" + j, r); SetCellValue(getSheet, "H" + j, data.reason); SetCellValue(getSheet, "G" + j, string.Join("\r\n", reason)); j = j + 3; } var y_data = db.FloorHeatYear.Find(new object[] { CustId, YY }); if (y_data != null) { string r = string.Empty; IList<string> reason = new List<string>(); if (y_data.diff_result > 0) { r = "增加" + y_data.diff_result + "%"; #region code select if (y_data.i_q_no1 == true) { reason.Add(Y + "營運未滿一年"); } if (y_data.i_q_no2 == true) { reason.Add(Y + "營運時間增加"); } if (y_data.i_q_no3 == true) { reason.Add(Y + "設備增加"); } if (y_data.i_q_no4 == true) { reason.Add(Y + "設備運轉時數增加"); } if (y_data.i_q_no5 == true) { reason.Add(Y + "營運規模擴大"); } if (y_data.i_q_no6 == true) { reason.Add(Y + "未定期保養"); } if (y_data.i_q_no7 == true) { reason.Add(Y + "其他:" + y_data.i_q_no7_memo); } #endregion } else { decimal v = y_data.diff_result == null ? 0 : (decimal)y_data.diff_result; r = "減少" + Math.Abs(v) + "%"; #region code select if (y_data.d_q_no1 == true) { reason.Add(Y + "營運時間減少"); } if (y_data.d_q_no2 == true) { reason.Add(Y + "設備減少"); } if (y_data.d_q_no3 == true) { reason.Add(Y + "設備運轉時數減少"); } if (y_data.d_q_no4 == true) { reason.Add(Y + "營運規模縮小"); } if (y_data.d_q_no5 == true) { reason.Add(Y + "改採熱泵"); } if (y_data.d_q_no6 == true) { reason.Add(Y + "定期保養"); } if (y_data.d_q_no7 == true) { reason.Add(Y + "其他:" + y_data.d_q_no7_memo); } #endregion } SetCellValue(getSheet, "B18", (double)y_data.p_floor); SetCellValue(getSheet, "C18", (double)y_data.n_floor); SetCellValue(getSheet, "D18", (double)y_data.p_eui); SetCellValue(getSheet, "E18", (double)y_data.n_eui); SetCellValue(getSheet, "F18", r); SetCellValue(getSheet, "G18", string.Join("\r\n", reason)); SetCellValue(getSheet, "H18", y_data.reason); } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表五之五、熱能績效自我評比表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
private async Task<IEnumerable<ElecAmt>> GetCalDefElec(string CustId, int YY) { var db = new EnergyNetEntities(); var f = await db.ElecEnergyUse .Where(x => x.CustID == CustId && x.YY == YY) .GroupBy(x => new { x.MM }) .Select(y => new ElecAmt() { MM = y.Key.MM, Total = y.Sum(x => x.PeakElec + x.OffPeakElec + x.HalfPeakElec + x.SatHalfPeakElec) }) .ToListAsync(); return f; }
public FileResult xls_9_5(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r9_5.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("表九之五、重大使用能源設備登錄表"); ISheet smpSheet = wbXLS.GetSheet("Sample"); getSheet.PrintSetup.FitHeight = 999; var item_total = db.EquipUseState.Where(x => x.CustID == CustId && x.YY == YY); decimal r = (decimal)item_total.Count() / 10; //每頁10 計算分頁 var row = Math.Ceiling(r); //Excel分頁範例 //CopyRowArea(wbXLS, smpSheet, getSheet, 0, 0, 25); //getSheet.GetRow(24 * 1 + 0).HeightInPoints = 12 * getSheet.DefaultRowHeight / 20; //getSheet.SetRowBreak(24*1+0); //CopyRowArea(wbXLS, smpSheet, getSheet, 0, 25, 25); //getSheet.GetRow(24 * 2 + 1).HeightInPoints = 12 * getSheet.DefaultRowHeight / 20; //getSheet.SetRowBreak(24 * 2 + 1); //CopyRowArea(wbXLS, smpSheet, getSheet, 0, 50, 25); //getSheet.GetRow(24 * 3 + 2).HeightInPoints = 12 * getSheet.DefaultRowHeight / 20; //getSheet.SetRowBreak(24 * 3 + 2); for (var i = 1; i <= row; i++) { #region Clear for (int k = 5; k <= 23; k = k + 2) { SetCellValue(smpSheet, "B" + k, ""); SetCellValue(smpSheet, "C" + k, ""); SetCellValue(smpSheet, "D" + k, N + "依標準程序規定操作"); SetCellValue(smpSheet, "D" + (k + 1), N + "依使用者經驗操作"); SetCellValue(smpSheet, "E" + k, N + "定期實施維護保養"); SetCellValue(smpSheet, "E" + (k + 1), N + "不定期實施維護保養"); } #endregion int j = 5; var p_item = item_total.OrderBy(x => x.PNO).Skip((i - 1) * 10).Take(10); foreach (var item in p_item) { SetCellValue(smpSheet, "B" + j, item.system_name); SetCellValue(smpSheet, "C" + j, item.plant_name); if (item.operator_state == 1) { SetCellValue(smpSheet, "D" + j, Y + "依標準程序規定操作"); SetCellValue(smpSheet, "D" + (j + 1), N + "依使用者經驗操作"); } if (item.operator_state == 0) { SetCellValue(smpSheet, "D" + j, N + "依標準程序規定操作"); SetCellValue(smpSheet, "D" + (j + 1), Y + "依使用者經驗操作"); } if (item.maintain_state == 1) { SetCellValue(smpSheet, "E" + j, Y + "定期實施維護保養"); SetCellValue(smpSheet, "E" + (j + 1), N + "不定期實施維護保養"); } if (item.maintain_state == 0) { SetCellValue(smpSheet, "E" + j, N + "定期實施維護保養"); SetCellValue(smpSheet, "E" + (j + 1), Y + "不定期實施維護保養"); } j = j + 2; } CopyRowArea(wbXLS, smpSheet, getSheet, 0, (i - 1) * 25, 25); getSheet.GetRow(24 * i + (i - 1)).HeightInPoints = 12 * getSheet.DefaultRowHeight / 20; getSheet.SetRowBreak(25 * i - 1); } getSheet.SetColumnWidth(0, 5 * 256); getSheet.SetColumnWidth(1, 12 * 256); getSheet.SetColumnWidth(2, 12 * 256); getSheet.SetColumnWidth(3, 25 * 256); getSheet.SetColumnWidth(4, 25 * 256); wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表九之五、重大使用能源設備登錄表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public async Task<string> CheckFloorElecQY(string CustId, int YY) { var db = new EnergyNetEntities(); var q = await db.FloorElecQ .Where(x => x.i_q_no1 == null && x.YY == YY && x.CustID == CustId && (x.diff_result >= 10 || x.diff_result <= -10)) .ToListAsync(); var y = await db.FloorElecYear .Where(x => x.i_q_no1 == null && x.YY == YY && x.CustID == CustId && (x.diff_result >= 10 || x.diff_result <= -10)) .FirstOrDefaultAsync(); return defJSON(new CheckFloorElec { q = q, y = y }); }
public FileResult GetGridHis(GetExamineReport_MasterParam param) { var db = new EnergyNetEntities(); #region Query Data var sql = @"Select A.YY,A.CustID,D.CustName,A.DeliverDate,M.FixDate,M.CheckDate,M.CheckState,C.repord_id,C.opt_id,C.mem,M.AtuCheckerId ,P.LoginName As CheckerNme,T.LoginName As ActuName from ExamineReport_Master As A Inner Join ExamineReport_Record As M On A.CustID=M.CustID And A.YY=M.YY Inner Join Grad As B On M.examine_report_record_id = B.examine_report_record_id Inner Join Graid_Detail As C On B.grad_id = C.graid_id Inner Join BuileBase As D On A.YY =D.YY And A.CustID = D.CustID Left Join Tb_Login As P On A.CheckerId = P.loginid Left Join Tb_Login As T On M.AtuCheckerId = T.loginid Where M.CheckDate Is Not Null And A.CustID !='AAA' And D.YY = 2015 Order by A.CustID,M.examine_report_record_id,C.repord_id,C.opt_id"; var items = db.Database.SqlQuery<GradHisList>(sql); #endregion var rpt_option = ReportList.GradOption(); var rpt_name = ReportList.GradRpt(); FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/GradHis.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["Query"]; int i = 1; foreach (var item in items) { var rpts = string.Empty; var option_name = string.Empty; var rptName = string.Empty; if (rpt_option.Any(x => x.Key == item.repord_id)) { var rpt = rpt_option[item.repord_id]; option_name = rpt.Any(x => x.Key == item.opt_id) ? rpt[item.opt_id] : "No"; rptName = rpt_name[item.repord_id]; } sheet.Cells[i + 1, 1].Value = i; sheet.Cells[i + 1, 2].Value = item.YY; sheet.Cells[i + 1, 3].Value = item.CustID; sheet.Cells[i + 1, 4].Value = item.CustName; sheet.Cells[i + 1, 5].Value = fma_date(item.DeliverDate); sheet.Cells[i + 1, 6].Value = fma_date(item.FixDate); sheet.Cells[i + 1, 7].Value = fma_date(item.CheckDate); sheet.Cells[i + 1, 8].Value = rptName; sheet.Cells[i + 1, 9].Value = fam_CheckState(item.CheckState); sheet.Cells[i + 1, 10].Value = option_name + (item.mem == null ? "" : item.mem); sheet.Cells[i + 1, 11].Value = item.CheckerNme; sheet.Cells[i + 1, 12].Value = item.ActuName; i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "申報資料審查歷程紀錄表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); }
public async Task<string> GetBuileBase(string CustId, int YY) { var db = new EnergyNetEntities(); var item = await db.BuileBase.FindAsync(new object[] { CustId, YY }); //var md = new m_BuileBase(); //md.CustID = item.CustID; //md.CustName = item.CustName; //md.ParkingMeasure = item.ParkingMeasure; //md.FloorSquareMeasure = item.FloorSquareMeasure; return defJSON(item); }
public FileResult Down10_4(int YY) { MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; FileInfo xlsfile = new FileInfo(Server.MapPath("~/xls/表10.4.xlsx")); ExcelPackage excel = new ExcelPackage(xlsfile); ExcelWorksheet sheet = excel.Workbook.Worksheets["報表10.4"]; var sql = @"Select B.YY,B.CustID,B.CustName, B.BusiID + B.BusiID3 As 行業編號, B.mbuild, no1_text ,no1_104_amt, no2_text, no3_text, no4_fam_text, no4_fam_select, no4_fam_select_5_memo, no4_eff_99, no4_eff_100, no4_eff_101, no4_eff_102, no4_eff_103, no4_eff_104, no4_eff_rate, no4_save_text, no4_save_amt_99, no4_save_amt_100, no4_save_amt_101, no4_save_amt_102, no4_save_amt_103, no4_use_amt_99, no4_use_amt_100, no4_use_amt_101, no4_use_amt_102, no4_use_amt_103, no4_save_rate, item ,y1, y2, [target], doing, fam_text, save_amt, save_money, invest, no6_text_1, no6_text_2, no6_text_3, no6_text_4, no7_text, no8_text From SaveNotGoal As A Inner Join BuileBase As B On A.CustID=B.CustID And A.YY= B.YY Left Join SaveNotGoal_No5 As C On A.CustID= C.CustID And A.YY= B.YY Where A.YY= 2015"; var items = db.Database.SqlQuery<Down10_4Modal>(sql); int i = 3; foreach (var item in items) { sheet.Cells[i + 1, 1].Value = item.CustID; sheet.Cells[i + 1, 2].Value = item.CustName; sheet.Cells[i + 1, 3].Value = item.YY; sheet.Cells[i + 1, 4].Value = item.行業編號; sheet.Cells[i + 1, 5].Value = item.mbuild; sheet.Cells[i + 1, 6].Value = item.no1_text; sheet.Cells[i + 1, 7].Value = item.no1_104_amt; sheet.Cells[i + 1, 8].Value = item.no2_text; sheet.Cells[i + 1, 9].Value = item.no3_text; sheet.Cells[i + 1, 10].Value = item.no4_fam_text; sheet.Cells[i + 1, 11].Value = item.no4_fam_select; sheet.Cells[i + 1, 12].Value = item.no4_fam_select_5_memo; sheet.Cells[i + 1, 13].Value = item.no4_eff_99; sheet.Cells[i + 1, 14].Value = item.no4_eff_100; sheet.Cells[i + 1, 15].Value = item.no4_eff_101; sheet.Cells[i + 1, 16].Value = item.no4_eff_102; sheet.Cells[i + 1, 17].Value = item.no4_eff_103; sheet.Cells[i + 1, 18].Value = item.no4_eff_104; sheet.Cells[i + 1, 19].Value = item.no4_eff_rate; sheet.Cells[i + 1, 20].Value = item.no4_save_text; sheet.Cells[i + 1, 21].Value = item.no4_save_amt_99; sheet.Cells[i + 1, 22].Value = item.no4_save_amt_100; sheet.Cells[i + 1, 23].Value = item.no4_save_amt_101; sheet.Cells[i + 1, 24].Value = item.no4_save_amt_102; sheet.Cells[i + 1, 25].Value = item.no4_save_amt_103; sheet.Cells[i + 1, 26].Value = item.no4_use_amt_99; sheet.Cells[i + 1, 27].Value = item.no4_use_amt_100; sheet.Cells[i + 1, 28].Value = item.no4_use_amt_101; sheet.Cells[i + 1, 29].Value = item.no4_use_amt_102; sheet.Cells[i + 1, 30].Value = item.no4_use_amt_103; sheet.Cells[i + 1, 31].Value = item.no4_save_rate; sheet.Cells[i + 1, 32].Value = item.item; sheet.Cells[i + 1, 33].Value = item.y1; sheet.Cells[i + 1, 34].Value = item.y2; sheet.Cells[i + 1, 35].Value = item.target; sheet.Cells[i + 1, 36].Value = item.doing; sheet.Cells[i + 1, 37].Value = item.fam_text; sheet.Cells[i + 1, 38].Value = item.save_amt; sheet.Cells[i + 1, 39].Value = item.save_money; sheet.Cells[i + 1, 40].Value = item.invest; sheet.Cells[i + 1, 41].Value = item.no6_text_1; sheet.Cells[i + 1, 42].Value = item.no6_text_2; sheet.Cells[i + 1, 43].Value = item.no6_text_3; sheet.Cells[i + 1, 44].Value = item.no6_text_4; sheet.Cells[i + 1, 45].Value = item.no7_text; sheet.Cells[i + 1, 46].Value = item.no8_text; i++; } var fs = new MemoryStream(); excel.SaveAs(fs); fs.Position = 0; string filename = "表10.4-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"; return File(fs, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename); } catch (Exception ex) { Response.Write(ex.Message); return null; } finally { } }
public async Task<string> GetFloorHeatMonth(string CustId, int YY) { var db = new EnergyNetEntities(); var r = new AjaxResult<IEnumerable<FloorHeat>>(); var floor_prv_p = await GetCalDefFloor(CustId, YY - 1); //前年 總樓地板面積”-“總室內停車場面積 var floor_now = await GetCalDefFloor(CustId, YY); // 總樓地板面積”-“總室內停車場面積 var floor_prv = floor_prv_p < 0 ? 0 : floor_prv_p; //if (floor_prv <= 0) //{ // RecordReportList(CustId, YY, "J55"); // r.result = false; // r.message = "無103年度資料不須填報!"; // return defJSONNull(r); //} //先檢查是否有負數 if (floor_now <= 0) { var t1 = await db.FloorHeat.Where(x => x.CustID == CustId && x.YY == YY).ToListAsync(); var t2 = await db.FloorHeatQ.Where(x => x.CustID == CustId && x.YY == YY).ToListAsync(); var t3 = await db.FloorHeatYear.FindAsync(new object[] { CustId, YY }); if (t1 != null) db.FloorHeat.RemoveRange(t1); if (t2 != null) db.FloorHeatQ.RemoveRange(t2); if (t3 != null) db.FloorHeatYear.Remove(t3); await db.SaveChangesAsync(); r.result = false; r.message = "能源用戶基本資料『總室內停車場面積』不可大於等於『總樓地板面積』,需先修正(104年)才可填報!"; return defJSONNull(r); } //以下為如果都沒有任何申報資料會預先新增12個月的預設空資料 及4季的空資料 if (!db.FloorHeat.Any(x => x.CustID == CustId && x.YY == YY)) { #region Code var heat_prv = await GetCalDefHeat(CustId, YY - 1); var heat_now = await GetCalDefHeat(CustId, YY); if (heat_now.Count() == 0) { r.result = false; r.message = "請先完成填報今年度熱能資料"; return defJSONNull(r); } var del_q_item = db.FloorHeatQ.Where(x => x.CustID == CustId && x.YY == YY); db.FloorHeatQ.RemoveRange(del_q_item); var del_y_item = await db.FloorHeatYear.FindAsync(new object[] { CustId, YY }); if (del_y_item != null) { db.FloorHeatYear.Remove(del_y_item); } await db.SaveChangesAsync(); #region 總年度處理 //先增加年度獨立資料 FloorHeatYear md_y = new FloorHeatYear(); md_y.YY = YY; md_y.CustID = CustId; md_y.p_floor = floor_prv; md_y.n_floor = floor_now; md_y.p_eui_amt = heat_prv.Select(x => x.Total).DefaultIfEmpty(0).Sum(); md_y.n_eui_amt = heat_now.Select(x => x.Total).DefaultIfEmpty(0).Sum(); md_y.p_eui = floor_prv == 0 ? 0 : (md_y.p_eui_amt / md_y.p_floor); //預防分母為0 md_y.n_eui = floor_now == 0 ? 0 : (md_y.n_eui_amt / md_y.n_floor); var n_value = md_y.n_eui_amt / md_y.n_floor; var p_value = md_y.p_floor == 0 ? 0 : md_y.p_eui_amt / md_y.p_floor; if (p_value == 0) { md_y.diff_result = 0; } else { md_y.diff_result = ((n_value - p_value) / p_value) * 100; //年度差異分析 } db.FloorHeatYear.Add(md_y); #endregion IList<FloorHeat> keep_FloorHeat = new List<FloorHeat>(); for (int i = 0; i < 12; i++) { #region code var e_p = heat_prv.Where(x => x.MM == (i + 1)).FirstOrDefault(); var f_e_p = e_p == null || e_p.Total == null || floor_prv == 0 ? 0 : e_p.Total / floor_prv; var e_n = heat_now.Where(x => x.MM == (i + 1)).FirstOrDefault(); var f_e_n = e_n.Total == null ? 0 : e_n.Total / floor_now; var md = new FloorHeat(); md.CustID = CustId; md.YY = YY; md.month = i + 1; md.p_eui_amt = e_p == null ? 0 : e_p.Total; md.n_eui_amt = e_n.Total; md.p_floor = floor_prv; md.n_floor = floor_now; md.p_eui = f_e_p; md.n_eui = f_e_n; db.FloorHeat.Add(md); keep_FloorHeat.Add(md); #endregion } for (int j = 0; j < 4; j++) { #region code var md = new FloorHeatQ(); md.CustID = CustId; md.YY = YY; md.QNO = (j + 1); var q_item = keep_FloorHeat.Where(x => (x.month - 1) / 3 == j); var handle_result = q_item.GroupBy(x => new { f = 1 }).Select( y => new { sum_p_eui_amt = y.Sum(x => x.p_eui_amt), sum_n_eui_amt = y.Sum(x => x.n_eui_amt), avg_p_floor = y.Average(x => x.p_floor), avg_n_floor = y.Average(x => x.n_floor) }).FirstOrDefault(); md.avg_n_floor = handle_result.avg_n_floor; md.avg_p_floor = handle_result.avg_p_floor; md.sum_n_eui_amt = handle_result.sum_n_eui_amt; md.sum_p_eui_amt = handle_result.sum_p_eui_amt; var q_p_value = md.avg_p_floor == 0 ? 0 : md.sum_p_eui_amt / md.avg_p_floor; var q_n_value = md.sum_n_eui_amt / md.avg_n_floor; if (q_p_value == 0) { md.diff_result = 0; } else { md.diff_result = ((q_n_value - q_p_value) / q_p_value) * 100; } db.FloorHeatQ.Add(md); #endregion } await db.SaveChangesAsync(); #endregion } else { var last_year_elec_months = await GetCalDefHeat(CustId, YY - 1); var now_year_elec_months = await GetCalDefHeat(CustId, YY); var mms = await db.FloorHeat.Where(x => x.CustID == CustId && x.YY == YY).OrderBy(x => x.month).ToListAsync(); var mmq = await db.FloorHeatQ.Where(x => x.CustID == CustId && x.YY == YY).OrderBy(x => x.QNO).ToListAsync(); var year_heat = await db.FloorHeatYear.FindAsync(new object[] { CustId, YY }); #region Code Month for (int i = 0; i < 12; i++) { var mm = mms.FirstOrDefault(x => x.month == (i + 1)); var last_year_elec_use_amt_month = last_year_elec_months.Where(x => x.MM == (i + 1)).FirstOrDefault(); if (mm != null && last_year_elec_use_amt_month != null) { mm.p_eui_amt = last_year_elec_use_amt_month.Total; mm.p_eui = mm.p_floor == 0 ? 0 : mm.p_eui_amt / mm.p_floor; //每月 用電量/樓地板面積 } var now_year_elec_use_amt_month = now_year_elec_months.Where(x => x.MM == (i + 1)).FirstOrDefault(); if (mm != null && now_year_elec_use_amt_month != null) { mm.n_eui_amt = now_year_elec_use_amt_month.Total; mm.n_eui = mm.n_eui_amt / mm.n_floor; //每月 用電量/樓地板面積 } } #endregion #region Code Q for (int j = 0; j < 4; j++) { var qno = j + 1; var q_data = mmq.FirstOrDefault(x => x.QNO == qno); if (q_data != null) { var q_data_by_mms = mms.Where(x => (x.month - 1) / 3 == j); var q_result = q_data_by_mms.GroupBy(x => new { f = 1 }).Select( y => new { sum_p_eui_amt = y.Sum(x => x.p_eui_amt), sum_n_eui_amt = y.Sum(x => x.n_eui_amt), avg_p_floor = y.Average(x => x.p_floor), avg_n_floor = y.Average(x => x.n_floor) }).FirstOrDefault(); q_data.avg_p_floor = q_result.avg_p_floor; q_data.avg_n_floor = q_result.avg_n_floor; q_data.sum_p_eui_amt = q_result.sum_p_eui_amt; q_data.sum_n_eui_amt = q_result.sum_n_eui_amt; var q_p_value = q_data.avg_p_floor == 0 ? 0 : q_data.sum_p_eui_amt / q_data.avg_p_floor; var q_n_value = q_data.sum_n_eui_amt / q_data.avg_n_floor; if (q_p_value == 0 || q_n_value == 0) { q_data.diff_result = 0; } else { q_data.diff_result = ((q_n_value - q_p_value) / q_p_value) * 100; } } } #endregion #region Year if (year_heat != null) { year_heat.n_floor = floor_now; year_heat.p_eui_amt = last_year_elec_months.Select(x => x.Total).DefaultIfEmpty(0).Sum(); year_heat.n_eui_amt = now_year_elec_months.Select(x => x.Total).DefaultIfEmpty(0).Sum(); year_heat.p_eui = floor_prv == 0 ? 0 : (year_heat.p_eui_amt / year_heat.p_floor); //預防分母為0 year_heat.n_eui = floor_now == 0 ? 0 : (year_heat.n_eui_amt / floor_now); if (year_heat.p_eui == 0) { year_heat.diff_result = 0; } else { year_heat.diff_result = ((year_heat.n_eui - year_heat.p_eui) / year_heat.p_eui) * 100; //年度差異分析 } } #endregion await db.SaveChangesAsync(); } var item = await db.FloorHeat .Where(x => x.CustID == CustId && x.YY == YY) .OrderBy(x => x.month) .ToListAsync(); r.data = item; r.result = true; return defJSONNull(r); }
public FileResult xls_10_2(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); fileStream = new FileStream(Server.MapPath("~/xls/r10_2.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("表十之二、104年節約能源措施執行成效分析表"); ISheet smpSheet = wbXLS.GetSheet("Sample"); var item_total = db.SaveEnergyResult_2015 .Where(x => x.CustID == CustId && x.YY == YY) .OrderBy(x => x.item); decimal r = (decimal)item_total.Count() / 10; //每頁10列 計算分頁 var row = Math.Ceiling(r); int pos = 1; decimal Ht = 0; decimal It = 0; decimal Lt = 0; decimal Mt = 0; decimal Nt = 0; decimal Ot = 0; decimal Pt = 0; decimal Qt = 0; decimal Rt = 0; for (int j = 0; j < row; j++) { CopyRowArea(wbXLS, smpSheet, getSheet, 9, pos, 3); //copy表頭 pos = pos + 3; for (int i = 0; i < 10; i++) { //Clear #region Clear SetCellValue(smpSheet, "B1", ""); var cv = "□跨年度成效(註4)\r\n□當年度計畫(註5)"; SetCellValue(smpSheet, "C1", cv); SetCellValue(smpSheet, "D1", ""); SetCellValue(smpSheet, "E1", ""); SetCellValue(smpSheet, "F1", ""); SetCellValue(smpSheet, "F2", ""); SetCellValue(smpSheet, "F3", ""); SetCellValue(smpSheet, "F4", ""); SetCellValue(smpSheet, "H1", ""); SetCellValue(smpSheet, "I1", ""); SetCellValue(smpSheet, "J1", ""); SetCellValue(smpSheet, "K1", ""); SetCellValue(smpSheet, "L1", ""); SetCellValue(smpSheet, "M1", ""); SetCellValue(smpSheet, "N1", ""); SetCellValue(smpSheet, "O1", ""); SetCellValue(smpSheet, "P1", ""); SetCellValue(smpSheet, "Q1", ""); SetCellValue(smpSheet, "R1", ""); #endregion #region Code var item = item_total.Skip((j * 10) + i).FirstOrDefault(); if (item != null) { SetCellValue(smpSheet, "B1", item.item); cv = string.Empty; if (item.save_src == "1") { cv = Y + "跨年度成效(註4)\r\n" + N + "當年度計畫(註5)"; } if (item.save_src == "0") { cv = N + "跨年度成效(註4)\r\n" + Y + "當年度計畫(註5)"; } SetCellValue(smpSheet, "C1", cv); SetCellValue(smpSheet, "D1", item.save_code); SetCellValue(smpSheet, "E1", item.act_exec); SetCellValue(smpSheet, "F1", item.y1.ToString()); SetCellValue(smpSheet, "F2", item.m1.ToString()); SetCellValue(smpSheet, "F3", item.y2.ToString()); SetCellValue(smpSheet, "F4", item.m2.ToString()); SetCellValue(smpSheet, "H1", (item.eff_save_money / 1000).ToString()); SetCellValue(smpSheet, "I1", (item.inves_total / 1000).ToString()); SetCellValue(smpSheet, "J1", item.start_m.ToString()); SetCellValue(smpSheet, "K1", item.end_m.ToString()); SetCellValue(smpSheet, "L1", item.restrain_amt.ToString()); Ht += (item.eff_save_money == null ? 0 : (decimal)item.eff_save_money); It += (item.inves_total == null ? 0 : (decimal)item.inves_total); Lt += (item.restrain_amt == null ? 0 : (decimal)item.restrain_amt); #region code if (item.eng_type == "1") { SetCellValue(smpSheet, "M1", item.fma_no3_F.ToString()); Mt = Mt + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } if (item.eng_type == "2") { SetCellValue(smpSheet, "N1", item.fma_no3_F.ToString()); Nt = Nt + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } if (item.eng_type == "3") { SetCellValue(smpSheet, "O1", item.fma_no3_F.ToString()); Ot = Ot + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } if (item.eng_type == "4") { SetCellValue(smpSheet, "P1", item.fma_no3_F.ToString()); Pt = Pt + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } if (item.eng_type == "5") { SetCellValue(smpSheet, "Q1", item.fma_no3_F.ToString()); Qt = Qt + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } if (item.eng_type == "6") { SetCellValue(smpSheet, "R1", item.fma_no3_F.ToString()); Rt = Rt + item.fma_no3_F == null ? 0 : (decimal)item.fma_no3_F; } #endregion } else { } CopyRowArea(wbXLS, smpSheet, getSheet, 0, pos, 4); pos = pos + 4; #endregion } //合計 SetCellValue(smpSheet, "H6", (Ht / 1000).ToString()); SetCellValue(smpSheet, "I6", (It / 1000).ToString()); SetCellValue(smpSheet, "L6", Lt.ToString()); SetCellValue(smpSheet, "M6", Mt.ToString()); SetCellValue(smpSheet, "N6", Nt.ToString()); SetCellValue(smpSheet, "O6", Ot.ToString()); SetCellValue(smpSheet, "P6", Pt.ToString()); SetCellValue(smpSheet, "Q6", Qt.ToString()); SetCellValue(smpSheet, "R6", Rt.ToString()); CopyRowArea(wbXLS, smpSheet, getSheet, 5, pos, 1); //copy 合計樣版 pos = pos + 1; CopyRowArea(wbXLS, smpSheet, getSheet, 7, pos, 1); //copy 註解樣版 getSheet.GetRow(pos).HeightInPoints = 7 * getSheet.DefaultRowHeight / 20; //設定註解列 列高 getSheet.SetRowBreak(pos); //設定分頁 pos = pos + 1; } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十之二、104年節約能源措施執行成效分析表-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { var m = getErrorMessage(ex); Log.Write(m); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public FileResult xls_11_3(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r11_3.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); ISheet getSheet = wbXLS.GetSheet("表十一之三、105年執行計畫之年度節電率"); var data = db.PlanEnergyAnalysis.Find(new object[] { CustId, YY }); if (data != null) { SetCellValue(getSheet, "C5", (double)data.ElecSave); if (data.cale_type == "1") { SetCellValue(getSheet, "C7", Y + " 104年全年實際用電量(註3)"); SetCellValue(getSheet, "E7", data.use_104_amt.ToString()); if (data.no1_amt != null) { SetCellValue(getSheet, "D9", Y + "軌道牽引電力之電量:" + data.no1_amt + "度。(註4)"); } if (data.no2_amt != null) { SetCellValue(getSheet, "D10", Y + "工程施作區域用電量:" + data.no2_amt + "度。(註5)"); } SetCellValue(getSheet, "E9", data.no_sum.ToString()); } if (data.cale_type == "2") { SetCellValue(getSheet, "C11", Y + " 其他估算方式(註6)"); SetCellValue(getSheet, "D13", data.use_other_memo); SetCellValue(getSheet, "E12", data.use_other_value.ToString()); } SetCellValue(getSheet, "D14", data.use_rate.ToString() + "%"); } wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十一之三、105年執行計畫之年度節電率-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public FileResult xls_10_4(string CustId, int YY) { HSSFWorkbook wbXLS; FileStream fileStream = null; MemoryStream outputStream = new MemoryStream(); try { var db = new EnergyNetEntities(); var CYY = YY - 1911; fileStream = new FileStream(Server.MapPath("~/xls/r10_4.xls"), FileMode.Open, FileAccess.ReadWrite); wbXLS = new HSSFWorkbook(fileStream); HSSFSheet getSheet = (HSSFSheet)wbXLS.GetSheet("表十之四、104年執行計畫平均年節電率未達1%說明"); var data = db.SaveNotGoal.Find(new object[] { CustId, YY }); var data_detail = db.SaveNotGoal_No5.Where(x => x.CustID == CustId && x.YY == YY).OrderByDescending(x => x.item); if (data != null) { SetCellValue(getSheet, "E4", data.no1_text); SetCellValue(getSheet, "E5", "104年啟用之新建築或新設備之用電量:" + data.no1_104_amt + "度"); SetCellValue(getSheet, "E6", data.no2_text); SetCellValue(getSheet, "E7", data.no3_text); SetCellValue(getSheet, "E10", data.no4_fam_text); if (data.no4_fam_select == "1") { SetCellValue(getSheet, "G13", "來客數"); SetCellValue(getSheet, "E17", "來客數"); } if (data.no4_fam_select == "2") { SetCellValue(getSheet, "G13", "貨櫃數"); SetCellValue(getSheet, "E17", "貨櫃數"); } if (data.no4_fam_select == "3") { SetCellValue(getSheet, "G13", "營業額"); SetCellValue(getSheet, "E17", "營業額"); } if (data.no4_fam_select == "4") { SetCellValue(getSheet, "G13", "樓地版面積"); SetCellValue(getSheet, "E17", "樓地版面積"); } if (data.no4_fam_select == "5") { SetCellValue(getSheet, "G13", "其他:" + data.no4_fam_select_5_memo); SetCellValue(getSheet, "E17", "其他"); } SetCellValue(getSheet, "F16", data.elec_use_99.ToString()); SetCellValue(getSheet, "G16", data.elec_use_100.ToString()); SetCellValue(getSheet, "H16", data.elec_use_101.ToString()); SetCellValue(getSheet, "I16", data.elec_use_102.ToString()); SetCellValue(getSheet, "J16", data.elec_use_103.ToString()); SetCellValue(getSheet, "K16", data.elec_use_104.ToString()); SetCellValue(getSheet, "F17", data.sour_use_99.ToString()); SetCellValue(getSheet, "G17", data.sour_use_100.ToString()); SetCellValue(getSheet, "H17", data.sour_use_101.ToString()); SetCellValue(getSheet, "I17", data.sour_use_102.ToString()); SetCellValue(getSheet, "J17", data.sour_use_103.ToString()); SetCellValue(getSheet, "K17", data.sour_use_104.ToString()); SetCellValue(getSheet, "F18", data.no4_eff_99.ToString()); SetCellValue(getSheet, "G18", data.no4_eff_100.ToString()); SetCellValue(getSheet, "H18", data.no4_eff_101.ToString()); SetCellValue(getSheet, "I18", data.no4_eff_102.ToString()); SetCellValue(getSheet, "J18", data.no4_eff_103.ToString()); SetCellValue(getSheet, "K18", data.no4_eff_104.ToString()); SetCellValue(getSheet, "I19", data.no4_eff_rate + "%"); SetCellValue(getSheet, "E22", data.no4_save_text); SetCellValue(getSheet, "F25", data.no4_save_amt_99.ToString()); SetCellValue(getSheet, "G25", data.no4_save_amt_100.ToString()); SetCellValue(getSheet, "H25", data.no4_save_amt_101.ToString()); SetCellValue(getSheet, "I25", data.no4_save_amt_102.ToString()); SetCellValue(getSheet, "J25", data.no4_save_amt_103.ToString()); SetCellValue(getSheet, "F26", data.no4_use_amt_99.ToString()); SetCellValue(getSheet, "G26", data.no4_use_amt_100.ToString()); SetCellValue(getSheet, "H26", data.no4_use_amt_101.ToString()); SetCellValue(getSheet, "I26", data.no4_use_amt_102.ToString()); SetCellValue(getSheet, "J26", data.no4_use_amt_103.ToString()); SetCellValue(getSheet, "G27", data.no4_save_rate.ToString()); SetCellValue(getSheet, "D31", data.no6_text_1); SetCellValue(getSheet, "F31", data.no6_text_2); SetCellValue(getSheet, "H31", data.no6_text_3); SetCellValue(getSheet, "J31", data.no6_text_4); SetCellValue(getSheet, "D32", data.no7_text); SetCellValue(getSheet, "D33", data.no8_text); } var ct = data_detail.Count(); for (int i = 0; i < ct; i++) { InsertRows(getSheet, 29, 1); var item = data_detail.Skip(i).FirstOrDefault(); SetCellValue(getSheet, "D30", item.item); SetCellValue(getSheet, "E30", item.y1 + "年~" + item.y2 + "年"); SetCellValue(getSheet, "F30", item.target); SetCellValue(getSheet, "G30", item.doing); SetCellValue(getSheet, "H30", item.fam_text); SetCellValue(getSheet, "I30", item.save_amt.ToString()); SetCellValue(getSheet, "J30", item.save_money.ToString()); SetCellValue(getSheet, "K30", item.invest.ToString()); } getSheet.AddMergedRegion(new CellRangeAddress(28, 28 + ct, 1, 1)); //合併儲存格 getSheet.AddMergedRegion(new CellRangeAddress(28, 28 + ct, 2, 2)); //合併儲存格 wbXLS.Write(outputStream); outputStream.Position = 0; string setFileName = "表十之四、104年執行計畫平均年節電率未達1%說明-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; OutPutForIE8(outputStream, setFileName); return File(outputStream, "application/vnd.ms-excel", setFileName); } catch (Exception ex) { Log.Write(ex.Message + ex.StackTrace); Log.WriteToFile(); return null; } finally { //db0.Dispose(); } }
public string GetNoPass() { var db = new EnergyNetEntities(); string sql = @"Select A.CustID,C.CustName,A.CheckDate,A.DeliverDate,A.FixDate,B.email As CheckerEmail,B.checker,B.checkerId,A.GreenCheckState From ExamineReport_Master As A Inner Join 評核對應表2015V1 As B On A.CustID=RTrim(B.comno) Inner Join (Select CustID,CustName,ManagerMail As Email From BuileBase Where YY=2015) As C On A.CustID=C.CustID Where A.GreenCheckState='C';"; var all_data = db.Database.SqlQuery<EmailNotHandle>(sql).ToList(); return defJSONNull(all_data); }