public static void AddIndex(string tableName, string columnName) { string index = string.Format("i_{0}_{1}", columnName.Remove(0, columnName.Length - 3), tableName); if (index.Length > 30) { index = index.Remove(29); } string sql = string.Format("CREATE INDEX {2} ON {0} ({1}) ", tableName, columnName, index); OracleHelper.Query(sql); }
public static List <string> getMapColName(string tableName) { List <string> result = new List <string>(); string sql = string.Format("select TABLE_COL_NAME from MDS_IMP_DATA_SCRIPT_MAP WHERE MDS_IMP_DATA_SCRIPT_RULE_ID IN(select FID from MDS_IMP_DATA_SCRIPT t where t.table_name = '{0}')", tableName); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { result.Add(dr[0].ToString()); } return(result); }
public static string getTablePK(string tableName) { string sql = string.Format(@"select m.column_name from user_constraints s, user_cons_columns m where upper(m.table_name)='{0}' and m.table_name=s.table_name and m.constraint_name=s.constraint_name and s.constraint_type='P'", tableName); DataSet ds = OracleHelper.Query(sql); if (ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0].Rows[0][0].ToString()); } return(""); }
public static Project getInfo(String FID) { Project result = null; string sql = String.Format("SELECT * FROM pm_task_info_view WHERE TASK_ID='{0}'", FID); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { result = Project.Parse(dr); } return(result); }
public static List <string> getTableNames() { List <string> result = new List <string>(); string sql = "select TABLE_NAME from user_tables "; DataSet ds = OracleHelper.Query(sql); foreach (DataRow row in ds.Tables[0].Rows) { result.Add(row[0].ToString()); } return(result); }
public static List <DataLog> getDistinctList(string projectID) { List <DataLog> result = new List <DataLog>(); string sql = string.Format("SELECT DISTINCT(typename), version,TEST_PROJECT_ID, OBJECT_TABLE FROM MDS_IMP_DATA_LOG WHERE TEST_PROJECT_ID='{0}'", projectID); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { DataLog item = DataLog.Parse(dr); result.Add(item); } return(result); }
public static DataScriptRule getInfo(string fid) { DataScriptRule result = null; string sql = string.Format("SELECT * FROM MDS_IMP_DATA_SCRIPT_RULE WHERE FID='{0}'", fid); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { DataScriptRule item = DataScriptRule.Parse(dr); result = item; } return(result); }
public static List <DataScriptRule> getList() { List <DataScriptRule> result = new List <DataScriptRule>(); string sql = "SELECT * FROM MDS_IMP_DATA_SCRIPT_RULE"; DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { DataScriptRule item = DataScriptRule.Parse(dr); result.Add(item); } return(result); }
public static List <DataScriptMap> getList(string mdsID) { List <DataScriptMap> result = new List <DataScriptMap>(); string sql = string.Format("SELECT * FROM MDS_IMP_DATA_SCRIPT_MAP WHERE MDS_IMP_DATA_SCRIPT_RULE_ID='{0}'", mdsID); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { DataScriptMap item = DataScriptMap.Parse(dr); result.Add(item); } return(result); }
public static string getSingleTableName(string projectID) { string sql = string.Format("SELECT OBJECT_TABLE FROM MDS_IMP_DATA_LOG WHERE TEST_PROJECT_ID='{0}' ORDER BY CREATION_DATE DESC", projectID); DataSet ds = OracleHelper.Query(sql); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { return(dt.Rows[0][0].ToString()); } } return(""); }
public static List <Project> getList() { List <Project> result = new List <Project>(); string sql = "SELECT * FROM pm_task_info_view"; DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { Project item = Project.Parse(dr); result.Add(item); } return(result); }
public static List <ObjtableInfo> getList() { List <ObjtableInfo> result = new List <ObjtableInfo>(); string sql = "SELECT * FROM MDS_IMP_OBJTABLE_INFO"; DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { ObjtableInfo item = ObjtableInfo.Parse(dr); result.Add(item); } return(result); }
public static void AddColumn(string tableName, string columnName, string columnType) { string sql = ""; switch (columnType) { case "number": sql = string.Format("alter table {0} add {1} number", tableName, columnName); break; default: sql = string.Format("alter table {0} add {1} NVARCHAR2(48)", tableName, columnName); break; } OracleHelper.Query(sql); }
/// <summary> /// 3.试验设备sql: (参数id为点击主表的任务id) /// </summary> private static List <resource> getResources(string id) { List <resource> result = new List <resource>(); string sql = string.Format(@"SELECT TT.ID,PD.RESOURCE_CODE,PD.RESOURCE_NAME, V.LOOKUP_NAME FROM pt6.PM_RESOURCE_DEFINE PD INNER JOIN TDM.TDM_TASK_RSRC_USER TT ON TT.DEVICE_ID = PD.ID LEFT JOIN pt6.SYS_LOOKUP_V V ON V.LOOKUP_CODE = PD.RESOURCE_TYPE WHERE TT.TASK_ID = '{0}' AND V.LOOKUP_TYPE = 'TDM_ RESOURCE_TYPE' AND TT.TYPE = 'R'", id); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { resource item = resource.Parse(dr); result.Add(item); } return(result); }
public static List <Structure> getTableStructure(string tableName) { List <Structure> result = new List <Structure>(); string sql = string.Format(@"select t.table_name,t.column_name,t.data_type,t.data_length,t.nullable,t.column_id,c.comments FROM user_tab_cols t, user_col_comments c WHERE upper(t.table_name)='{0}' and c.table_name=t.table_name and c.column_name=t.column_name and t.hidden_column='NO' order by t.column_id ", tableName); DataSet ds = OracleHelper.Query(sql); foreach (DataRow row in ds.Tables[0].Rows) { result.Add(Structure.Parse(row)); } return(result); }
public static List <string> getScriptCodes(string projectID) { List <string> result = new List <string>(); string sql = string.Format("select distinct TYPENAME from MDS_IMP_DATA_LOG WHERE TEST_PROJECT_ID='{0}'", projectID); DataSet ds = OracleHelper.Query(sql); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr[0] != DBNull.Value && (!string.IsNullOrEmpty(dr[0].ToString()))) { result.Add(dr[0].ToString()); } } } return(result); }
private static List <user> getUsers(string id) { List <user> result = new List <user>(); string sql = string.Format(@"SELECT TT.ID, SU.NAME AS USER_NAME,SU.NO AS USER_NO, SL.ROLE_NAME USER_ROSE FROM TDM.TDM_TASK_RSRC_USER TT INNER JOIN pt6.SYS_USER SU ON SU.ID = TT.USER_ID INNER JOIN pt6.SYS_USER_ROLE SR ON SR.SYS_USER_ID = SU.ID INNER JOIN pt6.SYS_ROLE SL ON SL.ID = SR.SYS_ROLE_ID WHERE TT.TASK_ID = '{0}' AND TT.TYPE = 'U'", id); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { user item = user.Parse(dr); result.Add(item); } return(result); }
private static List <resolver> getResolvers(string id) { List <resolver> result = new List <resolver>(); string sql = string.Format(@"with a as (select t.fid as resolver_id, t.mids_script_code resolver_code,t.script_type script_type,t.mids_script_name type_name from pt6.pm_task_info pt left join import.mds_imp_data_script t on instr(pt.resolver_task_id,t.fid)>0 where pt.id = '{0}') select a.resolver_id id,a.resolver_id,a.resolver_code,a.type_name,a.script_type,sa.attach_name from a left join pt6.sys_attachment sa on sa.parent_register_id = a.resolver_id" , id); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { resolver item = resolver.Parse(dr); result.Add(item); } return(result); }
public static List <TaskInfo> getList(string loginName) { List <TaskInfo> result = new List <TaskInfo>(); string sql = string.Format(@"with a as(select t.parent_id parent_id, min(t.plan_code_gantt) min_task_code from mpm.pm_task_info t where t.actual_e_date is null and (t.accept_state <> '3' or t.accept_state is null) and t.task_type = '1' and t.id in (select pti.parent_id from mpm.pm_task_info pti left join pt6.sys_user su on pti.user_id = su.id where pti.pm_task_type = '1' and (su.login_name='{0}' or exists( select 1 from tdm.tdm_task_rsrc_user ttru left join pt6.sys_user su1 on ttru.user_id = su1.id where ttru.task_id = pti.id and su1.login_name = '{0}' ))) --包含下达给试验部公布的工序 group by t.parent_id ), --查询所有任务下的按顺序应当执行的工序code对应的ID b as ( select a.parent_id,a.min_task_code,pti1.id from mpm.pm_task_info pti1,a where pti1.parent_id = a.parent_id and pti1.task_name not like '%_A' AND pti1.task_name NOT LIKE '%_B' and pti1.task_name not like '%_C' AND pti1.task_name NOT LIKE '%_D' ), --查询带ABCD的科研工序,只能查询上一未做的阶段工序 b1 as( select a.parent_id,a.min_task_code,pti1.id from mpm.pm_task_info pti1,a where pti1.parent_id = a.parent_id and ((pti1.TASK_NAME LIKE '%_A' AND nvl(pti1.ACTUAL_PROGRESS,0)<100) OR(pti1.TASK_NAME LIKE '%_B' AND nvl(pti1.ACTUAL_PROGRESS,0)<100 AND NOT EXISTS(SELECT 1 FROM mpm.PM_TASK_INFO WHERE PARENT_ID=pti1.PARENT_ID AND TASK_NAME LIKE '%_A' AND nvl(ACTUAL_PROGRESS,0)<100)) OR(pti1.TASK_NAME LIKE '%_C' AND nvl(pti1.ACTUAL_PROGRESS,0)<100 AND NOT EXISTS(SELECT 1 FROM mpm.PM_TASK_INFO WHERE PARENT_ID=pti1.PARENT_ID AND TASK_NAME LIKE '%_B' AND nvl(ACTUAL_PROGRESS,0)<100)) OR(pti1.TASK_NAME LIKE '%_D' AND nvl(pti1.ACTUAL_PROGRESS,0)<100 AND NOT EXISTS(SELECT 1 FROM mpm.PM_TASK_INFO WHERE PARENT_ID=pti1.PARENT_ID AND TASK_NAME LIKE '%_C' AND nvl(ACTUAL_PROGRESS,0)<100)) ) ), b2 as ( select parent_id,min_task_code,id from b union all select parent_id,min_task_code,id from b1 ), --查询该工序节点父节点和子节点的信息 d as( select distinct pti2.* from mpm.pm_task_info pti2,b2 where pti2.parent_id = b2.id or b2.parent_id = pti2.id or pti2.id=b2.id and pti2.actual_e_date is null and pti2.accept_state <> '3' ), --过滤工步不为登陆人的工步 c as ( select d.* from d left join pt6.sys_user su on d.user_id=su.id where (su.login_name='{0}' or exists( select 1 from tdm.tdm_task_rsrc_user ttru left join pt6.sys_user su1 on ttru.user_id = su1.id where ttru.task_id = d.id and su1.login_name = '{0}')) or d.task_type<>0 ) select c.id, --WBS/任务的唯一标识 decode(c.task_type, 0, '工步', 1, '工序', 2, '里程碑', 3,'任务','') task_type, --类型 pp.project_code, --项目code pp.project_name, --项目名称 c.plan_s_date, --计划开始时间 c.plan_e_date, --计划结束时间 c.actual_s_date, --实际开始时间 c.actual_e_date, --实际结束时间 (select attribute_07 from mpm.pm_task_info where instr(c.task_path,id)>0 and task_type='3')attribute_07, --任务项目编号 c.plan_code_gantt, --任务名称 c.task_name, --任务名称 c.task_code, --任务code su.login_name, --登入名 sdv.dept_name, --部门名称 syu.login_name as PLAN_MANAGER_name, --主管名称 c.INTERFACE_STATE , --任务状态 c.parent_id --父节点ID from c left join pt6.sys_user su on c.user_id = su.id --通过user_id左连接 系统用户表 拿到 用户登入名 left join pt6.sys_user syu on c.plan_manager_id = syu.id --通过plan_manager_id左连接 系统用户表 拿到 主管名称 left join pt6.sys_dept_v sdv on c.dept_id = sdv.id --通过dept_id左连接 部门表视图 拿到 部门名称 left join pt6.pm_project pp on c.pm_project_id = pp.id --通过pm_project_id左连接 项目结构信息表 拿到 项目code 项目名称 where c.task_status = '30' --状态 等于30(已下达) and nvl(c.ACTUAL_PROGRESS, 0) < 100 --并且 计划实际完成百分比%小于100 order by c.plan_code_gantt ", loginName); DataSet ds = OracleHelper.Query(sql); foreach (DataRow dr in ds.Tables[0].Rows) { TaskInfo item = TaskInfo.Parse(dr); result.Add(item); } foreach (var task in result) { task.parentName = ""; // 工步,工序,任务 var p1 = result.FirstOrDefault(it => it.id == task.parentId); TaskInfo p2 = null; //if (p1 != null) //{ // p2 = result.FirstOrDefault(it => it.id == p1.parentId); //} //if (p1 != null && p2 != null) //{ // task.parentName = string.Format("{0}", p2.taskName, p1.taskName); // // task.parentName = string.Format("{0}/{1}", p2.taskName, p1.taskName); //} //else if (p1 != null) { task.parentName = string.Format("{0}", p1.taskName); } task.parentName = task.taskName; } return(result); }
public static void CreateTable(string tableName, DataTable source) { var structures = getTableStructure(tableName); float f; if (structures.Count == 0) { // new StringBuilder ct = new StringBuilder(); ct.AppendFormat("create table {0} ", tableName); ct.AppendFormat("("); for (int i = 0; i < source.Columns.Count; i++) { bool isnum = false; if (float.TryParse(source.Rows[0][i].ToString(), out f)) { isnum = true; } else { try { // 是个16进制的数字 Convert.ToInt32(source.Rows[0][i].ToString(), 16); isnum = true; } catch { } } if (isnum) { ct.AppendFormat("COLUMN{0} NUMBER,", i); } else { ct.AppendFormat("COLUMN{0} NVARCHAR2(48),", i); } } ct.AppendFormat("PROJECTID NVARCHAR2(48),"); ct.AppendFormat("CREATED_BY NVARCHAR2(200),"); ct.AppendFormat("CREATION_DATE NVARCHAR2(48),"); ct.AppendFormat("LAST_UPDATED_BY NVARCHAR2(200),"); ct.AppendFormat("LAST_UPDATE_DATE NVARCHAR2(48),"); ct.AppendFormat("TASKTIMES NUMBER"); ct.AppendFormat(")"); OracleHelper.Query(ct.ToString()); string comment; for (int i = 0; i < source.Columns.Count; i++) { comment = string.Format("comment on column {0}.COLUMN{1} is '{2}'", tableName, i, source.Columns[i].ColumnName); OracleHelper.Query(comment); if (source.Columns[i].ColumnName == System.Configuration.ConfigurationManager.AppSettings["pk"]) { AddIndex(tableName, string.Format("COLUMN{0},TASKTIMES,PROJECTID", i)); } } comment = string.Format("comment on column {0}.PROJECTID is '项目ID'", tableName); OracleHelper.Query(comment); comment = string.Format("comment on column {0}.CREATED_BY is '创建人'", tableName); OracleHelper.Query(comment); comment = string.Format("comment on column {0}.CREATION_DATE is '创建时间'", tableName); OracleHelper.Query(comment); comment = string.Format("comment on column {0}.LAST_UPDATED_BY is '更新人'", tableName); OracleHelper.Query(comment); comment = string.Format("comment on column {0}.LAST_UPDATE_DATE is '更新时间'", tableName); OracleHelper.Query(comment); comment = string.Format("comment on column {0}.TASKTIMES is '试验次数'", tableName); OracleHelper.Query(comment); } else { // alert Hashtable newCol = new Hashtable(); foreach (DataColumn dc in source.Columns) { var st = structures.FirstOrDefault(it => it.Comments == dc.ColumnName); if (st == null) { newCol.Add(dc.ColumnName, source.Rows[0][dc].ToString()); } } int i = 0; string newColString; int columnCount = structures.Count(it => it.ColumnName.IndexOf("COLUMN") > -1); foreach (string key in newCol.Keys) { int index = columnCount + i; if (float.TryParse(newCol[key].ToString(), out f)) { newColString = string.Format("alter table {0} add COLUMN{1} number", tableName, index); } else { newColString = string.Format("alter table {0} add COLUMN{1} NVARCHAR2(48)", tableName, index); } OracleHelper.Query(newColString); string comment = string.Format("comment on column {0}.COLUMN{1} is '{2}'", tableName, index, key); OracleHelper.Query(comment); i++; } } }