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);
}
