public ActionResult GetColumns(ExcelSettingVM vm) { Cache.DelCache("ExcelsetedColumns"); Cache.DelCache("ExcelMappings"); var options = new StringBuilder(); options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = rep.getAllColumns(vm.SQLName).ToList(); foreach (var c in allColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { Options = options.ToString() }; return(Json(jsonData)); }
public ActionResult DeleteMapping(ExcelSettingVM vm) { #region 從暫存 Session ExcelMappings 刪除 List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>();; string ColumnName = string.Empty; try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch { } tblExcelMapping mapping = ExcelMappings.Find(x => x.ColumnName.Equals(vm.ColumnName, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; ExcelMappings.Remove(mapping); } Cache.SetLimitedCache("ExcelMappings", ExcelMappings); #endregion #region 從暫存 Session ExcelsetedColumns 刪除 List <tblSQLColumns> ExcelsetedColumns = new List <tblSQLColumns>();; try { ExcelsetedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("ExcelsetedColumns")); } catch { } tblSQLColumns column = ExcelsetedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { ExcelsetedColumns.Remove(column); } Cache.SetLimitedCache("ExcelsetedColumns", ExcelsetedColumns); #endregion var options = new StringBuilder(); options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = rep.getAllColumns(vm.SQLName).ToList(); IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(ExcelsetedColumns, new ColumnComparer()); foreach (var c in unsetColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { Options = options.ToString() }; return(Json(jsonData)); }
public ActionResult Save(ExcelSettingVM vm, string[] DateFormats) { if (string.IsNullOrEmpty(vm.ExcelName)) { vm.SaveResult += "請輸入 Excel Name!\r\n"; } if (string.IsNullOrEmpty(vm.CustomerName)) { vm.SaveResult += "請輸入 Customer Name!\r\n"; } if (string.IsNullOrEmpty(vm.SQLName)) { vm.SaveResult += "請選擇 SQL Name!\r\n"; } vm.FileNameDateFormat = string.Join(",", DateFormats); if (!string.IsNullOrEmpty(vm.SaveResult)) { return(View("Edit", vm)); } #region 取得暫存 Session ExcelMappings List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>(); string ColumnName = string.Empty; try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch (Exception ex) { } #endregion using (tblExcelSettingRepository setting = new tblExcelSettingRepository()) { vm.SaveResult = setting.Save(vm.ExcelName, vm.CustomerName, vm.SQLName, vm.FileName, vm.FileNameDateFormat, vm.UserID, userInfo.Account, ExcelMappings); if (vm.SaveResult.Equals("ok")) { return(RedirectToAction("Index")); } else { return(View("Edit", vm)); } } }
[OutputCache(NoStore = true, Duration = 0)] // 以防Server取得的是Cache,必須即時更新 public ActionResult ShowMappingData(ExcelSettingVM vm) { // 限定同網站的Ajax專用 if (!Request.IsAjaxRequest()) { return(Content("Fail")); } #region 讀入暫存 Session ExcelMappings List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>();; try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch { } #endregion model.ExcelMappingDataRow = ExcelMappings.OrderBy(x => x.SheetName).ThenBy(x => x.X).ToList(); return(PartialView("_ExcelMappingRow", model)); }
public ActionResult InsertMapping(ExcelSettingVM vm) { List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>(); string ColumnName = string.Empty; #region 寫入暫存 Session ExcelMappings try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch { } // 判斷 Column Name 是否有重複 if (string.IsNullOrEmpty(vm.ColumnName) || !vm.ColumnName.Equals(vm.newColumnName, StringComparison.OrdinalIgnoreCase)) { tblExcelMapping map = ExcelMappings.Find(x => x.ColumnName.Equals(vm.newColumnName, StringComparison.OrdinalIgnoreCase)); if (map != null) { var result = new { status = "Column Name 已存在!", }; return(Json(result)); } } // 判斷 X 是否 >0 if (vm.X <= 0) { var result = new { status = "X 起始值為 1", }; return(Json(result)); } else { tblExcelMapping map = ExcelMappings.Find(x => x.ColumnName != vm.ColumnName && x.X == vm.X); if (map != null) { var result = new { status = "X 重複!", }; return(Json(result)); } } // 更新時 將舊暫存資料刪除 tblExcelMapping mapping = ExcelMappings.Find(x => x.ColumnName.Equals(vm.ColumnName, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; ExcelMappings.Remove(mapping); } // 將新的設定值寫入 mapping = new tblExcelMapping() { ColumnName = vm.newColumnName, FieldName = vm.FieldName ?? string.Empty, DefaultValue = vm.DefaultValue, SheetName = vm.SheetName, X = vm.X, DataType = vm.DataType.GetDescription(), NewLineChar = vm.NewLineChar, CanRepeat = vm.CanRepeat }; ExcelMappings.Add(mapping); Cache.SetLimitedCache("ExcelMappings", ExcelMappings); #endregion #region 寫入暫存 Session ExcelsetedColumns List <tblSQLColumns> ExcelsetedColumns = new List <tblSQLColumns>();; try { ExcelsetedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("ExcelsetedColumns")); } catch { } // 更換指定欄位時 if (!ColumnName.Equals(vm.FieldName)) { // 更新時 將原指定的欄位刪除 tblSQLColumns column = ExcelsetedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { ExcelsetedColumns.Remove(column); } // 記錄 新指定的欄位 if (!string.IsNullOrEmpty(vm.FieldName)) { tblSQLColumns setedColumn = new tblSQLColumns() { SQLName = vm.SQLName, ColumnName = vm.FieldName }; ExcelsetedColumns.Add(setedColumn); } } Cache.SetLimitedCache("ExcelsetedColumns", ExcelsetedColumns); #endregion var options = new StringBuilder(); options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = rep.getAllColumns(vm.SQLName).ToList(); IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(ExcelsetedColumns, new ColumnComparer()); foreach (var c in unsetColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { status = "ok", Options = options.ToString() }; return(Json(jsonData)); }