/// <summary> /// 依 SQL Namq 取得 欄位 /// </summary> /// <param name="SQLName"></param> /// <returns></returns> public static IEnumerable <SelectListItem> ColumnsOptionBySQLName(string SQLName) { List <SelectListItem> items = new List <SelectListItem>(); items.Add(new SelectListItem { Text = "-Please Select-", Value = "" }); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { var columns = rep.getAllColumns(SQLName); foreach (var c in columns) { items.Add(new SelectListItem { Text = c.ColumnName, Value = c.ColumnName }); } } return(items); }
public ActionResult Load(string id) { Cache.DelCache("setedColumns"); Cache.DelCache("XMLMappings"); if (string.IsNullOrEmpty(id)) { return(View("Edit", model)); } model.ViewStatus = "E"; string XMLName = id; using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { model.UnsetColumns = rep.getAllColumns(model.SQLName).ToList(); //var options = new StringBuilder(); //options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblXMLSettingRepository setRep = new tblXMLSettingRepository()) using (tblXMLMappingRepository xmlRep = new tblXMLMappingRepository()) { tblXMLSetting setting = setRep.get(XMLName); IEnumerable <tblXMLMapping> mapping = xmlRep.get(XMLName); model.XMLName = setting.XMLName; model.SQLName = setting.SQLName; model.CustomerName = setting.CustomerName; model.FileName = setting.FileName; model.FileNameDateFormat = setting.FileNameDateFormat; model.UserID = (string.IsNullOrEmpty(setting.UserId)) ? "" : setting.UserId.Substring(1, setting.UserId.Length - 2); foreach (var m in mapping) { model.XMLMappingDataRow.Add(new tblXMLMapping() { TagName = m.TagName, FieldName = m.FieldName, DefaultValue = m.DefaultValue, FatherTag = m.FatherTag, Idx = m.Idx, CanRepeat = m.CanRepeat }); } using (tblSQLColumnsRepository colRep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = colRep.getAllColumns(model.SQLName).ToList(); List <tblSQLColumns> setedColumns = new List <tblSQLColumns>();; foreach (var c in mapping) { if (!string.IsNullOrEmpty(c.FieldName)) { setedColumns.Add(new tblSQLColumns() { SQLName = model.SQLName, ColumnName = c.FieldName }); } } model.SetedColumns = setedColumns; IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(setedColumns, new ColumnComparer()); model.UnsetColumns = unsetColumns.ToList(); } } } Cache.SetLimitedCache("XMLMappings", model.XMLMappingDataRow); Cache.SetLimitedCache("setedColumns", model.SetedColumns); return(View("Edit", model)); }
public ActionResult DeleteMapping(XMLSettingVM vm) { #region 從暫存 Session XMLMappings 刪除 List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>();; string ColumnName = string.Empty; try { xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings")); } catch { } tblXMLMapping mapping = xmlMappings.Find(x => x.TagName.Equals(vm.TagName, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; xmlMappings.Remove(mapping); } Cache.SetLimitedCache("XMLMappings", xmlMappings); // Father Tag Option var fatherTag = new StringBuilder(); fatherTag.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); foreach (var m in xmlMappings) { fatherTag.AppendFormat("<option value='{0}'>{1}</option>", m.TagName, m.TagName); } #endregion #region 從暫存 Session setedColumns 刪除 List <tblSQLColumns> setedColumns = new List <tblSQLColumns>();; try { setedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("setedColumns")); } catch { } tblSQLColumns column = setedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { setedColumns.Remove(column); } Cache.SetLimitedCache("setedColumns", setedColumns); #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(setedColumns, new ColumnComparer()); foreach (var c in unsetColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { Options = options.ToString(), FatherTagOptions = fatherTag.ToString() }; return(Json(jsonData)); }
public ActionResult InsertMapping(XMLSettingVM vm) { List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>(); string ColumnName = string.Empty; int idx = -1; #region 寫入暫存 Session XMLMappings try { xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings")); } catch { } // 判斷 Tag Name 是否有重複 if (string.IsNullOrEmpty(vm.TagName) || !vm.TagName.Equals(vm.newTagName, StringComparison.OrdinalIgnoreCase)) { tblXMLMapping map = xmlMappings.Find(x => x.TagName.Equals(vm.newTagName, StringComparison.OrdinalIgnoreCase) && x.FatherTag.Equals(vm.FatherTag, StringComparison.OrdinalIgnoreCase)); if (map != null) { var result = new { status = "Tag Name 已存在!", }; return(Json(result)); } } // 更新時 將舊暫存資料刪除 tblXMLMapping mapping = xmlMappings.Find(x => x.TagName.Equals(vm.TagName, StringComparison.OrdinalIgnoreCase) && x.FatherTag.Equals(vm.FatherTag, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; idx = mapping.Idx; xmlMappings.Remove(mapping); } // 將新的設定值寫入 mapping = new tblXMLMapping() { TagName = vm.newTagName, FieldName = vm.FieldName ?? string.Empty, DefaultValue = vm.DefaultValue, FatherTag = vm.FatherTag ?? string.Empty, Idx = (idx < 0) ? xmlMappings.Count + 1 : idx, CanRepeat = vm.CanRepeat }; xmlMappings.Add(mapping); Cache.SetLimitedCache("XMLMappings", xmlMappings); // Father Tag Option var fatherTag = new StringBuilder(); fatherTag.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); foreach (var m in xmlMappings) { fatherTag.AppendFormat("<option value='{0}'>{1}</option>", m.TagName, m.TagName); } #endregion #region 寫入暫存 Session setedColumns List <tblSQLColumns> setedColumns = new List <tblSQLColumns>();; try { setedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("setedColumns")); } catch { } // 更換指定欄位時 if (!ColumnName.Equals(vm.FieldName)) { // 更新時 將原指定的欄位刪除 tblSQLColumns column = setedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { setedColumns.Remove(column); } // 記錄 新指定的欄位 if (!string.IsNullOrEmpty(vm.FieldName)) { tblSQLColumns setedColumn = new tblSQLColumns() { SQLName = vm.SQLName, ColumnName = vm.FieldName }; setedColumns.Add(setedColumn); } } Cache.SetLimitedCache("setedColumns", setedColumns); #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(setedColumns, 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(), FatherTagOptions = fatherTag.ToString() }; return(Json(jsonData)); }
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)); }