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