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