/// <summary>
/// 取得蒸發器、冷凝器年平均值
/// </summary>
/// <param name="Item"></param>
/// <returns></returns>
public static double GetYearAvg(YearAvgValue Item)
{
IList<Double> sum = new List<Double>();
if (Item.td_value_01 != null)
sum.Add((double)Item.td_value_01);
if (Item.td_value_02 != null)
sum.Add((double)Item.td_value_02);
if (Item.td_value_03 != null)
sum.Add((double)Item.td_value_03);
if (Item.td_value_04 != null)
sum.Add((double)Item.td_value_04);
if (Item.td_value_05 != null)
sum.Add((double)Item.td_value_05);
if (Item.td_value_06 != null)
sum.Add((double)Item.td_value_06);
if (Item.td_value_07 != null)
sum.Add((double)Item.td_value_07);
if (Item.td_value_08 != null)
sum.Add((double)Item.td_value_08);
if (Item.td_value_09 != null)
sum.Add((double)Item.td_value_09);
if (Item.td_value_10 != null)
sum.Add((double)Item.td_value_10);
if (Item.td_value_11 != null)
sum.Add((double)Item.td_value_11);
if (Item.td_value_12 != null)
sum.Add((double)Item.td_value_12);
return sum.Average();
}
public FileResult aj_excel_ALL(int Y)
{
HSSFWorkbook wbXLS;
FileStream fileStream = null;
MemoryStream outputStream = new MemoryStream();
try
{
db0 = getDB0();
fileStream = new FileStream(Server.MapPath("~/_Code/RPTExcel/ALL.xls"), FileMode.Open, FileAccess.ReadWrite);
wbXLS = new HSSFWorkbook(fileStream);
ISheet getSheet = wbXLS.GetSheet("allReport");
var t = db0.Apply.Where(x => x.y == Y).Select(
x => new
{
x,
y = x.Apply_Detail_Evaporate.Select(n => new { n, eq = n.Equipment_Chiller }),
z = x.Apply_Detail_Condenser.Select(n => new { n, eq = n.Equipment_Chiller })
}).ToList();
foreach (var m in t)
{
#region Set evaporate value
foreach (var item in m.y)
{
IRow get_row = getSheet.CreateRow(getSheet.LastRowNum + 1);
for (int i = 0; i < 30; i++)
{
get_row.CreateCell(i);
}
IList<string> cate = new List<string>();
var getUser = db0.Apply_User.Where(x => x.USERID == m.x.userid).FirstOrDefault();
get_row.GetCell(0).SetCellValue(m.x.userid);
get_row.GetCell(1).SetCellValue(m.x.userid);
get_row.GetCell(2).SetCellValue(getUser.USERNAME);
get_row.GetCell(3).SetCellValue(m.x.doc_date == null ? "" : ((DateTime)m.x.doc_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(4).SetCellValue(m.x.start_date == null ? "" : ((DateTime)m.x.start_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(5).SetCellValue(m.x.end_date == null ? "" : ((DateTime)m.x.end_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(7).SetCellValue(m.x.doc_tel == null ? "" : m.x.doc_tel);
get_row.GetCell(11).SetCellValue(m.x.doc_name == null ? "" : m.x.doc_name);
get_row.GetCell(12).SetCellValue(m.x.doc_rank == null ? "" : m.x.doc_rank);
get_row.GetCell(14).SetCellValue("蒸發器");
get_row.GetCell(15).SetCellValue(item.eq.equipment_sn);
get_row.GetCell(16).SetCellValue(item.eq.is_new_equip ? "新設" : "既設");
YearAvgValue d_item = new YearAvgValue()
{
td_value_01 = item.n.td_value_01,
td_value_02 = item.n.td_value_02,
td_value_03 = item.n.td_value_03,
td_value_04 = item.n.td_value_04,
td_value_05 = item.n.td_value_05,
td_value_06 = item.n.td_value_06,
td_value_07 = item.n.td_value_07,
td_value_08 = item.n.td_value_08,
td_value_09 = item.n.td_value_09,
td_value_10 = item.n.td_value_10,
td_value_11 = item.n.td_value_11,
td_value_12 = item.n.td_value_12
};
get_row.GetCell(17).SetCellValue(item.eq.td_evaporate_set == null ? 0 : (double)Math.Round((decimal)item.eq.td_evaporate_set, 1));
get_row.GetCell(18).SetCellValue(item.eq.td_condenser_set == null ? 0 : (double)Math.Round((decimal)item.eq.td_condenser_set, 1));
get_row.GetCell(19).SetCellValue(GetYearAvg(d_item));
get_row.GetCell(20).SetCellValue(CodeSheet.GetStateVal(m.x.verify_state, CodeSheet.verify_state));
get_row.GetCell(21).SetCellValue(m.x.verify_date == null ? "" : ((DateTime)m.x.verify_date).ToString("yyyy年MM月dd日"));
}
#endregion
#region Set condenser value
foreach (var item in m.z)
{
IRow get_row = getSheet.CreateRow(getSheet.LastRowNum + 1);
for (int i = 0; i < 30; i++)
{
get_row.CreateCell(i);
}
IList<string> cate = new List<string>();
var getUser = db0.Apply_User.Where(x => x.USERID == m.x.userid).FirstOrDefault();
get_row.GetCell(0).SetCellValue(m.x.userid);
get_row.GetCell(1).SetCellValue(m.x.userid);
get_row.GetCell(2).SetCellValue(getUser.USERNAME);
get_row.GetCell(3).SetCellValue(m.x.doc_date == null ? "" : ((DateTime)m.x.doc_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(4).SetCellValue(m.x.start_date == null ? "" : ((DateTime)m.x.start_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(5).SetCellValue(m.x.end_date == null ? "" : ((DateTime)m.x.end_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(7).SetCellValue(m.x.doc_tel == null ? "" : m.x.doc_tel);
get_row.GetCell(11).SetCellValue(m.x.doc_name == null ? "" : m.x.doc_name);
get_row.GetCell(12).SetCellValue(m.x.doc_rank == null ? "" : m.x.doc_rank);
get_row.GetCell(14).SetCellValue("冷凝器");
get_row.GetCell(15).SetCellValue(item.eq.equipment_sn);
get_row.GetCell(16).SetCellValue(item.eq.is_new_equip ? "新設" : "既設");
YearAvgValue d_item = new YearAvgValue()
{
td_value_01 = item.n.td_value_01,
td_value_02 = item.n.td_value_02,
td_value_03 = item.n.td_value_03,
td_value_04 = item.n.td_value_04,
td_value_05 = item.n.td_value_05,
td_value_06 = item.n.td_value_06,
td_value_07 = item.n.td_value_07,
td_value_08 = item.n.td_value_08,
td_value_09 = item.n.td_value_09,
td_value_10 = item.n.td_value_10,
td_value_11 = item.n.td_value_11,
td_value_12 = item.n.td_value_12
};
get_row.GetCell(17).SetCellValue(item.eq.td_evaporate_set == null ? 0 : (double)item.eq.td_evaporate_set);
get_row.GetCell(18).SetCellValue(item.eq.td_condenser_set == null ? 0 : (double)item.eq.td_condenser_set);
get_row.GetCell(19).SetCellValue(GetYearAvg(d_item));
get_row.GetCell(20).SetCellValue(CodeSheet.GetStateVal(m.x.verify_state, CodeSheet.verify_state));
get_row.GetCell(21).SetCellValue(m.x.verify_date == null ? "" : ((DateTime)m.x.verify_date).ToString("yyyy年MM月dd日"));
}
#endregion
}
wbXLS.Write(outputStream);
outputStream.Position = 0;
string setFileName = "設備-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
if (Request.Browser.Browser == "IE" && Request.Browser.Version == "8.0")
{
byte[] bytes = outputStream.ToArray();
Response.Clear();
Response.ContentType = "application/xls";
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(setFileName));
Response.BinaryWrite(bytes);
Response.End();
}
return File(outputStream, "application/vnd.ms-excel", setFileName);
}
catch (Exception ex)
{
Log.Write(ex.Message + ex.StackTrace);
return null;
}
finally
{
db0.Dispose();
}
}
public FileResult aj_excel_ALL(int Y)
{
HSSFWorkbook wbXLS;
FileStream fileStream = null;
MemoryStream outputStream = new MemoryStream();
try
{
db0 = getDB0();
fileStream = new FileStream(Server.MapPath("~/_Code/RPTExcel/ALL.xls"), FileMode.Open, FileAccess.ReadWrite);
wbXLS = new HSSFWorkbook(fileStream);
ISheet getSheet = wbXLS.GetSheet("allReport");
var t = db0.Apply.Where(x => x.y == Y).Select(
x => new
{
x,
e = x.Apply_Detail_Evaporate.Select(n => new { n, eq = n.Equipment_Chiller }),//蒸發器
c = x.Apply_Detail_Condenser.Select(n => new { n, eq = n.Equipment_Chiller }),//冷凝器
t = x.Apply_Detail_Thermal_Oil.Select(n => new { n, m = n.Fuel_Apply, eq = n.Equipment_Thermal_Oil })//熱煤鍋爐
}).ToList();
foreach (var m in t)
{
#region get 蒸發器 value
foreach (var item in m.e)
{
IRow get_row = getSheet.CreateRow(getSheet.LastRowNum + 1);
for (int i = 0; i < 33; i++)
{
get_row.CreateCell(i);
}
IList<string> cate = new List<string>();
var getUser = db0.Apply_User.Where(x => x.userid == m.x.userid).FirstOrDefault();
get_row.GetCell(0).SetCellValue(m.x.userid);
get_row.GetCell(1).SetCellValue(m.x.userid);
get_row.GetCell(2).SetCellValue(getUser.userid);
get_row.GetCell(3).SetCellValue(m.x.doc_date == null ? "" : ((DateTime)m.x.doc_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(4).SetCellValue(m.x.start_date == null ? "" : ((DateTime)m.x.start_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(5).SetCellValue(m.x.end_date == null ? "" : ((DateTime)m.x.end_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(7).SetCellValue(m.x.doc_tel == null ? "" : m.x.doc_tel);
get_row.GetCell(11).SetCellValue(m.x.doc_name == null ? "" : m.x.doc_name);
get_row.GetCell(12).SetCellValue(m.x.doc_rank == null ? "" : m.x.doc_rank);
get_row.GetCell(14).SetCellValue("蒸發器");
get_row.GetCell(15).SetCellValue(item.eq.equipment_sn);
YearAvgValue d_item = new YearAvgValue()
{
td_value_01 = item.n.td_value_01,
td_value_02 = item.n.td_value_02,
td_value_03 = item.n.td_value_03,
td_value_04 = item.n.td_value_04,
td_value_05 = item.n.td_value_05,
td_value_06 = item.n.td_value_06,
td_value_07 = item.n.td_value_07,
td_value_08 = item.n.td_value_08,
td_value_09 = item.n.td_value_09,
td_value_10 = item.n.td_value_10,
td_value_11 = item.n.td_value_11,
td_value_12 = item.n.td_value_12
};
get_row.GetCell(27).SetCellValue(item.eq.td_evaporate_set == null ? 0 : (double)item.eq.td_evaporate_set);
get_row.GetCell(28).SetCellValue(item.eq.td_condenser_set == null ? 0 : (double)item.eq.td_condenser_set);
get_row.GetCell(29).SetCellValue(GetYearAvg(d_item));
get_row.GetCell(30).SetCellValue(CodeSheet.GetStateVal(m.x.verify_state, CodeSheet.verify_state));
get_row.GetCell(31).SetCellValue(m.x.verify_date == null ? "" : ((DateTime)m.x.verify_date).ToString("yyyy年MM月dd日"));
}
#endregion
#region get 冷凝器 value
foreach (var item in m.c)
{
IRow get_row = getSheet.CreateRow(getSheet.LastRowNum + 1);
for (int i = 0; i < 33; i++)
{
get_row.CreateCell(i);
}
IList<string> cate = new List<string>();
var getUser = db0.Apply_User.Where(x => x.userid == m.x.userid).FirstOrDefault();
get_row.GetCell(0).SetCellValue(m.x.userid);
get_row.GetCell(1).SetCellValue(m.x.userid);
get_row.GetCell(2).SetCellValue(getUser.userid);
get_row.GetCell(3).SetCellValue(m.x.doc_date == null ? "" : ((DateTime)m.x.doc_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(4).SetCellValue(m.x.start_date == null ? "" : ((DateTime)m.x.start_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(5).SetCellValue(m.x.end_date == null ? "" : ((DateTime)m.x.end_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(7).SetCellValue(m.x.doc_tel == null ? "" : m.x.doc_tel);
get_row.GetCell(11).SetCellValue(m.x.doc_name == null ? "" : m.x.doc_name);
get_row.GetCell(12).SetCellValue(m.x.doc_rank == null ? "" : m.x.doc_rank);
get_row.GetCell(14).SetCellValue("冷凝器");
get_row.GetCell(15).SetCellValue(item.eq.equipment_sn);
YearAvgValue d_item = new YearAvgValue()
{
td_value_01 = item.n.td_value_01,
td_value_02 = item.n.td_value_02,
td_value_03 = item.n.td_value_03,
td_value_04 = item.n.td_value_04,
td_value_05 = item.n.td_value_05,
td_value_06 = item.n.td_value_06,
td_value_07 = item.n.td_value_07,
td_value_08 = item.n.td_value_08,
td_value_09 = item.n.td_value_09,
td_value_10 = item.n.td_value_10,
td_value_11 = item.n.td_value_11,
td_value_12 = item.n.td_value_12
};
get_row.GetCell(27).SetCellValue(item.eq.td_evaporate_set == null ? 0 : (double)item.eq.td_evaporate_set);
get_row.GetCell(28).SetCellValue(item.eq.td_condenser_set == null ? 0 : (double)item.eq.td_condenser_set);
get_row.GetCell(29).SetCellValue(GetYearAvg(d_item));
get_row.GetCell(30).SetCellValue(CodeSheet.GetStateVal(m.x.verify_state, CodeSheet.verify_state));
get_row.GetCell(31).SetCellValue(m.x.verify_date == null ? "" : ((DateTime)m.x.verify_date).ToString("yyyy年MM月dd日"));
}
#endregion
#region set 熱媒鍋爐 value
foreach (var item in m.t)
{
IRow get_row = getSheet.CreateRow(getSheet.LastRowNum + 1);
for (int i = 0; i < 33; i++)
{
get_row.CreateCell(i);
}
IList<string> cate = new List<string>();
var getUser = db0.Apply_User.Where(x => x.userid == m.x.userid).FirstOrDefault();
get_row.GetCell(0).SetCellValue(m.x.userid);
get_row.GetCell(1).SetCellValue(m.x.userid);
get_row.GetCell(2).SetCellValue(getUser.username);
get_row.GetCell(3).SetCellValue(m.x.doc_date == null ? "" : ((DateTime)m.x.doc_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(4).SetCellValue(m.x.start_date == null ? "" : ((DateTime)m.x.start_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(5).SetCellValue(m.x.end_date == null ? "" : ((DateTime)m.x.end_date).ToString("yyyy年MM月dd日"));
get_row.GetCell(7).SetCellValue(m.x.doc_tel == null ? "" : m.x.doc_tel);
get_row.GetCell(11).SetCellValue(m.x.doc_name == null ? "" : m.x.doc_name);
get_row.GetCell(12).SetCellValue(m.x.doc_rank == null ? "" : m.x.doc_rank);
get_row.GetCell(14).SetCellValue("熱煤鍋爐");
get_row.GetCell(15).SetCellValue(item.eq.equipment_sn);
get_row.GetCell(16).SetCellValue(item.eq.setup_amount == null ? 0 : (double)item.eq.setup_amount / 1000000);
var gas_item = item.m.Where(x => x.fuel_category == "gas");
if (gas_item.Count() > 0)
{
//get_row.GetCell(14).SetCellValue("氣體");
cate.Add("氣");
get_row.GetCell(21).SetCellValue((gas_item.Sum(x => x.year_fuel_amount).GetValueOrDefault(0)) / 1000);
}
var liquid_item = item.m.Where(x => x.fuel_category == "liquid");
if (liquid_item.Count() > 0)
{
cate.Add("液");
//get_row.GetCell(14).SetCellValue("液體");
get_row.GetCell(20).SetCellValue(liquid_item.Sum(x => x.year_fuel_amount).GetValueOrDefault(0));
}
var solid_item = item.m.Where(x => x.fuel_category == "solid");
if (solid_item.Count() > 0)
{
cate.Add("固");
//get_row.GetCell(14).SetCellValue("固體");
get_row.GetCell(18).SetCellValue((solid_item.Sum(x => x.year_fuel_amount).GetValueOrDefault(0)) / 1000);
}
get_row.GetCell(17).SetCellValue(string.Join(",", cate.ToArray()));
get_row.GetCell(25).SetCellValue(item.n.avg_Y_gas_temperature == null ? 0 : (double)item.n.avg_Y_gas_temperature);
get_row.GetCell(26).SetCellValue(item.n.avg_Y_oxygen_concentration == null ? 0 : (double)item.n.avg_Y_oxygen_concentration);
get_row.GetCell(30).SetCellValue(CodeSheet.GetStateVal(m.x.verify_state, CodeSheet.verify_state));
get_row.GetCell(31).SetCellValue(m.x.verify_date == null ? "" : ((DateTime)m.x.verify_date).ToString("yyyy年MM月dd日"));
}
#endregion
}
wbXLS.Write(outputStream);
outputStream.Position = 0;
string setFileName = "設備-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
if (Request.Browser.Browser == "IE" && Request.Browser.Version == "8.0")
{
byte[] bytes = outputStream.ToArray();
Response.Clear();
Response.ContentType = "application/xls";
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(setFileName));
Response.BinaryWrite(bytes);
Response.End();
}
return File(outputStream, "application/vnd.ms-excel", setFileName);
}
catch (Exception ex)
{
Log.Write(ex.Message + ex.StackTrace);
return null;
}
finally
{
db0.Dispose();
}
}