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