/// <summary>
/// 查询 [模块与流程定义关联]
/// </summary>
/// <param name="CompanyID">创建公司ID</param>
/// <param name="DepartID">创建部门ID</param>
/// <param name="ModelCode">模块代码</param>
/// <param name="FlowType">0:审批流程，1：任务流程</param>
/// <param name="OrgType">1：代表部门ID为空，否则部门ID不为空</param>
/// <returns></returns>
public static List<FLOW_MODELFLOWRELATION_T> GetFlowByModelName(string CompanyID, string DepartID, string ModelCode, string FlowType, string OrgType)
{
List<FLOW_MODELFLOWRELATION_T> listRelation = new List<FLOW_MODELFLOWRELATION_T>();
IDataReader dr = null;
string sql = "";
try
{
sql = @" select * from FLOW_MODELFLOWRELATION_T where COMPANYID='{0}' and FLOWTYPE='{1}' and FLAG='1' and MODELCODE='{2}'";
if (OrgType != "1")
{
sql = sql + " and (DEPARTMENTID is null or DEPARTMENTID ='')";
}
else
{
sql = sql + " and DEPARTMENTID='{3}'";
}
sql = string.Format(sql, CompanyID, FlowType, ModelCode, DepartID);
#region
////OracleCommand cmd = con.CreateCommand();
////cmd.CommandText = sql;
//dr = cmd.ExecuteReader();
Tracer.Debug("FLOW_MODELFLOWRELATION_TDAL->GetFlowByModelName SQL语句：" + sql);
dr = dao.ExecuteReader(sql);
while (dr.Read())
{
#region FLOW_MODELFLOWRELATION_T
FLOW_MODELFLOWRELATION_T relation = new FLOW_MODELFLOWRELATION_T();
relation.COMPANYID = dr["COMPANYID"] == DBNull.Value ? null : dr["COMPANYID"].ToString();
relation.CREATECOMPANYID = dr["CREATECOMPANYID"] == DBNull.Value ? null : dr["CREATECOMPANYID"].ToString();
relation.CREATEDATE = (DateTime)dr["CREATEDATE"];
relation.CREATEDEPARTMENTID = dr["CREATEDEPARTMENTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
relation.CREATEPOSTID = dr["CREATEPOSTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
relation.CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? null : dr["CREATEUSERID"].ToString();
relation.CREATEUSERNAME = dr["CREATEUSERNAME"] == DBNull.Value ? null : dr["CREATEUSERNAME"].ToString();
relation.DEPARTMENTID = dr["DEPARTMENTID"] == DBNull.Value ? null : dr["DEPARTMENTID"].ToString();
relation.EDITDATE = dr["EDITDATE"] == DBNull.Value ? null : (DateTime?)dr["EDITDATE"];
relation.EDITUSERID = dr["EDITUSERID"] == DBNull.Value ? null : dr["EDITUSERID"].ToString();
relation.EDITUSERNAME = dr["EDITUSERNAME"] == DBNull.Value ? null : dr["EDITUSERNAME"].ToString();
relation.FLAG = dr["FLAG"] == DBNull.Value ? null : dr["FLAG"].ToString();
relation.FLOWTYPE = dr["FLOWTYPE"] == DBNull.Value ? null : dr["FLOWTYPE"].ToString();
relation.MODELFLOWRELATIONID = dr["MODELFLOWRELATIONID"] == DBNull.Value ? null : dr["MODELFLOWRELATIONID"].ToString();
relation.FLOW_FLOWDEFINE_T = new FLOW_FLOWDEFINE_T();
relation.FLOW_FLOWDEFINE_T.FLOWCODE = dr["FLOWCODE"] == DBNull.Value ? null : dr["FLOWCODE"].ToString();
listRelation.Add(relation);
break;
#endregion
}
dr.Close();
if (listRelation.Count > 0)
{
sql = @"select * from FLOW_FLOWDEFINE_T where FLOWCODE='" + listRelation[0].FLOW_FLOWDEFINE_T.FLOWCODE + "'";
//dr = cmd.ExecuteReader();
dr = dao.ExecuteReader(sql);
while (dr.Read())
{
#region FLOW_FLOWDEFINE_T
FLOW_FLOWDEFINE_T define = new FLOW_FLOWDEFINE_T();
define.CREATECOMPANYID = dr["CREATECOMPANYID"] == DBNull.Value ? null : dr["CREATECOMPANYID"].ToString();
define.CREATEDATE = (DateTime)dr["CREATEDATE"];
define.CREATEDEPARTMENTID = dr["CREATEDEPARTMENTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEPOSTID = dr["CREATEPOSTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? null : dr["CREATEUSERID"].ToString();
define.CREATEUSERNAME = dr["CREATEUSERNAME"] == DBNull.Value ? null : dr["CREATEUSERNAME"].ToString();
define.DESCRIPTION = dr["DESCRIPTION"] == DBNull.Value ? null : dr["DESCRIPTION"].ToString();
define.EDITDATE = dr["EDITDATE"] == DBNull.Value ? null : (DateTime?)dr["EDITDATE"];
define.EDITUSERID = dr["EDITUSERID"] == DBNull.Value ? null : dr["EDITUSERID"].ToString();
define.EDITUSERNAME = dr["EDITUSERNAME"] == DBNull.Value ? null : dr["EDITUSERNAME"].ToString();
define.FLOWCODE = dr["FLOWCODE"] == DBNull.Value ? null : dr["FLOWCODE"].ToString();
define.FLOWTYPE = dr["FLOWTYPE"] == DBNull.Value ? null : dr["FLOWTYPE"].ToString();
define.FLOWDEFINEID = dr["FLOWDEFINEID"] == DBNull.Value ? null : dr["FLOWDEFINEID"].ToString();
define.LAYOUT = dr["LAYOUT"] == DBNull.Value ? null : dr["LAYOUT"].ToString();
define.RULES = dr["RULES"] == DBNull.Value ? null : dr["RULES"].ToString();
define.XOML = dr["XOML"] == DBNull.Value ? null : dr["XOML"].ToString(); ;
listRelation[0].FLOW_FLOWDEFINE_T = define;
break;
#endregion
}
dr.Close();
}
#endregion
return listRelation;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
throw new Exception("GetFlowByModelName-->" + ex.Message);
}
}
/// <summary>
/// 新增流程定义
/// </summary>
/// <param name="conn">链接</param>
/// <param name="command">数据库命令</param>
/// <param name="define">流程定义</param>
/// <returns>bool</returns>
private bool AddFlow(OracleConnection conn, OracleCommand command, FLOW_FLOWDEFINE_T define)
{
try
{
string insSql = @"INSERT INTO FLOW_FLOWDEFINE_T (FLOWDEFINEID,FLOWCODE,DESCRIPTION,XOML,RULES,LAYOUT,FLOWTYPE,
CREATEUSERID,CREATEUSERNAME,CREATECOMPANYID,CREATEDEPARTMENTID,CREATEPOSTID,CREATEDATE,EDITUSERID,EDITUSERNAME,
EDITDATE,SYSTEMCODE,BUSINESSOBJECT,WFLAYOUT) VALUES (:FLOWDEFINEID,:FLOWCODE,:DESCRIPTION,:XOML,:RULES,:LAYOUT,
:FLOWTYPE,:CREATEUSERID,:CREATEUSERNAME,:CREATECOMPANYID,:CREATEDEPARTMENTID,:CREATEPOSTID,:CREATEDATE,:EDITUSERID,:EDITUSERNAME,
:EDITDATE,:SYSTEMCODE,:BUSINESSOBJECT,:WFLAYOUT)";
OracleParameter[] pageparm =
{
#region 参数
new OracleParameter(":FLOWDEFINEID",OracleType.NVarChar,100),
new OracleParameter(":FLOWCODE",OracleType.NVarChar,100),
new OracleParameter(":DESCRIPTION",OracleType.NVarChar,100),
new OracleParameter(":XOML",OracleType.Clob),
new OracleParameter(":RULES",OracleType.Clob),
new OracleParameter(":LAYOUT",OracleType.Clob),
new OracleParameter(":FLOWTYPE",OracleType.NVarChar,2),
new OracleParameter(":CREATEUSERID",OracleType.NVarChar,100),
new OracleParameter(":CREATEUSERNAME",OracleType.NVarChar,100),
new OracleParameter(":CREATECOMPANYID",OracleType.NVarChar,100),
new OracleParameter(":CREATEDEPARTMENTID",OracleType.NVarChar,100),
new OracleParameter(":CREATEPOSTID",OracleType.NVarChar,100),
new OracleParameter(":CREATEDATE",OracleType.DateTime),
new OracleParameter(":EDITUSERID",OracleType.NVarChar,100),
new OracleParameter(":EDITUSERNAME",OracleType.NVarChar,100),
new OracleParameter(":EDITDATE",OracleType.DateTime),
new OracleParameter(":SYSTEMCODE",OracleType.NVarChar,100),
new OracleParameter(":BUSINESSOBJECT",OracleType.NVarChar,100),
new OracleParameter(":WFLAYOUT",OracleType.Clob)
#endregion
};
pageparm[0].Value = GetValue(Guid.NewGuid().ToString().Replace("-", string.Empty));//流程定义ID
pageparm[1].Value = GetValue(define.FLOWCODE);//流程代码
pageparm[2].Value = GetValue(define.DESCRIPTION); ;//名称描述
pageparm[3].Value = GetValue(define.XOML);//模型文件
pageparm[4].Value = GetValue(define.RULES);//模型规则
pageparm[5].Value = GetValue(define.LAYOUT);//模型布局
pageparm[6].Value = GetValue(define.FLOWTYPE);//流程类型 -- 0:审批流程, 1:任务流程
pageparm[7].Value = GetValue(define.CREATEUSERID);//操作人员ID
pageparm[8].Value = GetValue(define.CREATEUSERNAME);//操作人员名
pageparm[9].Value = GetValue(define.CREATECOMPANYID);//创建公司ID
pageparm[10].Value = GetValue(define.CREATEDEPARTMENTID);//创建部门ID
pageparm[11].Value = GetValue(define.CREATEPOSTID);//创建岗位ID
pageparm[12].Value = GetValue(define.CREATEDATE);//创建时间
pageparm[13].Value = GetValue(define.EDITUSERID);//修改人ID
pageparm[14].Value = GetValue(define.EDITUSERNAME);//修改人用户名
pageparm[15].Value = GetValue(define.EDITDATE);//修改时间
pageparm[16].Value = GetValue(define.SYSTEMCODE);//业务系统:OA,HR,TM等
pageparm[17].Value = GetValue(define.BUSINESSOBJECT);//业务对象：各种申请报销单
pageparm[18].Value = GetValue(define.WFLAYOUT);//流程定义文件,把旧的ID换成新的ID
int n = MicrosoftOracle.ExecuteSQL(conn, command, insSql, pageparm);
if (n > 0)
{
return true;
}
return false;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 新增流程定义
/// </summary>
/// <param name="conn">链接</param>
/// <param name="command">数据库命令</param>
/// <param name="define">流程定义</param>
/// <returns>bool</returns>
private bool AddFlow(FLOW_FLOWDEFINE_T define)
{
try
{
// string insSql = @"INSERT INTO FLOW_FLOWDEFINE_T (FLOWDEFINEID,FLOWCODE,DESCRIPTION,XOML,RULES,LAYOUT,FLOWTYPE,
// CREATEUSERID,CREATEUSERNAME,CREATECOMPANYID,CREATEDEPARTMENTID,CREATEPOSTID,CREATEDATE,EDITUSERID,EDITUSERNAME,
// EDITDATE,SYSTEMCODE,BUSINESSOBJECT,WFLAYOUT) VALUES (FLOWDEFINEID,FLOWCODE,DESCRIPTION,XOML,RULES,LAYOUT,
// FLOWTYPE,CREATEUSERID,CREATEUSERNAME,CREATECOMPANYID,CREATEDEPARTMENTID,CREATEPOSTID,CREATEDATE,EDITUSERID,EDITUSERNAME,
// EDITDATE,SYSTEMCODE,BUSINESSOBJECT,WFLAYOUT)";
string insSql = @"INSERT INTO FLOW_FLOWDEFINE_T (FLOWDEFINEID,FLOWCODE,DESCRIPTION,XOML,RULES,LAYOUT,FLOWTYPE,
CREATEUSERID,CREATEUSERNAME,CREATECOMPANYID,CREATEDEPARTMENTID,CREATEPOSTID,CREATEDATE,EDITUSERID,EDITUSERNAME,
EDITDATE,SYSTEMCODE,BUSINESSOBJECT,WFLAYOUT) VALUES (@FLOWDEFINEID,@FLOWCODE,@DESCRIPTION,@XOML,@RULES,@LAYOUT,
@FLOWTYPE,@CREATEUSERID,@CREATEUSERNAME,@CREATECOMPANYID,@CREATEDEPARTMENTID,@CREATEPOSTID,@CREATEDATE,@EDITUSERID,@EDITUSERNAME,
@EDITDATE,@SYSTEMCODE,@BUSINESSOBJECT,@WFLAYOUT)";
Parameter[] pageparm =
{
#region 参数
new Parameter("@FLOWDEFINEID",null),
new Parameter("@FLOWCODE",null),
new Parameter("@DESCRIPTION",null),
new Parameter("@XOML",null),
new Parameter("@RULES",null),
new Parameter("@LAYOUT",null),
new Parameter("@FLOWTYPE",null),
new Parameter("@CREATEUSERID",null),
new Parameter("@CREATEUSERNAME",null),
new Parameter("@CREATECOMPANYID",null),
new Parameter("@CREATEDEPARTMENTID",null),
new Parameter("@CREATEPOSTID",null),
new Parameter("@CREATEDATE",null),
new Parameter("@EDITUSERID",null),
new Parameter("@EDITUSERNAME",null),
new Parameter("@EDITDATE",null),
new Parameter("@SYSTEMCODE",null),
new Parameter("@BUSINESSOBJECT",null),
new Parameter("@WFLAYOUT",null)
#endregion
};
pageparm[0].ParameterValue = GetValue(Guid.NewGuid().ToString().Replace("-", string.Empty));//流程定义ID
pageparm[1].ParameterValue = GetValue(define.FLOWCODE);//流程代码
pageparm[2].ParameterValue = GetValue(define.DESCRIPTION); ;//名称描述
pageparm[3].ParameterValue = GetValue(define.XOML);//模型文件
pageparm[4].ParameterValue = GetValue(define.RULES);//模型规则
pageparm[5].ParameterValue = GetValue(define.LAYOUT);//模型布局
pageparm[6].ParameterValue = GetValue(define.FLOWTYPE);//流程类型 -- 0:审批流程, 1:任务流程
pageparm[7].ParameterValue = GetValue(define.CREATEUSERID);//操作人员ID
pageparm[8].ParameterValue = GetValue(define.CREATEUSERNAME);//操作人员名
pageparm[9].ParameterValue = GetValue(define.CREATECOMPANYID);//创建公司ID
pageparm[10].ParameterValue = GetValue(define.CREATEDEPARTMENTID);//创建部门ID
pageparm[11].ParameterValue = GetValue(define.CREATEPOSTID);//创建岗位ID
pageparm[12].ParameterValue = DateTime.Now;//创建时间
pageparm[13].ParameterValue = GetValue(define.EDITUSERID);//修改人ID
pageparm[14].ParameterValue = GetValue(define.EDITUSERNAME);//修改人用户名
pageparm[15].ParameterValue = DateTime.Now; //修改时间
pageparm[16].ParameterValue = GetValue(define.SYSTEMCODE);//业务系统:OA,HR,TM等
pageparm[17].ParameterValue = GetValue(define.BUSINESSOBJECT);//业务对象：各种申请报销单
pageparm[18].ParameterValue = GetValue(define.WFLAYOUT);//流程定义文件,把旧的ID换成新的ID
ParameterCollection pras = new ParameterCollection();
foreach (var item in pageparm)
{
pras.Add(item);
}
int n = dao.ExecuteNonQuery(insSql,CommandType.Text,pras);
if (n > 0)
{
return true;
}
return false;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 流程模型定义
/// </summary>
/// <param name="con"></param>
/// <param name="CompanyID"></param>
/// <param name="ModelCode"></param>
/// <returns></returns>
public List<FLOW_FLOWDEFINE_T> GetFlowByModelName(string CompanyID, string ModelCode)
{
List<FLOW_FLOWDEFINE_T> listDefine = new List<FLOW_FLOWDEFINE_T>();
IDataReader dr = null;
try
{
#region
string sqlFLOW_MODELFLOWRELATION_T = @"select * from FLOW_MODELFLOWRELATION_T where COMPANYID='" + CompanyID + "' and FLAG='1'";
string sqlFLOW_FLOWDEFINE_T = @"select * from FLOW_FLOWDEFINE_T where MODELCODE='" + ModelCode + "'";
string sql = @"select FLOW_FLOWDEFINE_T.* from ("
+ sqlFLOW_MODELFLOWRELATION_T
+ ") FLOW_MODELFLOWRELATION_T join ("
+ sqlFLOW_FLOWDEFINE_T
+ ") FLOW_FLOWDEFINE_T on FLOW_MODELFLOWRELATION_T.FLOWCODE=FLOW_FLOWDEFINE_T.FLOWCODE";
////OracleCommand cmd = con.CreateCommand();
////cmd.CommandText = sql;
//dr = cmd.ExecuteReader();
Tracer.Debug("FLOW_FLOWDEFINE_TDAL->GetFlowByModelName SQL语句：" + sql);
dr = dao.ExecuteReader(sql);
while (dr.Read())
{
#region FLOW_FLOWDEFINE_T
FLOW_FLOWDEFINE_T define = new FLOW_FLOWDEFINE_T();
define.CREATECOMPANYID = dr["CREATECOMPANYID"] == DBNull.Value ? null : dr["CREATECOMPANYID"].ToString();
define.CREATEDATE = (DateTime)dr["CREATEDATE"];
define.CREATEDEPARTMENTID = dr["CREATEDEPARTMENTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEPOSTID = dr["CREATEPOSTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? null : dr["CREATEUSERID"].ToString();
define.CREATEUSERNAME = dr["CREATEUSERNAME"] == DBNull.Value ? null : dr["CREATEUSERNAME"].ToString();
define.DESCRIPTION = dr["DESCRIPTION"] == DBNull.Value ? null : dr["DESCRIPTION"].ToString();
define.EDITDATE = dr["EDITDATE"] == DBNull.Value ? null : (DateTime?)dr["EDITDATE"];
define.EDITUSERID = dr["EDITUSERID"] == DBNull.Value ? null : dr["EDITUSERID"].ToString();
define.EDITUSERNAME = dr["EDITUSERNAME"] == DBNull.Value ? null : dr["EDITUSERNAME"].ToString();
define.FLOWCODE = dr["FLOWCODE"] == DBNull.Value ? null : dr["FLOWCODE"].ToString();
define.FLOWTYPE = dr["FLOWTYPE"] == DBNull.Value ? null : dr["FLOWTYPE"].ToString();
define.FLOWDEFINEID = dr["FLOWDEFINEID"] == DBNull.Value ? null : dr["FLOWDEFINEID"].ToString();
define.LAYOUT = dr["LAYOUT"] == DBNull.Value ? null : dr["LAYOUT"].ToString();
define.RULES = dr["RULES"] == DBNull.Value ? null : dr["RULES"].ToString();
define.XOML = dr["XOML"] == DBNull.Value ? null : dr["XOML"].ToString(); ;
listDefine.Add(define);
break;
#endregion
}
dr.Close();
#endregion
return listDefine;
}
catch (Exception ex)
{
#region
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
throw new Exception("GetFlowByModelName-->" + ex.Message);
#endregion
}
}
