//查詢後的顯示資料
public string ReturnTB(ReturnDBResult ReturnDBResult)
{
string ReturnTB = "";
DataSet ReturnData = new DataSet();
if (ReturnDBResult.ReturnDS.Tables.Count > 0)
{
if (ReturnDBResult.ReturnDS.Tables[0].Rows.Count > 0)
{
ReturnData = ReturnDBResult.ReturnDS;
ReturnTB += "<table class='table table-bordered ' style='width: auto;' >";
for (int j = 0; j < ReturnData.Tables[0].Columns.Count; j++)
{
if (j == 0)
ReturnTB += "<thead><tr><td>" + ReturnData.Tables[0].Columns[j].ColumnName + "</td>";
else if (j == ReturnData.Tables[0].Columns.Count - 1)
ReturnTB += "<td>" + ReturnData.Tables[0].Columns[j].ColumnName + "</td></tr></thead>";
else
ReturnTB += "<td>" + ReturnData.Tables[0].Columns[j].ColumnName + "</td>";
}
for (int i = 0; i < ReturnData.Tables[0].Rows.Count; i++)
{
if (i==0)
{
ReturnTB += "<tbody>";
}
for (int j = 0; j < ReturnData.Tables[0].Columns.Count; j++)
{
if (j == 0)
ReturnTB += "<tr><td>" + Convert.ToString(ReturnData.Tables[0].Rows[i][ReturnData.Tables[0].Columns[j].ColumnName]) + "</td>";
else if (j == ReturnData.Tables[0].Columns.Count - 1)
ReturnTB += "<td>" + Convert.ToString(ReturnData.Tables[0].Rows[i][ReturnData.Tables[0].Columns[j].ColumnName]) + "</td></tr>";
else
ReturnTB += "<td>" + Convert.ToString(ReturnData.Tables[0].Rows[i][ReturnData.Tables[0].Columns[j].ColumnName]) + "</td>";
}
if (i == ReturnData.Tables[0].Rows.Count -1)
{
ReturnTB += "</tbody>";
}
}
ReturnTB += "</table> ";
}
}
return ReturnTB;
}
//資料查詢
public JsonResult IndexDataJson(string select, string view, string where, string orderby, string iseasyquery, string strquery)
{
ReturnResult ReturnResult = new ReturnResult();
ReturnDBResult ReturnDBResult = new ReturnDBResult();
if (NeedSn(view, where))
{
ReturnResult.ReturnMsgNo = "MQLD0001";
ReturnResult.ReturnMsg = "此查詢必須要有SN";
return Json(ReturnResult, JsonRequestBehavior.AllowGet);
}
ReturnDBResult = ViewQueryLog(select, view, where, orderby, iseasyquery, strquery);
ReturnResult.ReturnMsgNo = Convert.ToString(ReturnDBResult.ReturnMsgNo);
ReturnResult.ReturnMsg = ReturnDBResult.ReturnMsg;
ReturnResult.ReturnTB = ReturnTB(ReturnDBResult);
return Json(ReturnResult, JsonRequestBehavior.AllowGet);
}
//資料View查詢內容
public ReturnDBResult ViewQueryLog(string select, string view, string where, string orderby, string iseasyquery, string strquery) //取得資料
{
ReturnDBResult ReturnResult = new ReturnDBResult();
InValue = "ViewQueryLog";
if (MyCardQueryLog.Properties.Settings.Default.In34.ToUpper() == "TRUE")
{
MyCardQueryLogWcf.Service1Client MyCardQueryLogWcf = new MyCardQueryLogWcf.Service1Client();
MyCardQueryLogWcf.ReturnResult ReturnValueB2B = new MyCardQueryLogWcf.ReturnResult();
try
{
ReturnValueB2B = MyCardQueryLogWcf.ViewQueryLog(select, view, where, orderby, iseasyquery, strquery);
ReturnResult.ReturnMsgNo = ReturnValueB2B.ReturnMsgNo;
ReturnResult.ReturnMsg = ReturnValueB2B.ReturnMsg;
ReturnResult.ReturnDS = ReturnValueB2B.ReturnDS;
}
catch (Exception ex)
{
OutValue = "|回應|" + "Exception|" + ex.ToString();
ErrorLog(InValue + OutValue);
ReturnResult.ReturnMsgNo = -99;
ReturnResult.ReturnMsg = "系統發生錯誤";
}
}
else
{
MyCardQueryLogWcfB2B.Service1Client MyCardQueryLogWcfB2B = new MyCardQueryLogWcfB2B.Service1Client();
MyCardQueryLogWcfB2B.ReturnResult ReturnValueB2B = new MyCardQueryLogWcfB2B.ReturnResult();
try
{
MyCardQueryLogWcfB2B.Endpoint.Binding.ReceiveTimeout = new TimeSpan(0, 2, 0);
ReturnValueB2B = MyCardQueryLogWcfB2B.ViewQueryLog(select, view, where, orderby, iseasyquery, strquery);
ReturnResult.ReturnMsgNo = ReturnValueB2B.ReturnMsgNo;
ReturnResult.ReturnMsg = ReturnValueB2B.ReturnMsg;
ReturnResult.ReturnDS = ReturnValueB2B.ReturnDS;
}
catch (Exception ex)
{
OutValue = "|回應|" + "Exception|" + ex.ToString();
ErrorLog(InValue + OutValue);
ReturnResult.ReturnMsgNo = -99;
ReturnResult.ReturnMsg = "系統發生錯誤";
}
}
return ReturnResult;
}
//資料View查詢內容描述
public ReturnDBResult ViewQueryNote(string view) //取得資料內容描述
{
ReturnDBResult ReturnResult = new ReturnDBResult();
if (MyCardQueryLog.Properties.Settings.Default.In34.ToUpper() == "TRUE")
{
MyCardQueryLogWcf.Service1Client MyCardQueryLogWcf = new MyCardQueryLogWcf.Service1Client();
MyCardQueryLogWcf.ReturnResult ReturnValueB2B = new MyCardQueryLogWcf.ReturnResult();
InValue = "QueryDBTableNote";
try
{
ReturnValueB2B = MyCardQueryLogWcf.QueryDBTableNote(view);
ReturnResult.ReturnMsgNo = ReturnValueB2B.ReturnMsgNo;
ReturnResult.ReturnMsg = ReturnValueB2B.ReturnMsg;
ReturnResult.ReturnDS = ReturnValueB2B.ReturnDS;
}
catch (Exception ex)
{
OutValue = "|回應|" + "Exception|" + ex.ToString();
ErrorLog(InValue + OutValue);
ReturnResult.ReturnMsgNo = -99;
ReturnResult.ReturnMsg = "系統發生錯誤";
}
}
else
{
MyCardQueryLogWcfB2B.Service1Client MyCardQueryLogWcfB2B = new MyCardQueryLogWcfB2B.Service1Client();
MyCardQueryLogWcfB2B.ReturnResult ReturnValueB2B = new MyCardQueryLogWcfB2B.ReturnResult();
InValue = "QueryDBTableNote";
try
{
ReturnValueB2B = MyCardQueryLogWcfB2B.QueryDBTableNote(view);
ReturnResult.ReturnMsgNo = ReturnValueB2B.ReturnMsgNo;
ReturnResult.ReturnMsg = ReturnValueB2B.ReturnMsg;
ReturnResult.ReturnDS = ReturnValueB2B.ReturnDS;
}
catch (Exception ex)
{
OutValue = "|回應|" + "Exception|" + ex.ToString();
ErrorLog(InValue + OutValue);
ReturnResult.ReturnMsgNo = -99;
ReturnResult.ReturnMsg = "系統發生錯誤";
}
}
return ReturnResult;
}
//查詢VIEW欄位
public JsonResult ChangeTableDataJson(string select, string view, string where, string orderby, string iseasyquery, string strquery)
{
ReturnResult ReturnResult = new ReturnResult();
ReturnDBResult ReturnDBResult = new ReturnDBResult();
ReturnDBResult = ViewQueryLog(select, view, where, orderby, iseasyquery, strquery);
ReturnResult.ReturnMsgNo = Convert.ToString(ReturnDBResult.ReturnMsgNo);
ReturnResult.ReturnMsg = ReturnDBResult.ReturnMsg;
//描述
ReturnDBResult ReturnDBResultNote = new ReturnDBResult();
ReturnDBResultNote = ViewQueryNote(view);
//string ColumnName = "";
StringBuilder JsonString = new StringBuilder();
if (ReturnDBResult.ReturnDS.Tables.Count > 0)
{
if (ReturnDBResult.ReturnDS.Tables[0].Columns.Count > 0)
{
JsonString.Append("[");
for (int i = 0; i < ReturnDBResult.ReturnDS.Tables[0].Columns.Count; i++)
{
if (i == 0)
JsonString.Append("{");//JsonString.Append("{");
else
JsonString.Append(",{");//JsonString.Append(",{");
string Temp = "";
if (ReturnDBResultNote.ReturnDS.Tables.Count > 0 )
{
if (ReturnDBResultNote.ReturnDS.Tables[0].Rows.Count > 0 )
{
for (int j = 0; j < ReturnDBResultNote.ReturnDS.Tables[0].Rows.Count; j++)
{
if (Convert.ToString(ReturnDBResultNote.ReturnDS.Tables[0].Rows[j]["objname"]) == Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Columns[i].ColumnName))
{
Temp = Convert.ToString(ReturnDBResultNote.ReturnDS.Tables[0].Rows[j]["value"]);
break;
}
}
}
}
JsonString.Append("\"ColumnName\":\"" + ReturnDBResult.ReturnDS.Tables[0].Columns[i].ColumnName + "\",\"ColumnNameNote\":\"" + Temp + "\""); //'ColumnName':
JsonString.Append("}");
}
JsonString.Append("]");
}
}
ReturnResult.ReturnTB = JsonString.ToString();
return Json(ReturnResult, JsonRequestBehavior.AllowGet);
}
public JsonResult IndexDDLDataJson(string DBName)//取得下拉Table資料
{
ReturnDDLResult ReturnDDLResult = new ReturnDDLResult();
ReturnDBResult ReturnDBResult = new ReturnDBResult();
ReturnDBResult = ViewQueryTableViewLog(DBName);
ReturnDDLResult.ReturnMsgNo = Convert.ToString(ReturnDBResult.ReturnMsgNo);
ReturnDDLResult.ReturnMsg = ReturnDBResult.ReturnMsg;
//處理NOTE
ReturnDBResult ReturnDBResultNote = new ReturnDBResult();
ReturnDBResultNote = ViewQueryDBOrViewNote(DBName);
List<Table> ListTable = new List<Table>();
if (ReturnDBResult.ReturnDS.Tables.Count > 0)
{
if (ReturnDBResult.ReturnDS.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ReturnDBResult.ReturnDS.Tables[0].Rows.Count; i++)
{
Table Table = new Table();
Table.TableName = Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_CATALOG"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_SCHEMA"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_NAME"]);
Table.TableNote = Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_CATALOG"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_SCHEMA"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_NAME"]);
if (ReturnDBResultNote.ReturnDS.Tables.Count > 0)
{
if (ReturnDBResultNote.ReturnDS.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ReturnDBResultNote.ReturnDS.Tables[0].Rows.Count; j++)
{
if (Convert.ToString(ReturnDBResultNote.ReturnDS.Tables[0].Rows[j]["ViewTableName"]) == Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_NAME"]))
{
Table.TableNote = Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_CATALOG"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_SCHEMA"]) + "." + Convert.ToString(ReturnDBResult.ReturnDS.Tables[0].Rows[i]["TABLE_NAME"]) + " " + Convert.ToString(ReturnDBResultNote.ReturnDS.Tables[0].Rows[j]["Note"]);
break;
}
}
}
}
ListTable.Add(Table);
}
}
}
ReturnDDLResult.ReturnData = ListTable;
return Json(ReturnDDLResult, JsonRequestBehavior.AllowGet);
}
