public async Task<string> aj_excelCondenserApplyUpload(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
var fileName = "Manager-" + this.UserId + "-申報冷凝器-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
var path = Path.Combine(Server.MapPath("~/_Code/SysUpFiles/ExcelUpLoadData/ApplyCon"), fileName);
file.SaveAs(path);
#region
string tpl_File = string.Empty;
HSSFWorkbook wbXLS;
try
{
db0 = getDB0();
wbXLS = new HSSFWorkbook(file.InputStream);
ISheet getSheet = wbXLS.GetSheet("年度申報表");
var getUserId = GetCellValueString(getSheet, "G3");
if (getUserId != this.UserId)
{
rAjaxResult.result = false;
rAjaxResult.message = "User Id驗證不符";
return defJSON(rAjaxResult);
}
double getYear = GetCellValueInt(getSheet, "C4");
if (getYear > allowApplyYear)
{
rAjaxResult.message = string.Format("{0}年度不充許申報!", getYear);
rAjaxResult.result = false;
return defJSON(rAjaxResult);
}
var isExist = db0.Apply.Any(x => x.y == getYear && x.userid == this.UserId);
int startRow = 7;
int getLastRow = getSheet.LastRowNum;
int apply_id = 0;
Apply apply = new Apply();
if (!isExist)//如果申報表不存在就新增
{
//Apply apply = new Apply();
apply.apply_id = GetNewId(ProcCore.Business.CodeTable.Apply);
apply.doc_date = DateTime.Now;
apply_id = apply.apply_id;//取得id
var getLast_1 = getSheet.GetRow(getLastRow - 1);
apply.doc_name = getLast_1.GetCell(2).StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.GetCell(4).StringCellValue; ;
apply.doc_tel = getLast_1.GetCell(6).StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.GetCell(2).StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.GetCell(4).StringCellValue;
apply.mng_tel = getLast_2.GetCell(6).StringCellValue;
apply.y = Convert.ToInt16(getYear);
apply.start_date = DateTime.Parse(getYear + "/01/01");
apply.end_date = DateTime.Parse(getYear + "/12/31");
apply.i_InsertDateTime = DateTime.Now;
apply.userid = this.UserId;
apply.Apply_Detail_Condenser = new List<Apply_Detail_Condenser>();
apply.Apply_Detail_Evaporate = new List<Apply_Detail_Evaporate>();
}
else
{//存在就修改資料
apply_id = db0.Apply.SingleOrDefault(x => x.y == getYear && x.userid == this.UserId).apply_id;//取得id
apply = await db0.Apply.FindAsync(apply_id);
var getLast_1 = getSheet.GetRow(getLastRow - 1);
apply.doc_name = getLast_1.GetCell(2).StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.GetCell(4).StringCellValue; ;
apply.doc_tel = getLast_1.GetCell(6).StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.GetCell(2).StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.GetCell(4).StringCellValue;
apply.mng_tel = getLast_2.GetCell(6).StringCellValue;
apply.i_UpdateDateTime = DateTime.Now;
}
int equiCount = db0.Equipment_Chiller.Where(x => x.USERID == this.UserId).Count();//計算該廠商有幾筆設備
for (int i = 0; i < equiCount; i++)
{
//int monthstart = 0;//起始一月行數
IRow getRow = getSheet.GetRow(startRow);
ICell getCell = getRow.GetCell(1);
//取得設備編號
string getEquipmentSN = getRow.GetCell(1).StringCellValue;
var checkEquip = db0.Equipment_Chiller.Where(x => x.equipment_sn == getEquipmentSN && x.USERID == this.UserId).Select(x => new { x.equipment_chiller_id, x.equipment_sn }).FirstOrDefault();
if (checkEquip == null)
{
rAjaxResult.result = false;
rAjaxResult.message = "此設備編號不存在:" + getEquipmentSN;
return defJSON(rAjaxResult);
}
//判斷設備是否已申請過
int getEquid = checkEquip.equipment_chiller_id;//取得設備id
Boolean checkDetail = db0.Apply_Detail_Condenser.Any(x => x.equipment_id == getEquid && x.y == getYear && x.userid == this.UserId);
if (checkDetail)
{
rAjaxResult.result = false;
rAjaxResult.message = "此設備編號已存在:" + getEquipmentSN + ",請點選刪除後再重新上傳";
return defJSON(rAjaxResult);
}
#region 申報明細
if (getCellValue(getRow, 2) != "")
{
decimal get設計值 = (decimal)getRow.GetCell(2).NumericCellValue;
string[] getAbnormal = new string[12];//異常狀況陣列
decimal?[] getTd_value = new decimal?[12];
//每月資料填入
for (int j = 0; j < 12; j++)
{
getRow = getSheet.GetRow(startRow);
try { getTd_value[j] = (decimal?)getCellValue_ForAvgMonth(getRow, 4); }
catch
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = "此設備編號:" + getEquipmentSN + "，月平均溫度有資料格式錯誤！";
return defJSON(rAjaxResult);
}
getAbnormal[j] = getRow.GetCell(5).StringCellValue;
startRow += 1;
}
Apply_Detail_Condenser makeCondenserDetail = new Apply_Detail_Condenser()
{
apply_detail_condenser_id = GetNewId(ProcCore.Business.CodeTable.Apply_Detail_Condenser),
apply_id = apply_id,
equipment_id = checkEquip.equipment_chiller_id,
equipment_sn = checkEquip.equipment_sn,
td_set = get設計值,
abnormal_01 = getAbnormal[0],
abnormal_02 = getAbnormal[1],
abnormal_03 = getAbnormal[2],
abnormal_04 = getAbnormal[3],
abnormal_05 = getAbnormal[4],
abnormal_06 = getAbnormal[5],
abnormal_07 = getAbnormal[6],
abnormal_08 = getAbnormal[7],
abnormal_09 = getAbnormal[8],
abnormal_10 = getAbnormal[9],
abnormal_11 = getAbnormal[10],
abnormal_12 = getAbnormal[11],
td_value_01 = getTd_value[0],
td_value_02 = getTd_value[1],
td_value_03 = getTd_value[2],
td_value_04 = getTd_value[3],
td_value_05 = getTd_value[4],
td_value_06 = getTd_value[5],
td_value_07 = getTd_value[6],
td_value_08 = getTd_value[7],
td_value_09 = getTd_value[8],
td_value_10 = getTd_value[9],
td_value_11 = getTd_value[10],
td_value_12 = getTd_value[11],
i_InsertDateTime = DateTime.Now,
y = Convert.ToInt16(getYear),
userid = this.UserId
};
apply.Apply_Detail_Condenser.Add(makeCondenserDetail);
}
#endregion
}
if (!isExist)
{
db0.Apply.Add(apply);
}
await db0.SaveChangesAsync();
rAjaxResult.result = true;
rAjaxResult.success = true;
rAjaxResult.FileName = file.FileName;
}
catch (LogicError ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = getRecMessage(ex.Message);
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = ex.Message + ex.StackTrace;
}
finally
{
db0.Dispose();
}
#endregion
return defJSON(rAjaxResult);
}
public async Task<string> aj_excelMonthUpload(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
#region
string tpl_File = string.Empty;
HSSFWorkbook wbXLS;
try
{
#region MyRegion
db0 = getDB0();
wbXLS = new HSSFWorkbook(file.InputStream);
ISheet getSheet = wbXLS.GetSheet("每月監控平均值");
var getUserId = GetCellValueString(getSheet, "E3");
if (getUserId != this.UserId)
{
rAjaxResult.result = false;
rAjaxResult.message = "User Id驗證不符";
return defJSON(rAjaxResult);
}
double getYear = GetCellValueInt(getSheet, "C4");
if (getYear > allowApplyYear)
{
rAjaxResult.message = string.Format("{0}年度不充許申報!", getYear);
rAjaxResult.result = false;
return defJSON(rAjaxResult);
}
var isExist = db0.Apply_Detail_Condenser.Any(x => x.y == getYear);
if (isExist)
{
rAjaxResult.message = string.Format("{0}年度資料已存在無法新增!", getYear);
rAjaxResult.result = false;
return defJSON(rAjaxResult);
}
int startRow = 7;
int getLastRow = getSheet.LastRowNum;
var getEquipments = db0.Equipment_Chiller.Where(x => x.USERID == this.UserId).ToList();
for (int i = startRow; i < getLastRow; i++)
{
#region MyRegion
var getRow = getSheet.GetRow(i);
var getEquip = getRow.Cells[1];
if (getEquip.StringCellValue != "")
{
var getEquipment = getEquipments.Where(x => x.equipment_sn == getEquip.StringCellValue).FirstOrDefault();
if (getEquipment != null)
{
var md = new Apply_Detail_Condenser()
{
y = Convert.ToInt16(getYear),
userid = this.UserId,
apply_detail_condenser_id = GetNewId(ProcCore.Business.CodeTable.Apply_MonthAverage),
equipment_id = getEquipment.equipment_chiller_id,
td_value_01 = (decimal)getRow.Cells[3].NumericCellValue,
td_value_02 = (decimal)getRow.Cells[4].NumericCellValue,
td_value_03 = (decimal)getRow.Cells[5].NumericCellValue,
td_value_04 = (decimal)getRow.Cells[6].NumericCellValue,
td_value_05 = (decimal)getRow.Cells[7].NumericCellValue,
td_value_06 = (decimal)getRow.Cells[8].NumericCellValue,
td_value_07 = (decimal)getRow.Cells[9].NumericCellValue,
td_value_08 = (decimal)getRow.Cells[10].NumericCellValue,
td_value_09 = (decimal)getRow.Cells[11].NumericCellValue,
td_value_10 = (decimal)getRow.Cells[12].NumericCellValue,
td_value_11 = (decimal)getRow.Cells[13].NumericCellValue,
td_value_12 = (decimal)getRow.Cells[14].NumericCellValue,
abnormal_01 = getRow.Cells[16].StringCellValue,
abnormal_02 = getRow.Cells[17].StringCellValue,
abnormal_03 = getRow.Cells[18].StringCellValue,
abnormal_04 = getRow.Cells[19].StringCellValue,
abnormal_05 = getRow.Cells[20].StringCellValue,
abnormal_06 = getRow.Cells[21].StringCellValue,
abnormal_07 = getRow.Cells[22].StringCellValue,
abnormal_08 = getRow.Cells[23].StringCellValue,
abnormal_09 = getRow.Cells[24].StringCellValue,
abnormal_10 = getRow.Cells[25].StringCellValue,
abnormal_11 = getRow.Cells[26].StringCellValue,
abnormal_12 = getRow.Cells[27].StringCellValue,
i_InsertDateTime = DateTime.Now
};
db0.Apply_Detail_Condenser.Add(md);
}
}
#endregion
}
#endregion
await db0.SaveChangesAsync();
rAjaxResult.result = true;
rAjaxResult.success = true;
rAjaxResult.FileName = file.FileName;
}
catch (LogicError ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = getRecMessage(ex.Message);
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.success = false;
rAjaxResult.message = ex.Message + ex.StackTrace;
}
finally
{
db0.Dispose();
}
#endregion
return defJSON(rAjaxResult);
}