public void ProcessRequest(HttpContext context)
{
try
{
m_da = new Accudata.Data.Agent.DataAccessAgent();
JavaScriptSerializer serializer = new JavaScriptSerializer();
var data = serializer.DeserializeObject(context.Request.Form[0]);
Dictionary <string, object> dictData = (Dictionary <string, object>)data;
object[] objData = dictData.Values.ToArray()[3] as object[];
var cond_ID = 0; //條件序號流水號
var joinStep_ID = 0; //關聯組合序號流水號
//var joinColSeq = 0;//關聯輸出欄位序號流水號
var aggreStep_ID = 0; //聚合組合序號流水號
//var aggreColSeq = 0;//聚合輸出欄位序號流水號
var outputSeq_ID = 0; //輸出檔.欄位序號流水號
#region Json To Object
UDA_M_SEARCH ums = new UDA_M_SEARCH();
ums.SEARCH_NO = dictData.Values.ToArray()[0].ToString() == "" ? int.Parse(m_da.GetValue("select get_search_no() from dual", m_connectionStringKey).ToString()) : int.Parse(dictData.Values.ToArray()[0].ToString());
ums.SEARCH_NAME = dictData.Values.ToArray()[1].ToString();
ums.STATUS = dictData.Values.ToArray()[2].ToString();
ums.CREATE_TIME = DateTime.Now;
ums.USER_ID = "System";//待變更
ums.UPDATE_TIME = DateTime.Now;
List <UDA_D_SOURCE> udsList = new List <UDA_D_SOURCE>();
List <UDA_D_COLUMN> udcList = new List <UDA_D_COLUMN>();
List <UDA_D_CONDITION> udcnList = new List <UDA_D_CONDITION>();
List <UDA_D_JOIN> udjList = new List <UDA_D_JOIN>();
List <UDA_D_JOINCOLUMN> udjcList = new List <UDA_D_JOINCOLUMN>();
//List<UDA_D_JOINOUTPUT> udjoList = new List<UDA_D_JOINOUTPUT>();
List <UDA_D_AGGRATION> udaList = new List <UDA_D_AGGRATION>();
List <UDA_D_AGGRECOLUMN> udacList = new List <UDA_D_AGGRECOLUMN>();
//List<UDA_D_AGGRATIONOUTPUT> udaoList = new List<UDA_D_AGGRATIONOUTPUT>();
List <UDA_D_OUTPUT> udoList = new List <UDA_D_OUTPUT>();
List <UDA_D_OUTCOLUMN> udocList = new List <UDA_D_OUTCOLUMN>();
foreach (Dictionary <string, object> item in objData)
{
switch (item["StepFeatures"].ToString())
{
#region DataSRC
case "DataSRC":
UDA_D_SOURCE uds = new UDA_D_SOURCE();
foreach (string key in item.Keys)
{
PropertyInfo pi = uds.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(uds, ConvertType(pi, item[key]), null);
}
}
uds.SEARCH_NO = ums.SEARCH_NO;
uds.USER_ID = ums.USER_ID;
uds.UPDATE_TIME = ums.UPDATE_TIME;
udsList.Add(uds);
foreach (Dictionary <string, object> column in (object[])item["Columns"])
{
UDA_D_COLUMN udc = new UDA_D_COLUMN();
UDA_D_CONDITION udcn = new UDA_D_CONDITION();
foreach (string col in column.Keys)
{
PropertyInfo cpi = udc.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
if (cpi != null)
{
cpi.SetValue(udc, ConvertType(cpi, column[col]), null);
}
PropertyInfo cnpi = udcn.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
if (cnpi != null)
{
cnpi.SetValue(udcn, ConvertType(cnpi, column[col]), null);
}
}
udc.SEARCH_NO = ums.SEARCH_NO;
udc.STEP_ID = uds.STEP_ID;
udc.VER_NO = uds.VER_NO;
udc.DATABASE_NAME = uds.DATABASE_NAME;
udc.TABLE_NAME = uds.TABLE_NAME;
udc.USER_ID = ums.USER_ID;
udc.UPDATE_TIME = ums.UPDATE_TIME;
udcn.SEARCH_NO = ums.SEARCH_NO;
udcn.STEP_ID = uds.STEP_ID;
udcn.COLUMN_SEQ = udc.COLUMN_SEQ;
udcn.CONDTITION_ID = ++cond_ID;
udcn.USER_ID = ums.USER_ID;
udcn.UPDATE_TIME = ums.UPDATE_TIME;
udcList.Add(udc);
udcnList.Add(udcn);
}
break;
#endregion DataSRC
#region DataJoin
case "DataJoin":
UDA_D_JOIN udj = new UDA_D_JOIN();
foreach (string key in item.Keys)
{
PropertyInfo pi = udj.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(udj, ConvertType(pi, item[key]), null);
}
}
udj.SEARCH_NO = ums.SEARCH_NO;
udj.USER_ID = ums.USER_ID;
udj.UPDATE_TIME = ums.UPDATE_TIME;
udjList.Add(udj);
foreach (Dictionary <string, object> jColumn in (object[])item["JoinColumns"])
{
UDA_D_JOINCOLUMN udjc = new UDA_D_JOINCOLUMN();
udjc.SEARCH_NO = ums.SEARCH_NO;
udjc.STEP_ID = udj.STEP_ID;
udjc.JOIN_SET_ID = ++joinStep_ID;
udjc.JOIN_SOURCE_FROM = (int)((Dictionary <string, object>)jColumn["f1"])["CStep"];
udjc.JOIN_COLUMN_FROM = (int)((Dictionary <string, object>)jColumn["f1"])["ColumnSeq"];
udjc.JOIN_SOURCE_TO = (int)((Dictionary <string, object>)jColumn["f2"])["CStep"];
udjc.JOIN_COLUMN_TO = (int)((Dictionary <string, object>)jColumn["f2"])["ColumnSeq"];
udjc.USER_ID = ums.USER_ID;
udjc.UPDATE_TIME = ums.UPDATE_TIME;
udjcList.Add(udjc);
}
//foreach (Dictionary<string, object> sColumn in (object[])item["ShowColumns"])
//{
// UDA_D_JOINOUTPUT udjo = new UDA_D_JOINOUTPUT();
// foreach (string col in sColumn.Keys)
// {
// PropertyInfo cpi = udjo.GetType().GetProperties()
// .Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast<NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
// if (cpi != null)
// {
// cpi.SetValue(udjo, ConvertType(cpi, sColumn[col]), null);
// }
// }
// udjo.SEARCH_NO = ums.SEARCH_NO;
// udjo.STEP_ID = udj.STEP_ID;
// udjo.COLUMN_SEQ = ++joinColSeq;
// udjo.COLUMN_ALIAS_NAME = string.Format("{0}_{1}", udjo.STEP_ID, udjo.COLUMN_ALIAS_NAME);
// udjo.USER_ID = ums.USER_ID;
// udjo.UPDATE_TIME = ums.UPDATE_TIME;
// udjoList.Add(udjo);
//}
foreach (Dictionary <string, object> sColumn in (object[])item["ShowColumns"])
{
//取得於定義來源欄位檔內，相同定義編號且同名稱的欄位序號，並更新自訂欄位名稱及是否顯示為結果等欄位
foreach (var uItem in udcList)
{
if ((uItem.SEARCH_NO == ums.SEARCH_NO) && (uItem.STEP_ID.ToString() == sColumn["CStep"].ToString()) && (uItem.COLUMN_SEQ == (int)sColumn["ColumnSeq"]))
{
uItem.COLUMN_SHOW_NAME = sColumn["CName"].ToString();
uItem.IS_SHOW_FG = sColumn["IsShow"].ToString();
break;
}
}
}
break;
#endregion DataJoin
#region 自訂聚合、一般聚合
case "Customize":
case "CustSum":
case "CustAvg":
case "CustMax":
case "CustMin":
case "CustCount":
//var aggCode = string.Empty;
UDA_D_AGGRATION uda = new UDA_D_AGGRATION();
foreach (string key in item.Keys)
{
PropertyInfo pi = uda.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(uda, ConvertType(pi, item[key]), null);
}
}
uda.SEARCH_NO = ums.SEARCH_NO;
udaList.Add(uda);
foreach (Dictionary <string, object> cColumn in (object[])item["CustColumns"])
{
UDA_D_AGGRECOLUMN udac = new UDA_D_AGGRECOLUMN();
foreach (string col in cColumn.Keys)
{
PropertyInfo cpi = udac.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
if (cpi != null)
{
cpi.SetValue(udac, ConvertType(cpi, cColumn[col]), null);
}
}
udac.SEARCH_NO = ums.SEARCH_NO;
udac.STEP_ID = uda.STEP_ID;
udac.AGGRE_SET_ID = ++aggreStep_ID;
udac.USER_ID = ums.USER_ID;
udac.UPDATE_TIME = ums.UPDATE_TIME;
//if (item["StepFeatures"].ToString() != "Customize")
//{
// aggCode = udac.AGGREGATION_CODE;
//}
udacList.Add(udac);
}
//foreach (Dictionary<string, object> aColumn in (object[])item["AllColumns"])
//{
// UDA_D_AGGRATIONOUTPUT udao = new UDA_D_AGGRATIONOUTPUT();
// foreach (string col in aColumn.Keys)
// {
// PropertyInfo cpi = udao.GetType().GetProperties()
// .Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast<NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
// if (cpi != null)
// {
// cpi.SetValue(udao, ConvertType(cpi, aColumn[col]), null);
// }
// }
// udao.SEARCH_NO = ums.SEARCH_NO;
// udao.STEP_ID = uda.STEP_ID;
// udao.COLUMN_SEQ = ++aggreColSeq;
// udao.AGGREGATION_CODE = aggCode;
// udao.COLUMN_ALIAS_NAME = string.Format("{0}_{1}", udao.STEP_ID, udao.COLUMN_ALIAS_NAME);
// udao.USER_ID = ums.USER_ID;
// udao.UPDATE_TIME = ums.UPDATE_TIME;
// udaoList.Add(udao);
//}
break;
#endregion 自訂聚合、一般聚合
#region 資料輸出
case "Output":
UDA_D_OUTPUT udo = new UDA_D_OUTPUT();
foreach (string key in item.Keys)
{
PropertyInfo pi = udo.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(udo, ConvertType(pi, item[key]), null);
}
}
udo.SEARCH_NO = ums.SEARCH_NO;
udo.USER_ID = ums.USER_ID;
udo.UPDATE_TIME = ums.UPDATE_TIME;
udoList.Add(udo);
foreach (Dictionary <string, object> column in (object[])item["Columns"])
{
UDA_D_OUTCOLUMN udoc = new UDA_D_OUTCOLUMN();
foreach (string col in column.Keys)
{
PropertyInfo cpi = udoc.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == col).Count() > 0).DefaultIfEmpty(null).First();
if (cpi != null)
{
cpi.SetValue(udoc, ConvertType(cpi, column[col]), null);
}
}
udoc.SEARCH_NO = ums.SEARCH_NO;
udoc.STEP_ID = udo.STEP_ID;
udoc.COLUMN_SEQ = ++outputSeq_ID;
udoc.COLUMN_ALIAS_NAME = string.Format("{0}_{1}", udoc.STEP_ID, udoc.COLUMN_ALIAS_NAME);
udoc.USER_ID = ums.USER_ID;
udoc.UPDATE_TIME = ums.UPDATE_TIME;
udocList.Add(udoc);
}
break;
#endregion 資料輸出
}
}
#endregion Json To Object
#region Object To DataTable
SelectCommand[] cmds = new[]
{
new SelectCommand(string.Format("SELECT * FROM UDA_M_SEARCH WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_M_SEARCH", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_SOURCE WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_SOURCE", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_COLUMN WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_COLUMN", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_CONDITION WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_CONDITION", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_JOIN WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_JOIN", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_JOINCOLUMN WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_JOINCOLUMN", m_connectionStringKey),
//new SelectCommand(string.Format("SELECT * FROM UDA_D_JOINOUTPUT WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_JOINOUTPUT", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_AGGRATION WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_AGGRATION", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_AGGRECOLUMN WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_AGGRECOLUMN", m_connectionStringKey),
//new SelectCommand(string.Format("SELECT * FROM UDA_D_AGGRATIONOUTPUT WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_AGGRATIONOUTPUT", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_OUTPUT WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_OUTPUT", m_connectionStringKey),
new SelectCommand(string.Format("SELECT * FROM UDA_D_OUTCOLUMN WHERE SEARCH_NO = {0}", ums.SEARCH_NO), "UDA_D_OUTCOLUMN", m_connectionStringKey)
};
DataSet ds = m_da.GetDataSet(cmds);
//刪除DB資料
foreach (DataTable table in ds.Tables)
{
foreach (DataRow dr in table.Rows)
{
dr.Delete();
}
}
//使用者查詢定義檔
DataRow umsDr = ds.Tables["UDA_M_SEARCH"].NewRow();
umsDr.BeginEdit();
umsDr["SEARCH_NO"] = ums.SEARCH_NO;
umsDr["SEARCH_NAME"] = ums.SEARCH_NAME;
umsDr["NOTE"] = ums.NOTE;
umsDr["EMP_NO"] = ums.EMP_NO;
umsDr["STATUS"] = ums.STATUS;
umsDr["CREATE_TIME"] = ums.CREATE_TIME;
umsDr["USER_ID"] = ums.USER_ID;
umsDr["UPDATE_TIME"] = ums.UPDATE_TIME;
umsDr.EndEdit();
ds.Tables["UDA_M_SEARCH"].Rows.Add(umsDr);
//定義來源檔
foreach (var item in udsList)
{
DataRow udsDr = ds.Tables["UDA_D_SOURCE"].NewRow();
udsDr.BeginEdit();
udsDr["SEARCH_NO"] = item.SEARCH_NO;
udsDr["STEP_ID"] = item.STEP_ID;
udsDr["VER_NO"] = item.VER_NO;
udsDr["DATABASE_NAME"] = item.DATABASE_NAME;
udsDr["TABLE_NAME"] = item.TABLE_NAME;
udsDr["SOURCE_NAME"] = item.SOURCE_NAME;
udsDr["USER_ID"] = item.USER_ID;
udsDr["UPDATE_TIME"] = item.UPDATE_TIME;
udsDr.EndEdit();
ds.Tables["UDA_D_SOURCE"].Rows.Add(udsDr);
}
//定義來源欄位檔
foreach (var item in udcList)
{
DataRow udcDr = ds.Tables["UDA_D_COLUMN"].NewRow();
udcDr.BeginEdit();
udcDr["SEARCH_NO"] = item.SEARCH_NO;
udcDr["STEP_ID"] = item.STEP_ID;
udcDr["COLUMN_SEQ"] = item.COLUMN_SEQ;
udcDr["VER_NO"] = item.VER_NO;
udcDr["DATABASE_NAME"] = item.DATABASE_NAME;
udcDr["TABLE_NAME"] = item.TABLE_NAME;
udcDr["COLUMN_NAME"] = item.COLUMN_NAME;
udcDr["COLUMN_SHOW_NAME"] = item.COLUMN_SHOW_NAME;
udcDr["COLUMNTYPE_CODE"] = item.COLUMNTYPE_CODE;
udcDr["IS_SELECT_FG"] = item.IS_SELECT_FG;
udcDr["IS_SHOW_FG"] = item.IS_SHOW_FG;
udcDr["USER_ID"] = item.USER_ID;
udcDr["UPDATE_TIME"] = item.UPDATE_TIME;
udcDr.EndEdit();
ds.Tables["UDA_D_COLUMN"].Rows.Add(udcDr);
}
//定義來源篩選條件檔
foreach (var item in udcnList)
{
if (!string.IsNullOrEmpty(item.COND_CODE) && !string.IsNullOrEmpty(item.COND_TYPE))
{
DataRow udcnDr = ds.Tables["UDA_D_CONDITION"].NewRow();
udcnDr.BeginEdit();
udcnDr["SEARCH_NO"] = item.SEARCH_NO;
udcnDr["STEP_ID"] = item.STEP_ID;
udcnDr["COLUMN_SEQ"] = item.COLUMN_SEQ;
udcnDr["CONDTITION_ID"] = item.CONDTITION_ID;
udcnDr["COND_CODE"] = item.COND_CODE;
udcnDr["COND_TYPE"] = item.COND_TYPE;
udcnDr["COND_VALUE"] = item.COND_VALUE;
udcnDr["USER_ID"] = item.USER_ID;
udcnDr["UPDATE_TIME"] = item.UPDATE_TIME;
udcnDr.EndEdit();
ds.Tables["UDA_D_CONDITION"].Rows.Add(udcnDr);
}
}
//定義來源關聯檔
foreach (var item in udjList)
{
DataRow udjDr = ds.Tables["UDA_D_JOIN"].NewRow();
udjDr.BeginEdit();
udjDr["SEARCH_NO"] = item.SEARCH_NO;
udjDr["STEP_ID"] = item.STEP_ID;
udjDr["JOIN_TYPE"] = item.JOIN_TYPE;
udjDr["USER_ID"] = item.USER_ID;
udjDr["UPDATE_TIME"] = item.UPDATE_TIME;
udjDr.EndEdit();
ds.Tables["UDA_D_JOIN"].Rows.Add(udjDr);
}
//定義來源欄位關聯檔
foreach (var item in udjcList)
{
DataRow udjcDr = ds.Tables["UDA_D_JOINCOLUMN"].NewRow();
udjcDr.BeginEdit();
udjcDr["SEARCH_NO"] = item.SEARCH_NO;
udjcDr["STEP_ID"] = item.STEP_ID;
udjcDr["JOIN_SET_ID"] = item.JOIN_SET_ID;
udjcDr["JOIN_SOURCE_FROM"] = item.JOIN_SOURCE_FROM;
udjcDr["JOIN_COLUMN_FROM"] = item.JOIN_COLUMN_FROM;
udjcDr["JOIN_SOURCE_TO"] = item.JOIN_SOURCE_TO;
udjcDr["JOIN_COLUMN_TO"] = item.JOIN_COLUMN_TO;
udjcDr["USER_ID"] = item.USER_ID;
udjcDr["UPDATE_TIME"] = item.UPDATE_TIME;
udjcDr.EndEdit();
ds.Tables["UDA_D_JOINCOLUMN"].Rows.Add(udjcDr);
}
//定義關聯輸出欄位檔
//foreach (var item in udjoList)
//{
// DataRow udjoDr = ds.Tables["UDA_D_JOINOUTPUT"].NewRow();
// udjoDr.BeginEdit();
// udjoDr["SEARCH_NO"] = item.SEARCH_NO;
// udjoDr["STEP_ID"] = item.STEP_ID;
// udjoDr["COLUMN_SEQ"] = item.COLUMN_SEQ;
// udjoDr["SRC_STEP_ID"] = item.SRC_STEP_ID;
// udjoDr["SRC_COLUMN_SEQ"] = item.SRC_COLUMN_SEQ;
// udjoDr["COLUMN_SHOW_NAME"] = item.COLUMN_SHOW_NAME;
// udjoDr["COLUMN_ALIAS_NAME"] = item.COLUMN_ALIAS_NAME;
// udjoDr["COLUMNTYPE_CODE"] = item.COLUMNTYPE_CODE;
// udjoDr["IS_SELECT_FG"] = item.IS_SELECT_FG;
// udjoDr["USER_ID"] = item.USER_ID;
// udjoDr["UPDATE_TIME"] = item.UPDATE_TIME;
// udjoDr.EndEdit();
// ds.Tables["UDA_D_JOINOUTPUT"].Rows.Add(udjoDr);
//}
//定義聚合檔
foreach (var item in udaList)
{
DataRow udaDr = ds.Tables["UDA_D_AGGRATION"].NewRow();
udaDr.BeginEdit();
udaDr["SEARCH_NO"] = item.SEARCH_NO;
udaDr["STEP_ID"] = item.STEP_ID;
udaDr.EndEdit();
ds.Tables["UDA_D_AGGRATION"].Rows.Add(udaDr);
}
//定義聚合欄位檔
foreach (var item in udacList)
{
DataRow udacDr = ds.Tables["UDA_D_AGGRECOLUMN"].NewRow();
udacDr.BeginEdit();
udacDr["SEARCH_NO"] = item.SEARCH_NO;
udacDr["STEP_ID"] = item.STEP_ID;
udacDr["AGGRE_SET_ID"] = item.AGGRE_SET_ID;
udacDr["AGGREGATION_CODE"] = item.AGGREGATION_CODE;
udacDr["AGGREGATION_DESC"] = item.AGGREGATION_DESC;
udacDr["SRC_STEP_ID"] = item.SRC_STEP_ID;
udacDr["SRC_COLUMN_ID"] = item.SRC_COLUMN_ID;
udacDr["USER_ID"] = item.USER_ID;
udacDr["UPDATE_TIME"] = item.UPDATE_TIME;
udacDr["COLUMNTYPE_CODE"] = item.COLUMNTYPE_CODE;
udacDr.EndEdit();
ds.Tables["UDA_D_AGGRECOLUMN"].Rows.Add(udacDr);
}
//定義聚合輸出欄位檔
//foreach (var item in udaoList)
//{
// DataRow udaoDr = ds.Tables["UDA_D_AGGRATIONOUTPUT"].NewRow();
// udaoDr.BeginEdit();
// udaoDr["SEARCH_NO"] = item.SEARCH_NO;
// udaoDr["STEP_ID"] = item.STEP_ID;
// udaoDr["COLUMN_SEQ"] = item.COLUMN_SEQ;
// udaoDr["SRC_STEP_ID"] = item.SRC_STEP_ID;
// udaoDr["SRC_COLUMN_SEQ"] = item.SRC_COLUMN_SEQ;
// udaoDr["AGGREGATION_CODE"] = item.AGGREGATION_CODE;
// udaoDr["COLUMN_SHOW_NAME"] = item.COLUMN_SHOW_NAME;
// udaoDr["COLUMN_ALIAS_NAME"] = item.COLUMN_ALIAS_NAME;
// udaoDr["COLUMNTYPE_CODE"] = item.COLUMNTYPE_CODE;
// udaoDr["IS_SELECT_FG"] = item.IS_SELECT_FG;
// udaoDr["USER_ID"] = item.USER_ID;
// udaoDr["UPDATE_TIME"] = item.UPDATE_TIME;
// udaoDr.EndEdit();
// ds.Tables["UDA_D_AGGRATIONOUTPUT"].Rows.Add(udaoDr);
//}
//定義輸出檔
foreach (var item in udoList)
{
DataRow udoDr = ds.Tables["UDA_D_OUTPUT"].NewRow();
udoDr.BeginEdit();
udoDr["SEARCH_NO"] = item.SEARCH_NO;
udoDr["STEP_ID"] = item.STEP_ID;
udoDr["OUTPUT_NAME"] = item.OUTPUT_NAME;
udoDr["USER_ID"] = item.USER_ID;
udoDr["UPDATE_TIME"] = item.UPDATE_TIME;
udoDr.EndEdit();
ds.Tables["UDA_D_OUTPUT"].Rows.Add(udoDr);
}
//定義輸出欄位檔
foreach (var item in udocList)
{
DataRow udocDr = ds.Tables["UDA_D_OUTCOLUMN"].NewRow();
udocDr.BeginEdit();
udocDr["SEARCH_NO"] = item.SEARCH_NO;
udocDr["STEP_ID"] = item.STEP_ID;
udocDr["COLUMN_SEQ"] = item.COLUMN_SEQ;
udocDr["SRC_STEP_ID"] = item.SRC_STEP_ID;
udocDr["SRC_COLUMN_SEQ"] = item.SRC_COLUMN_SEQ;
udocDr["COLUMN_SHOW_NAME"] = item.COLUMN_SHOW_NAME;
udocDr["COLUMNTYPE_CODE"] = item.COLUMNTYPE_CODE;
udocDr["IS_SELECT_FG"] = item.IS_SELECT_FG;
udocDr["IS_SHOW_FG"] = item.IS_SHOW_FG;
udocDr["USER_ID"] = item.USER_ID;
udocDr["UPDATE_TIME"] = item.UPDATE_TIME;
udocDr.EndEdit();
ds.Tables["UDA_D_OUTCOLUMN"].Rows.Add(udocDr);
}
TableParam[] param;
param = new TableParam[]
{
new TableParam("UDA_M_SEARCH", "UDA_M_SEARCH", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO" }),
new TableParam("UDA_D_SOURCE", "UDA_D_SOURCE", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID" }),
new TableParam("UDA_D_COLUMN", "UDA_D_COLUMN", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID", "COLUMN_SEQ" }),
new TableParam("UDA_D_CONDITION", "UDA_D_CONDITION", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID", "COLUMN_SEQ", "CONDTITION_ID" }),
new TableParam("UDA_D_JOIN", "UDA_D_JOIN", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID" }),
new TableParam("UDA_D_JOINCOLUMN", "UDA_D_JOINCOLUMN", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID", "JOIN_SET_ID" }),
//new TableParam("UDA_D_JOINOUTPUT", "UDA_D_JOINOUTPUT", DataConcurrencyMode.None, null, null, new string[]{"SEARCH_NO", "STEP_ID", "COLUMN_SEQ"}),
new TableParam("UDA_D_AGGRATION", "UDA_D_AGGRATION", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID" }),
new TableParam("UDA_D_AGGRECOLUMN", "UDA_D_AGGRECOLUMN", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID", "AGGRE_SET_ID" }),
//new TableParam("UDA_D_AGGRATIONOUTPUT", "UDA_D_AGGRATIONOUTPUT", DataConcurrencyMode.None, null, null, new string[]{"SEARCH_NO", "STEP_ID", "COLUMN_SEQ"}),
new TableParam("UDA_D_OUTPUT", "UDA_D_OUTPUT", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID" }),
new TableParam("UDA_D_OUTCOLUMN", "UDA_D_OUTCOLUMN", DataConcurrencyMode.None, null, null, new string[] { "SEARCH_NO", "STEP_ID", "COLUMN_SEQ" })
};
m_da.Update(ds, param, m_connectionStringKey);
#endregion
}
catch (Exception)
{
throw;
}
}
public void ProcessRequest(HttpContext context)
{
try
{
m_da = new Accudata.Data.Agent.DataAccessAgent();
JavaScriptSerializer serializer = new JavaScriptSerializer();
var data = serializer.DeserializeObject(context.Request.Form[0]);
Dictionary <string, object> dictData = (Dictionary <string, object>)data;
object[] objData = dictData.Values.ToArray()[4] as object[];
var searchNo = dictData.Values.ToArray()[0].ToString();
var note = dictData.Values.ToArray()[1].ToString();
var userId = dictData.Values.ToArray()[2].ToString();
var empNo = dictData.Values.ToArray()[3].ToString();
List <UDA_D_JOBCONDITION> udjcList = new List <UDA_D_JOBCONDITION>();
List <UDA_D_JOB_OUTCONDITION> udjocList = new List <UDA_D_JOB_OUTCONDITION>();
foreach (Dictionary <string, object> item in objData)
{
if (item["Type"].ToString() == "UDA_D_CONDITIN")
{
UDA_D_JOBCONDITION udjc = new UDA_D_JOBCONDITION();
foreach (string key in item.Keys)
{
PropertyInfo pi = udjc.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(udjc, ConvertType(pi, item[key]), null);
}
}
udjcList.Add(udjc);
}
else
{
UDA_D_JOB_OUTCONDITION udjoc = new UDA_D_JOB_OUTCONDITION();
foreach (string key in item.Keys)
{
PropertyInfo pi = udjoc.GetType().GetProperties()
.Where(prop => prop.GetCustomAttributes(typeof(NameMappingAttribute), false).Cast <NameMappingAttribute>().Where(attr => attr.JsonName == key).Count() > 0).DefaultIfEmpty(null).First();
if (pi != null)
{
pi.SetValue(udjoc, ConvertType(pi, item[key]), null);
}
}
udjocList.Add(udjoc);
}
}
UpdateParameter[] ups = new UpdateParameter[5];
ups[0] = new UpdateParameter("i_SEARCH_NO", "OleDbType.Integer", 10, searchNo, "ParameterDirection.Input");
ups[1] = new UpdateParameter("i_EMP_NO", "OleDbType.VarChar", 30, empNo, "ParameterDirection.Input");
ups[2] = new UpdateParameter("i_USER_ID", "OleDbType.VarChar", 30, userId, "ParameterDirection.Input");
ups[3] = new UpdateParameter("i_NOTE", "OleDbType.VarChar", 100, note, "ParameterDirection.Input");
ups[4] = new UpdateParameter("o_JOB_NO", "OleDbType.Integer", 24, "", "ParameterDirection.Output");
//呼叫SP
string[] getData = m_da.Update("sp_create_new_job", ups, m_connectionStringKey);
//資料更新
List <string> uCmd = new List <string>();
foreach (var item in udjcList)
{
uCmd.Add(string.Format(@"UPDATE UDA_D_JOBCONDITION SET COND_VALUE = '{0}' WHERE JOB_NO = '{1}' AND STEP_ID = '{2}' AND COLUMN_SEQ = '{3}' AND CONDTITION_ID = '{4}'", item.COND_VALUE, getData[0], item.STEP_ID, item.COLUMN_SEQ, item.CONDTITION_ID));
}
m_da.Update(uCmd.ToArray(), m_connectionStringKey);
string agIP = WebConfigurationManager.AppSettings["agentIP"];
int agPort = int.Parse(WebConfigurationManager.AppSettings["agentPort"]);
Agent ag = new Agent(agIP, agPort);
ag.ExecuteJob(getData[0].ToString());
}
catch (Exception)
{
throw;
}
}
public void ProcessRequest(HttpContext context)
{
m_da = new Accudata.Data.Agent.DataAccessAgent();
JavaScriptSerializer serializer = new JavaScriptSerializer();
context.Response.ContentType = "application/json";
switch (context.Request.QueryString["Get"])
{
case "dataRevert":
#region SQL
var uda_d_column_sql = @"select DC.*,
NVL(SC.COLUMN_TITLE, SC.COLUMN_NAME) COLUMN_CNAME,
NVL(MST.TABLENAME_TITLE, MST.TABLE_NAME) AS TABLENAME_TITLE
from
(
select ver_no,
search_no,
database_name,
table_name,
column_name,
column_show_name,
columntype_code,
step_id,
column_seq,
is_select_fg,
is_show_fg
from uda_d_column dc
where search_no = '{0}'
and (step_id, column_seq) not in
(select src_step_id, src_column_id
from UDA_D_AGGRECOLUMN
where search_no = '{0}')
union all
select dc.ver_no,
da.search_no,
dc.database_name,
dc.table_name,
dc.column_name,
da.aggregation_desc column_show_name,
da.columntype_code,
da.src_step_id step_id,
da.src_column_id column_seq,
'Y' is_select_fg,
'Y' is_show_fg
from UDA_D_AGGRECOLUMN DA inner join uda_d_column dc on da.search_no = dc.search_no and da.src_step_id = dc.step_id and da.src_column_id = dc.column_seq
where da.search_no = '{0}'
order by step_id,column_seq
) DC
LEFT JOIN UDA_D_SYSCOLUMN SC
ON DC.VER_NO = SC.VER_NO
AND DC.DATABASE_NAME = SC.DATABASE_NAME
AND DC.TABLE_NAME = SC.TABLE_NAME
AND DC.COLUMN_NAME = SC.COLUMN_NAME
LEFT JOIN UDA_M_SYSTABLE MST
ON SC.VER_NO = MST.VER_NO
AND SC.DATABASE_NAME = MST.DATABASE_NAME
AND SC.TABLE_NAME = MST.TABLE_NAME
";
var uda_d_joincolumn_sql = @"SELECT DJC.*,
NVL(DCF.COLUMN_NAME, DOC.COLUMN_SHOW_NAME) F_COLUMN_NAME,
NVL(NVL(DCF.COLUMN_SHOW_NAME, DCF.COLUMN_NAME), DOC.COLUMN_SHOW_NAME) F_COLUMN_TITLE,
DCF.DATABASE_NAME F_DATABASE_NAME,
DCF.TABLE_NAME F_TABLE_NAME,
DCT.COLUMN_NAME T_COLUMN_NAME,
NVL(DCT.COLUMN_SHOW_NAME, DCT.COLUMN_NAME) T_COLUMN_TITLE,
DCT.DATABASE_NAME T_DATABASE_NAME,
DCT.TABLE_NAME T_TABLE_NAME
FROM UDA_D_JOINCOLUMN DJC
LEFT JOIN UDA_D_COLUMN DCF
ON DJC.SEARCH_NO = DCF.SEARCH_NO
AND DJC.JOIN_SOURCE_FROM = DCF.STEP_ID
AND DJC.JOIN_COLUMN_FROM = DCF.COLUMN_SEQ
LEFT JOIN UDA_D_COLUMN DCT
ON DJC.SEARCH_NO = DCT.SEARCH_NO
AND DJC.JOIN_SOURCE_TO = DCT.STEP_ID
AND DJC.JOIN_COLUMN_TO = DCT.COLUMN_SEQ
LEFT JOIN UDA_D_OUTCOLUMN DOC
ON DJC.SEARCH_NO = DOC.SEARCH_NO
AND DJC.JOIN_SOURCE_FROM = DOC.STEP_ID
AND DJC.JOIN_COLUMN_FROM = DOC.COLUMN_SEQ
LEFT JOIN UDA_D_OUTPUT DO
ON DOC.SEARCH_NO = DO.SEARCH_NO
AND DOC.STEP_ID = DO.STEP_ID
WHERE DJC.SEARCH_NO = '{0}'
";
#endregion SQL
string search_no = context.Request.QueryString["searchNo"].ToString();
DataSet dset = m_da.GetDataSet(new SelectCommand[] {
new SelectCommand(string.Format(@"SELECT * FROM (SELECT 3 TYPE,STEP_ID,'UDA_D_AGGRATION' T_NAME FROM UDA_D_AGGRATION WHERE SEARCH_NO ='{0}' UNION ALL SELECT 1 TYPE,STEP_ID,'UDA_D_SOURCE' T_NAME FROM UDA_D_SOURCE WHERE SEARCH_NO ='{0}' UNION ALL SELECT 2 TYPE,STEP_ID,'UDA_D_JOIN' T_NAME FROM UDA_D_JOIN WHERE SEARCH_NO ='{0}' UNION ALL SELECT 4 TYPE,STEP_ID,'UDA_D_OUTPUT' T_NAME FROM UDA_D_OUTPUT WHERE SEARCH_NO ='{0}') ORDER BY STEP_ID", search_no), "SEARCH_STEP", m_connectionStringKey), //依定義編號取得流程順序
new SelectCommand(string.Format(@"SELECT * FROM UDA_M_SEARCH WHERE SEARCH_NO ='{0}'", search_no), "UDA_M_SEARCH", m_connectionStringKey), //使用者查詢定義檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_SOURCE WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_SOURCE", m_connectionStringKey), //定義來源檔
new SelectCommand(string.Format(uda_d_column_sql, search_no), "UDA_D_COLUMN", m_connectionStringKey), //定義來源欄位檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_AGGRATION WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_AGGRATION", m_connectionStringKey), //定義聚合檔
new SelectCommand(string.Format(@"SELECT DAC.*, DC.COLUMN_NAME, DC.COLUMN_SHOW_NAME COLUMN_TITLE, DC.DATABASE_NAME, DC.TABLE_NAME FROM UDA_D_AGGRECOLUMN DAC INNER JOIN UDA_D_COLUMN DC ON DAC.SEARCH_NO = DC.SEARCH_NO AND DAC.SRC_STEP_ID = DC.STEP_ID AND DAC.SRC_COLUMN_ID = DC.COLUMN_SEQ WHERE DAC.SEARCH_NO ='{0}' ORDER BY DAC.SEARCH_NO, DAC.STEP_ID, DAC.AGGRE_SET_ID", search_no), "UDA_D_AGGRECOLUMN", m_connectionStringKey), //定義聚合欄位檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_JOIN WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_JOIN", m_connectionStringKey), //定義來源關聯檔
new SelectCommand(string.Format(uda_d_joincolumn_sql, search_no), "UDA_D_JOINCOLUMN", m_connectionStringKey), //定義來源欄位關聯檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_CONDITION WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_CONDITION", m_connectionStringKey), //定義來源篩選條件檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_OUTPUT WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_OUTPUT", m_connectionStringKey), //定義輸出檔
new SelectCommand(string.Format(@"SELECT * FROM UDA_D_OUTCOLUMN WHERE SEARCH_NO ='{0}'", search_no), "UDA_D_OUTCOLUMN", m_connectionStringKey) //定義輸出欄位檔
});
DataTable dtSs = dset.Tables["SEARCH_STEP"]; //定義步驟
DataTable dtMs = dset.Tables["UDA_M_SEARCH"]; //使用者查詢定義檔
DataIGA dIGA = new DataIGA();
dIGA.SearchNo = dtMs.Rows[0]["SEARCH_NO"].ToString();
dIGA.SearchName = dtMs.Rows[0]["SEARCH_NAME"].ToString();
dIGA.Status = int.Parse(dtMs.Rows[0]["STATUS"].ToString());
foreach (DataRow dr in dtSs.Rows)
{
DataRow drDT = dset.Tables[dr["T_NAME"].ToString()].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString()).FirstOrDefault();
switch (dr["TYPE"].ToString())
{
case "1":
DataSource ds = new DataSource();
ds.Step = drDT["STEP_ID"].ToString();
ds.StepFeatures = "DataSRC";
ds.Name = drDT["DATABASE_NAME"].ToString();
ds.TableName = drDT["TABLE_NAME"].ToString();
ds.TableCName = drDT["SOURCE_NAME"].ToString();
ds.VerNo = drDT["VER_NO"].ToString();
foreach (DataRow drDc in dset.Tables["UDA_D_COLUMN"].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString()))
{
Column col = new Column();
col.CStep = int.Parse(drDc["STEP_ID"].ToString());
col.Name = drDc["COLUMN_NAME"].ToString();
col.CName = drDc["COLUMN_CNAME"].ToString();
col.CType = drDc["COLUMNTYPE_CODE"].ToString();
col.IsSelected = drDc["IS_SELECT_FG"].ToString();
col.IsShow = drDc["IS_SHOW_FG"].ToString();
//col.DataSourceName = "";
col.ColumnSeq = int.Parse(drDc["COLUMN_SEQ"].ToString());
col.SRCStep = drDc["STEP_ID"].ToString();
foreach (DataRow drDcd in dset.Tables["UDA_D_CONDITION"].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString() && w["COLUMN_SEQ"].ToString() == drDc["COLUMN_SEQ"].ToString()))
{
col.ConditionDefine = drDcd["COND_CODE"].ToString();
col.ConditionType = drDcd["COND_TYPE"].ToString();
col.ConditionValue = drDcd["COND_VALUE"].ToString();
}
ds.Columns.Add(col);
}
dIGA.Step.Add(ds);
break;
case "2":
DataJoin dj = new DataJoin();
dj.Step = drDT["STEP_ID"].ToString();
dj.StepFeatures = "DataJoin";
dj.JoinType = drDT["JOIN_TYPE"].ToString();
string step1 = Convert.ToString(int.Parse(drDT["STEP_ID"].ToString()) - 1);
string step2 = Convert.ToString(int.Parse(drDT["STEP_ID"].ToString()) - 2);
foreach (DataRow drShow in dset.Tables["UDA_D_COLUMN"].AsEnumerable())
{
Column colS = new Column();
colS.CStep = int.Parse(drShow["STEP_ID"].ToString());
colS.Name = drShow["COLUMN_NAME"].ToString();
colS.CName = drShow["COLUMN_SHOW_NAME"].ToString();
colS.CType = drShow["COLUMNTYPE_CODE"].ToString();
//colS.ConditionDefine = "";
//colS.ConditionType = "";
//colS.ConditionValue = "";
colS.IsSelected = drShow["IS_SELECT_FG"].ToString();
colS.IsShow = drShow["IS_SHOW_FG"].ToString();
colS.DataSourceName = drShow["DATABASE_NAME"].ToString() + "." + drShow["TABLE_NAME"].ToString();
colS.ColumnSeq = int.Parse(drShow["COLUMN_SEQ"].ToString());
//colS.SRCStep = "";
colS.DataSourceCName = drShow["TABLENAME_TITLE"].ToString();
dj.ShowColumns.Add(colS);
}
foreach (DataRow drJoin in dset.Tables["UDA_D_JOINCOLUMN"].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString()))
{
Column c1 = new Column();
Column c2 = new Column();
c1.CStep = int.Parse(drJoin["JOIN_SOURCE_FROM"].ToString());
c1.Name = drJoin["F_COLUMN_NAME"].ToString();
c1.CName = drJoin["F_COLUMN_TITLE"].ToString();
//c1.CType = "";
//c1.ConditionDefine = "";
//c1.ConditionType = "";
//c1.ConditionValue = "";
c1.IsSelected = "Y";
//c1.IsShow = "";
c1.DataSourceName = drJoin["F_DATABASE_NAME"].ToString() + "." + drJoin["F_TABLE_NAME"].ToString();
c1.ColumnSeq = int.Parse(drJoin["JOIN_COLUMN_FROM"].ToString());
//c1.SRCStep = "";
c2.CStep = int.Parse(drJoin["JOIN_SOURCE_TO"].ToString());
c2.Name = drJoin["T_COLUMN_NAME"].ToString();
c2.CName = drJoin["T_COLUMN_TITLE"].ToString();
//c2.CType = "";
//c2.ConditionDefine = "";
//c2.ConditionType = "";
//c2.ConditionValue = "";
c2.IsSelected = "Y";
//c2.IsShow = "";
c2.DataSourceName = drJoin["T_DATABASE_NAME"].ToString() + "." + drJoin["T_TABLE_NAME"].ToString();
c2.ColumnSeq = int.Parse(drJoin["JOIN_COLUMN_TO"].ToString());
//c2.SRCStep = "";
dj.JoinColumns.Add(new { f1 = c1, f2 = c2 });
}
dIGA.Step.Add(dj);
break;
case "3":
Customize cust = new Customize();
cust.Step = drDT["STEP_ID"].ToString();
cust.StepFeatures = "Customize";
foreach (DataRow drDac in dset.Tables["UDA_D_AGGRECOLUMN"].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString()))
{
CustColumn cc = new CustColumn();
cc.CStep = int.Parse(drDac["SRC_STEP_ID"].ToString());
cc.Name = drDac["COLUMN_NAME"].ToString();
cc.CName = drDac["COLUMN_TITLE"].ToString();
cc.CType = drDac["COLUMNTYPE_CODE"].ToString();
cc.TableName = drDac["DATABASE_NAME"].ToString() + "." + drDac["TABLE_NAME"].ToString();
cc.CustType = drDac["AGGREGATION_CODE"].ToString();
cc.UPDName = drDac["AGGREGATION_DESC"].ToString();
cc.ColumnSeq = int.Parse(drDac["SRC_COLUMN_ID"].ToString());
cust.CustColumns.Add(cc);
}
dIGA.Step.Add(cust);
break;
case "4":
DataSource dsOP = new DataSource();
dsOP.Step = drDT["STEP_ID"].ToString();
dsOP.StepFeatures = "Output";
dsOP.TableCName = drDT["OUTPUT_NAME"].ToString();
foreach (DataRow drOp in dset.Tables["UDA_D_OUTCOLUMN"].AsEnumerable().Where(w => w["STEP_ID"].ToString() == dr["STEP_ID"].ToString()))
{
Column colOp = new Column();
colOp.CStep = int.Parse(drOp["STEP_ID"].ToString());
colOp.Name = drOp["COLUMN_SHOW_NAME"].ToString();
colOp.CName = drOp["COLUMN_SHOW_NAME"].ToString();
colOp.CType = drOp["COLUMNTYPE_CODE"].ToString();
//colOp.ConditionDefine = "";
//colOp.ConditionType = "";
//colOp.ConditionValue = "";
colOp.IsSelected = drOp["IS_SELECT_FG"].ToString();
//colOp.IsShow = "";
//colOp.DataSourceName = "";
colOp.ColumnSeq = int.Parse(drOp["SRC_COLUMN_SEQ"].ToString());
colOp.SRCStep = drOp["SRC_STEP_ID"].ToString();
dsOP.Columns.Add(colOp);
}
dIGA.Step.Add(dsOP);
break;
}
}
context.Response.Write(serializer.Serialize(dIGA));
break;
}
}
public void ProcessRequest(HttpContext context)
{
m_da = new Accudata.Data.Agent.DataAccessAgent();
JavaScriptSerializer serializer = new JavaScriptSerializer();
context.Response.ContentType = "application/json";
switch (context.Request.QueryString["Get"])
{
case "IGAList":
DataTable dtIGA = m_da.GetDataTable(@"SELECT ROWNUM, S.*
FROM(SELECT M.SEARCH_NO,
NVL(M.SEARCH_NAME, ' ') SEARCH_NAME,
C.CODE_NAME STATUS,
TO_CHAR(M.CREATE_TIME, 'yyyy/mm/dd') CREATE_TIME
FROM UDA_M_SEARCH M
LEFT JOIN UDA_CODE C
ON M.STATUS = C.CODE_ID
AND CODE_TYPE = 'SEARCH_STATUS'
ORDER BY C.CODE_NAME, M.CREATE_TIME DESC) S", m_connectionStringKey);
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
foreach (DataRow dr in dtIGA.Rows)
{
row = new Dictionary <string, object>();
foreach (DataColumn col in dtIGA.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
context.Response.Write(serializer.Serialize(rows));
break;
case "selDataSRC": //來源別
DataTable dt = m_da.GetDataTable("SELECT DATABASE_NAME NAME, DATA_INDEX S_ID, VER_NO FROM UDA_V_SYSDB", m_connectionStringKey);
List <Dictionary <string, object> > rows1 = new List <Dictionary <string, object> >();
Dictionary <string, object> row1;
foreach (DataRow dr in dt.Rows)
{
row1 = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
row1.Add(col.ColumnName, dr[col]);
}
rows1.Add(row1);
}
context.Response.Write(serializer.Serialize(rows1));
break;
case "tbDataTable": //資料庫資料表
string selSourceVal = context.Request.QueryString["selSourceVal"].ToString();
DataTable dtTable = m_da.GetDataTable(string.Format(@"SELECT TABLE_NAME T_NAME, NVL(TABLENAME_TITLE, TABLE_NAME) C_NAME, DATABASE_NAME, VER_NO FROM UDA_V_SYSTABLE WHERE DATABASE_NAME = '{0}'", selSourceVal), m_connectionStringKey);
List <Dictionary <string, object> > rows2 = new List <Dictionary <string, object> >();
Dictionary <string, object> row2;
foreach (DataRow dr in dtTable.Rows)
{
row2 = new Dictionary <string, object>();
foreach (DataColumn col in dtTable.Columns)
{
row2.Add(col.ColumnName, dr[col]);
}
rows2.Add(row2);
}
context.Response.Write(serializer.Serialize(rows2));
break;
case "tbDataColumns": //資料庫欄位
string tableNameVal = context.Request.QueryString["tableName"].ToString();
DataTable dtColumn = m_da.GetDataTable(string.Format(@"SELECT TABLE_NAME T_NAME, DATABASE_INDEX S_ID, COLUMN_NAME NAME, NVL(COLUMN_TITLE, COLUMN_NAME) C_NAME, COLUMNTYPE_CODE C_TYPE, VER_NO, COLUMN_SEQ SEQ FROM UDA_V_SYSCOLUMN S LEFT JOIN UDA_CODE C ON S.COLUMNTYPE_CODE = C.CODE_ID AND CODE_TYPE = 'COLUMN_TYPE_CODE' WHERE TABLE_NAME = '{0}' ORDER BY COLUMN_SEQ", tableNameVal), m_connectionStringKey);
List <Dictionary <string, object> > rows3 = new List <Dictionary <string, object> >();
Dictionary <string, object> row3;
foreach (DataRow dr in dtColumn.Rows)
{
row3 = new Dictionary <string, object>();
foreach (DataColumn col in dtColumn.Columns)
{
row3.Add(col.ColumnName, dr[col]);
}
rows3.Add(row3);
}
context.Response.Write(serializer.Serialize(rows3));
break;
case "udaCode": //代碼檔
DataTable dtCode = m_da.GetDataTable(@"SELECT CODE_TYPE, CODE_ID, CODE_NAME FROM UDA_CODE", m_connectionStringKey);
List <Dictionary <string, object> > rows4 = new List <Dictionary <string, object> >();
Dictionary <string, object> row4;
foreach (DataRow dr in dtCode.Rows)
{
row4 = new Dictionary <string, object>();
foreach (DataColumn col in dtCode.Columns)
{
row4.Add(col.ColumnName, dr[col]);
}
rows4.Add(row4);
}
context.Response.Write(serializer.Serialize(rows4));
break;
case "floatList": //浮動變數
string searchNoVal = context.Request.QueryString["searchNo"].ToString();
#region SQL
string floatSql = @"
SELECT DCD.*, DC.COLUMN_SHOW_NAME, DC.COLUMNTYPE_CODE, 'UDA_D_CONDITIN' type
FROM UDA_D_CONDITION DCD
INNER JOIN UDA_D_COLUMN DC
ON DCD.STEP_ID = DC.STEP_ID
AND DCD.COLUMN_SEQ = DC.COLUMN_SEQ
AND DCD.SEARCH_NO = DC.SEARCH_NO
WHERE DCD.SEARCH_NO = '{0}'
AND DCD.COND_TYPE = '1'
UNION ALL
SELECT DOCD.*, DOC.COLUMN_SHOW_NAME, DOC.COLUMNTYPE_CODE, 'UDA_D_OUTCONDITIN' type
FROM UDA_D_OUTCONDITION DOCD
INNER JOIN UDA_D_OUTCOLUMN DOC
ON DOCD.STEP_ID = DOC.STEP_ID
AND DOCD.COLUMN_SEQ = DOC.COLUMN_SEQ
AND DOCD.SEARCH_NO = DOC.SEARCH_NO
WHERE DOCD.SEARCH_NO = '{0}'
AND DOCD.COND_TYPE = '1'
";
#endregion
DataTable dtFloat = m_da.GetDataTable(string.Format(floatSql, searchNoVal), m_connectionStringKey);
List <Dictionary <string, object> > rows5 = new List <Dictionary <string, object> >();
Dictionary <string, object> row5;
foreach (DataRow dr in dtFloat.Rows)
{
row5 = new Dictionary <string, object>();
foreach (DataColumn col in dtFloat.Columns)
{
row5.Add(col.ColumnName, dr[col]);
}
rows5.Add(row5);
}
context.Response.Write(serializer.Serialize(rows5));
break;
//case "preview":
// #region 預覽用
// DataTable table = new DataTable();
// table.Columns.Add("Value1");
// table.Columns.Add("Value2");
// table.Columns.Add("Value3");
// table.Columns.Add("Value4");
// for (int i = 0; i < 10; i++)
// {
// DataRow dr = table.NewRow();
// dr.BeginEdit();
// dr["Value1"] = "a" + i.ToString();
// dr["Value2"] = "a" + i.ToString();
// dr["Value3"] = "a" + i.ToString();
// dr["Value4"] = "a" + i.ToString();
// dr.EndEdit();
// table.Rows.Add(dr);
// }
// List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
// Dictionary<string, object> row;
// foreach (DataRow dr in table.Rows)
// {
// row = new Dictionary<string, object>();
// foreach (DataColumn col in table.Columns)
// {
// row.Add(col.ColumnName, dr[col]);
// }
// rows.Add(row);
// }
// context.Response.Write(serializer.Serialize(rows));
// #endregion 預覽用
// break;
}
}
