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