public ActionResult GetColumns(XMLSettingVM vm)
{
Cache.DelCache("setedColumns");
Cache.DelCache("XMLMappings");
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);
}
}
// Father Tag Option
var fatherTag = new StringBuilder();
fatherTag.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-");
var jsonData = new
{
Options = options.ToString(),
FatherTagOptions = fatherTag.ToString()
};
return(Json(jsonData));
}
[OutputCache(NoStore = true, Duration = 0)] // 以防Server取得的是Cache，必須即時更新
public ActionResult ShowXML(XMLSettingVM vm)
{
// 限定同網站的Ajax專用
if (!Request.IsAjaxRequest())
{
return(Content("Fail"));
}
#region 取得暫存 Session XMLMappings
List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>();
string ColumnName = string.Empty;
try
{
xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings"));
}
catch { }
#endregion
#region 依指定SQL語句抓取資料
using (tblSQLSettingRepository setting = new tblSQLSettingRepository())
{
tblSQLSetting SQLSetting = setting.select(vm.SQLName);
if (SQLSetting != null)
{
using (DataAccess da = new DataAccess())
{
string sql = Func.SqlPlusTop(SQLSetting.SQLStatement, SQLSetting.DataRow);
Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sql);
DataTable dt = result.Item2;
XmlDocument xmlDoc = XmlProcess.GenerateXML(dt, xmlMappings);
//XmlDocument xmlDoc = new XmlDocument();
////根節點 只有1個
//var rootTag = xmlMappings.Where(x => string.IsNullOrEmpty(x.FatherTag)).First();
//XmlElement root = xmlDoc.CreateElement(rootTag.TagName);
//for (int i = 0; i < dt.Rows.Count; i++)
// appendXmlByRow(dt.Rows[i], xmlMappings, xmlDoc, root, rootTag.TagName, 1);
////addSubElement(xmlMappings, xmlDoc, root, rootTag.TagName, dt);
//xmlDoc.AppendChild(root);
model.XMLView = Server.HtmlEncode(Func.BeautifyXML(xmlDoc));
}
}
else
{
model.XMLView = "找不到指定的 SQL 設定!";
}
}
#endregion
return(PartialView("_XMLView", model));
}
public ActionResult Save(XMLSettingVM vm, string[] DateFormats)
{
if (string.IsNullOrEmpty(vm.XMLName))
{
vm.SaveResult += "請輸入 XML 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 XMLMappings
List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>();
string ColumnName = string.Empty;
try
{
xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings"));
}
catch (Exception ex) { }
#endregion
using (tblXMLSettingRepository setting = new tblXMLSettingRepository())
{
vm.SaveResult = setting.Save(vm.XMLName, vm.CustomerName, vm.SQLName, vm.FileName, vm.FileNameDateFormat, vm.UserID, userInfo.Account, xmlMappings);
if (vm.SaveResult.Equals("ok"))
{
return(RedirectToAction("Index"));
}
else
{
return(View("Edit", vm));
}
}
}
[OutputCache(NoStore = true, Duration = 0)] // 以防Server取得的是Cache，必須即時更新
public ActionResult ShowMappingData(XMLSettingVM vm)
{
// 限定同網站的Ajax專用
if (!Request.IsAjaxRequest())
{
return(Content("Fail"));
}
#region 讀入暫存 Session XMLMappings
List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>();;
try
{
xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings"));
}
catch { }
#endregion
model.XMLMappingDataRow = xmlMappings.OrderBy(x => x.Idx).ToList();;
return(PartialView("_XMLMappingRow", 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));
}
