public string aj_excelEvaporateApplyUploadForTest(HttpPostedFileBase file)
{
ReturnAjaxFiles rAjaxResult = new ReturnAjaxFiles();
#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.Cells[1].StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.Cells[3].StringCellValue; ;
apply.doc_tel = getLast_1.Cells[5].StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.Cells[1].StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.Cells[3].StringCellValue;
apply.mng_tel = getLast_2.Cells[5].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.Cells[1].StringCellValue;
apply.doc_gender = true;
apply.doc_rank = getLast_1.Cells[3].StringCellValue; ;
apply.doc_tel = getLast_1.Cells[5].StringCellValue; ;
var getLast_2 = getSheet.GetRow(getLastRow);
apply.mng_name = getLast_2.Cells[1].StringCellValue;
apply.mng_gender = true;
apply.mng_rank = getLast_2.Cells[3].StringCellValue;
apply.mng_tel = getLast_2.Cells[5].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.Cells[0];
//取得設備編號
string getEquipmentSN = getRow.Cells[0].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_Evaporate.Any(x => x.equipment_id == getEquid && x.y == getYear && x.userid == this.UserId);
if (checkDetail)
{
rAjaxResult.result = false;
rAjaxResult.message = "此設備編號已存在:" + getEquipmentSN + ",請點選刪除後再重新上傳";
return defJSON(rAjaxResult);
}
decimal get設計值 = (decimal)getRow.Cells[1].NumericCellValue;
string[] getAbnormal = new string[12];//異常狀況陣列
decimal[] getTd_value = new decimal[12];
//每月資料填入
for (int j = 0; j < 12; j++)
{
getRow = getSheet.GetRow(startRow);
getTd_value[j] = (decimal)getRow.Cells[3].NumericCellValue;
getAbnormal[j] = getRow.Cells[4].StringCellValue;
startRow += 1;
}
Apply_Detail_Evaporate makeEvaporateDetail = new Apply_Detail_Evaporate()
{
apply_detail_evaporate_id = GetNewId(ProcCore.Business.CodeTable.Apply_Detail_Evaporate),
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_Evaporate.Add(makeEvaporateDetail);
}
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);
}
//apply新增
public string aj_MasterInsert(Apply md)
{
ResultInfo rAjaxResult = new ResultInfo();
if (Session["apply"].ToString() != md.vaild)
{
rAjaxResult.result = false;
rAjaxResult.message = "驗證碼不正確";
return defJSON(rAjaxResult);
};
if (md.join_num > 20 && md.apply_filepath == null)
{
rAjaxResult.result = false;
rAjaxResult.message = "申請人數超過20人,請上傳活動計畫書!";
return defJSON(rAjaxResult);
};
var open = openLogic();
using (db0 = open.getDB0)
{
using (TransactionScope tx = new TransactionScope())
{
try
{
md.apply_datetime = DateTime.Now;
md.i_Lang = "zh-TW";
md.audit_state = CodeSheet.audit_state.notaudit.Code;//狀態未審核
md.apply_class = CodeSheet.apply_class.front.Code;//前台審核
md.apply_idno = md.apply_idno.ToUpper();
#region 人數額度及日期驗證
//取得限制額度
int get_Apply_Max_Day = 0;
if (md.event_area == CodeSheet.apply_area.bufferNorth.Code.CInt())
{
get_Apply_Max_Day = (int)open.getParmValue(ParmDefine.bufferNorth_Max);
}
if (md.event_area == CodeSheet.apply_area.bufferSouth.Code.CInt())
{
get_Apply_Max_Day = (int)open.getParmValue(ParmDefine.bufferSouth_Max);
}
int get_N_MaxJoinnum = (int)open.getParmValue(ParmDefine.N_Max_joinnum);
int get_S_MaxJoinnum = (int)open.getParmValue(ParmDefine.S_Max_joinnum);
//設定申請天數範圍
DateTime setWorkFistDay = DateTime.Now.AddDays(7);//提前七天才可申請
DateTime setWokrLastDay = setWorkFistDay.AddDays(get_Apply_Max_Day);
if (md.event_strattime <= setWorkFistDay && md.event_strattime >= setWokrLastDay)
{
rAjaxResult.result = false;
rAjaxResult.message = "申請日期超過可允許範圍";
return defJSON(rAjaxResult);
}
int N = CodeSheet.apply_area.bufferNorth.Code.CInt();
int getNsum = db0.Apply.Where(x => ((DateTime)x.event_strattime).Year == md.endDate.Year &&
((DateTime)x.event_strattime).Month == md.endDate.Month &&
((DateTime)x.event_strattime).Day == md.endDate.Day &&
(x.audit_state == CodeSheet.audit_state.notaudit.Code ||
x.audit_state == CodeSheet.audit_state.pass.Code) &&
x.event_area == N)
.Select(x => x.join_num)
.DefaultIfEmpty(0)
.Sum();
int S = CodeSheet.apply_area.bufferSouth.Code.CInt();
int getSsum = db0.Apply.Where(x => ((DateTime)x.event_strattime).Year == md.endDate.Year &&
((DateTime)x.event_strattime).Month == md.endDate.Month &&
((DateTime)x.event_strattime).Day == md.endDate.Day &&
(x.audit_state == CodeSheet.audit_state.notaudit.Code ||
x.audit_state == CodeSheet.audit_state.pass.Code) &&
x.event_area == S)
.Select(x => x.join_num)
.DefaultIfEmpty(0)
.Sum();
Boolean arealimt = true;
if (md.event_area == CodeSheet.apply_area.bufferNorth.Code.CInt())
{
arealimt = (md.join_num + getNsum) > get_N_MaxJoinnum ? false : true;
}
else if (md.event_area == CodeSheet.apply_area.bufferSouth.Code.CInt())
{
arealimt = (md.join_num + getSsum) > get_S_MaxJoinnum ? false : true;
}
if (!arealimt)
{
rAjaxResult.result = false;
rAjaxResult.message = "申請人數超過上限~!";
return defJSON(rAjaxResult);
}
#endregion
#region 申請序號
//取得上一筆的日期
int oldid = 0;
if (db0.Apply.Any())
oldid = db0.Apply.Max(x => x.apply_id);
//取得最後一筆id
string oldday = "";
var text = db0.Apply.Any(x => x.apply_id == oldid);
if (oldid == 0 && !text)//如果連第一筆也不存在
oldday = DateTime.Now.AddYears(-1911).ToString("yyyMMdd");
if (text)
oldday = db0.Apply.Single(x => x.apply_id == oldid).apply_datetime.AddYears(-1911).ToString("yyyMMdd");
if (DateTime.Now.AddYears(-1911).ToString("yyyMMdd") != oldday) //成立後序號重新由1開始計算
{
var item = db0.i_IDX.First(x => x.table_name == "Apply_sn");
item.IDX = 0;
var Async = db0.SaveChangesAsync();
Async.Wait();
}
string dt = DateTime.Now.AddYears(-1911).ToString("yyyMMdd");
string num = String.Format("{0:000}", GetNewId(ProcCore.Business.CodeTable.Apply_sn));
md.apply_sn = dt + num; //取得自訂sn
#endregion
#region 日期字串組合
string Tstr = "";
if (md.event_times) //判斷活動日期是否選擇一次進出
{
Tstr = (md.endDate).ToString("yyyy-MM-dd ");
DateTime ts = DateTime.Parse(Tstr + md.event_start + ":00:00");
DateTime te = DateTime.Parse(Tstr + md.event_end + ":00:00");
md.event_strattime = ts;
md.event_endtime = te;
//Jerry 加入申請日期可允許範圍
if (md.event_strattime > DateTime.Now.AddDays(CommWebSetup.Limit_Max_Apply_Days))
{
rAjaxResult.result = false;
rAjaxResult.message = "申請日期超過可允許範圍";
return defJSON(rAjaxResult);
}
}
#endregion
#region 地區字串分割
if (md.apply_id == 0)
md.apply_id = GetNewId(ProcCore.Business.CodeTable.Apply); //取得自訂id
//foreach (var area in md.event_area)
//{
// db0.EventArea.Add(new EventArea()
// {
// eventarea_id = GetNewId(ProcCore.Business.CodeTable.EventArea),
// apply_id = md.apply_id,
// code = int.Parse(area)
// });
//}
#endregion
#region 信件發送
string Body = getMailBody("ApplyEmail", md);//套用信件版面
string mailTitle = "桃園觀新藻礁生態系野生動物保護區入園申請";
string receiveMails = (string)open.getParmValue(ParmDefine.receiveMails);
string BccMails = (string)open.getParmValue(ParmDefine.BccMails);
Boolean mail;
mail = Mail_Send(md.apply_email, //寄信人
receiveMails.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries), //收信人
BccMails.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries), //收信人(密件副本)
mailTitle, //信件標題
Body, //信件內容
true); //是否為html格式
if (mail == false)
{
rAjaxResult.result = false;
rAjaxResult.message = "信箱號碼不正確或送信失敗";
return defJSON(rAjaxResult);
}
#endregion
db0.Apply.Add(md);
var getRows = db0.SaveChanges();
tx.Complete();
rAjaxResult.result = true;
rAjaxResult.id = md.apply_id;
}
catch (Exception ex)
{
rAjaxResult.result = false;
rAjaxResult.message = ex.Message;
}
}
return defJSON(rAjaxResult);
}
}