/// <summary>
/// 查询流程图定义的条件字符串
/// </summary>
/// <param name="orderType">订单类型</param>
/// <param name="approverInt">审批人</param>
public string GetWorkFlowsLine_OrderQuerySQL(WorkFlowsHandleDAO.OrderType orderType, int approverInt)
{
string sqlStr = string.Format("Select AutoId from BS_WorkFlows where WorkFlowsType = {0} and IsNull(Enabled, 0) = 1", (int)orderType);
DataTable workFlowsTable = BaseSQL.GetTableBySql(sqlStr);
if (workFlowsTable.Rows.Count == 0)
{
return("");
}
int workFlowsIdInt = DataTypeConvert.GetInt(workFlowsTable.Rows[0]["AutoId"]);
sqlStr = string.Format("select AutoId, Enabled from BS_WorkFlowsLine where WorkFlowsId = {0} and LineType = {1}", workFlowsIdInt, (int)WorkFlowsHandleDAO.LineType.审批);
DataTable lineTable = BaseSQL.GetTableBySql(sqlStr);
if (lineTable.Rows.Count == 0)
{
return("");
}
if (DataTypeConvert.GetInt(lineTable.Rows[0]["Enabled"]) == 1)
{
int lineIdInt = DataTypeConvert.GetInt(lineTable.Rows[0]["AutoId"]);
string loginIdStr = "";
string roleNoStr = "";
QueryUserInfo_LoginIdAndRoleNo(approverInt, ref loginIdStr, ref roleNoStr);
sqlStr = string.Format("select MultiLevelApprover, Condition from BS_WorkFlowsLineHandle as wfHandle join BS_WorkFlowsLineCondition as cond on wfHandle.ConditionId = cond.AutoId where wfHandle.LineId = {0} and ((LineHandleCate = 0 and HandleOwner = '{1}') or (LineHandleCate = 1 and HandleOwner = '{2}'))", lineIdInt, loginIdStr, roleNoStr);
DataTable wfHandleTable = BaseSQL.GetTableBySql(sqlStr);
WorkFlowsHandleDAO.QueryOrderViewName viewName = (WorkFlowsHandleDAO.QueryOrderViewName)orderType;
WorkFlowsHandleDAO.QueryOrderPrimaryKey primaryKey = (WorkFlowsHandleDAO.QueryOrderPrimaryKey)orderType;
sqlStr = "";
foreach (DataRow conditonRow in wfHandleTable.Rows)
{
string tempStr = DataHandler.SQLStringReplaceHandle(DataTypeConvert.GetString(conditonRow["Condition"]));
if (tempStr == "")
{
tempStr = " 1=1";
}
sqlStr += string.Format("select * from {0} where {1} and {2} in (select DataNo from BS_WorkFlowsDataCurrentNode as curNode where ISNULL(IsEnd,0)= 0 and CurrentNodeId in (select NodeId from BS_WorkFlowsLine where AutoId = {3}) and ApproverLevel ={4}) Union ", viewName, tempStr, primaryKey, lineIdInt, conditonRow["MultiLevelApprover"]);
}
if (sqlStr.Length > 6)
{
return(sqlStr.Substring(0, sqlStr.Length - 6));
}
else
{
return(string.Format("Select * from {0} where 1=2", viewName));
}
}
else
{
return("");
}
}
/// <summary>
/// 查询用户的工作流信息列表
/// </summary>
/// <param name="queryDataTable">要填充的数据表</param>
public void QueryUserWorkFlows_Table(DataTable queryDataTable)
{
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
cmd.CommandText = string.Format("select * from F_QueryUserWorkFlows_Internal('{0}', '{1}')", SystemInfo.user.LoginID, SystemInfo.user.RoleNo);
DataTable wfInternalTable = BaseSQL.GetTableBySql(cmd);
DataTable wfTypeInternalTable = wfInternalTable.DefaultView.ToTable(true, "WorkFlowsType", "LineId", "LineType");
foreach (DataRow wfTypeRow in wfTypeInternalTable.Rows)
{
int workFlowsTypeInt = DataTypeConvert.GetInt(wfTypeRow["WorkFlowsType"]);
int lineIdInt = DataTypeConvert.GetInt(wfTypeRow["LineId"]);
int lineTypeInt = DataTypeConvert.GetInt(wfTypeRow["lineType"]);
WorkFlowsHandleDAO.QueryOrderViewName viewName = (WorkFlowsHandleDAO.QueryOrderViewName)workFlowsTypeInt;
WorkFlowsHandleDAO.QueryOrderPrimaryKey primaryKey = (WorkFlowsHandleDAO.QueryOrderPrimaryKey)workFlowsTypeInt;
DataRow[] userWFRows = wfInternalTable.Select(string.Format("WorkFlowsType = {0} and LineId = {1} and LineType = {2}", workFlowsTypeInt, lineIdInt, lineTypeInt));
string sqlStr = "";
int approverInt = 1;
switch (lineTypeInt)
{
case (int)LineType.审批:
approverInt = -1;
break;
}
foreach (DataRow userRow in userWFRows)
{
string tempStr = DataHandler.SQLStringReplaceHandle(DataTypeConvert.GetString(userRow["Condition"]));
if (tempStr == "")
{
tempStr = " 1=1";
}
sqlStr += string.Format("select curNode.*,{0} as WorkFlowsType,{1} as LineType,{2} as LevelNodeId,{0} as NextType from BS_WorkFlowsDataCurrentNode as curNode where ISNULL(IsEnd,0)=0 and DataNo in (select {3} from {4} where {5}) and CurrentNodeId in (select NodeId from BS_WorkFlowsLine where AutoId = {6}) and (ApproverLevel={7} or {8}={7}) Union All ", workFlowsTypeInt, lineTypeInt, userRow["LevelNodeId"], primaryKey, viewName, tempStr, userRow["LineId"], userRow["MultiLevelApprover"], approverInt);
}
if (sqlStr.Length > 10)
{
cmd.CommandText = sqlStr.Substring(0, sqlStr.Length - 10);
SqlDataAdapter adapterList = new SqlDataAdapter(cmd);
adapterList.Fill(queryDataTable);
}
}
string whereSqlStr_Between = "";
cmd.CommandText = string.Format("select * from F_QueryUserWorkFlows_Between('{0}', '{1}')", SystemInfo.user.LoginID, SystemInfo.user.RoleNo);
DataTable wfBetweenTable = BaseSQL.GetTableBySql(cmd);
foreach (DataRow wfTypeRow in wfBetweenTable.Rows)
{
int workFlowsTypeInt = DataTypeConvert.GetInt(wfTypeRow["WorkFlowsType"]);
int nextWorkFlowsTypeInt = DataTypeConvert.GetInt(wfTypeRow["NextWorkFlowsType"]);
//int lineType = DataTypeConvert.GetInt(wfTypeRow["LineType"]);
//lineType = lineType == 1 ? 0 : lineType;
int lineType = 0;
WorkFlowsHandleDAO.QueryOrderViewName viewName = (WorkFlowsHandleDAO.QueryOrderViewName)workFlowsTypeInt;
WorkFlowsHandleDAO.QueryOrderPrimaryKey primaryKey = (WorkFlowsHandleDAO.QueryOrderPrimaryKey)workFlowsTypeInt;
string tempStr = DataHandler.SQLStringReplaceHandle(DataTypeConvert.GetString(wfTypeRow["Condition"]));
if (tempStr == "")
{
tempStr = " 1=1";
}
whereSqlStr_Between += string.Format("select curNode.*,{0} as WorkFlowsType,{1} as LineType,{2} as LevelNodeId,{3} as NextType from BS_WorkFlowsDataCurrentNode as curNode where ISNULL(IsEnd,0)=1 and ISNULL(NextHandleEnd,0)=0 and DataNo in (select {4} from {5} where {6}) Union All ", workFlowsTypeInt, lineType, -1, nextWorkFlowsTypeInt, primaryKey, viewName, tempStr);
}
if (whereSqlStr_Between.Length > 10)
{
cmd.CommandText = whereSqlStr_Between.Substring(0, whereSqlStr_Between.Length - 10);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(queryDataTable);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 订单流程图是否有权限
/// </summary>
/// <param name="orderType">订单类型</param>
/// <param name="lineType">操作类型</param>
/// <param name="orderNoDictionary">登记单号</param>
private int OrderWorkFlowsIsPower(OrderType orderType, LineType lineType, string orderNoStr)
{
using (SqlConnection conn = new SqlConnection(BaseSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("", conn, trans);
cmd.CommandText = string.Format("Select AutoId, Enabled from BS_WorkFlows where WorkFlowsType = {0}", (int)orderType);
DataTable workFlowsTable = BaseSQL.GetTableBySql(cmd);
if (workFlowsTable.Rows.Count == 0)
{
return(0);
}
else if (DataTypeConvert.GetInt(workFlowsTable.Rows[0]["Enabled"]) != 1)
{
return(0);
}
int workFlowsIdInt = DataTypeConvert.GetInt(workFlowsTable.Rows[0]["AutoId"]);
int approverLevelInt = 1;
//查询当前操作所属的连接线信息
cmd.CommandText = string.Format("select line.*, IsNull(ApproverLevel, 1) as ApproverLevel from BS_WorkFlowsLine as line left join BS_WorkFlowsDataCurrentNode on BS_WorkFlowsDataCurrentNode.CurrentNodeId = line.NodeId where line.WorkFlowsId = {0} and line.LineType = {1} and DataNo = '{2}'", workFlowsIdInt, (int)lineType, orderNoStr);
DataTable lineTable = BaseSQL.GetTableBySql(cmd);
if (lineTable.Rows.Count == 0)
{
cmd.CommandText = string.Format("select line.*, 1 as ApproverLevel from BS_WorkFlowsLine as line where line.WorkFlowsId = {0} and line.LineType = {1}", workFlowsIdInt, (int)lineType);
lineTable = BaseSQL.GetTableBySql(cmd);
}
if (lineTable.Rows.Count == 0)
{
return(0);
}
int lineIdInt = DataTypeConvert.GetInt(lineTable.Rows[0]["AutoId"]);
switch (lineType)
{
case LineType.审批: //采用多级操作
approverLevelInt = DataTypeConvert.GetInt(lineTable.Rows[0]["ApproverLevel"]);
break;
default:
approverLevelInt = 1;
break;
}
int enabledInt = DataTypeConvert.GetInt(lineTable.Rows[0]["Enabled"]);
if (enabledInt == 1)
{
WorkFlowsHandleDAO.QueryOrderViewName viewName = (WorkFlowsHandleDAO.QueryOrderViewName)((int)orderType);
WorkFlowsHandleDAO.QueryOrderPrimaryKey primaryKey = (WorkFlowsHandleDAO.QueryOrderPrimaryKey)((int)orderType);
cmd.CommandText = string.Format("Select AutoId, Condition, ConditionText from BS_WorkFlowsLineCondition where LineId = {0}", lineIdInt);
DataTable conditionTable = BaseSQL.GetTableBySql(cmd);
for (int i = 0; i < conditionTable.Rows.Count; i++)
{
string tempStr = DataHandler.SQLStringReplaceHandle(DataTypeConvert.GetString(conditionTable.Rows[i]["Condition"]));
if (tempStr == "")
{
tempStr = " 1=1";
}
cmd.CommandText = string.Format("Select Count(*) from {0} where {1} = '{2}' and ({3})", viewName, primaryKey, orderNoStr, tempStr);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
int conditionIdInt = DataTypeConvert.GetInt(conditionTable.Rows[i]["AutoId"]);
cmd.CommandText = string.Format("Select Count(*) from BS_WorkFlowsLineHandle where LineId = {0} and ConditionId = {1} and MultiLevelApprover = {2} and ((LineHandleCate = 0 and HandleOwner = '{3}') or (LineHandleCate = 1 and HandleOwner = '{4}'))", lineIdInt, conditionIdInt, approverLevelInt, SystemInfo.user.LoginID, SystemInfo.user.RoleNo);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
}
else
{
cmd.Transaction.Rollback();
return(-1);
}
break;
}
}
}
trans.Commit();
return(1);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 订单工作流处理
/// </summary>
/// <param name="orderType">订单类型 请购单 = 1，采购订单 = 2，工单 = 3,</param>
/// <param name="lineType">操作类型删除 = -1，保存 = 0，提交 = 1，取消提交 = 2，审批 = 3，取消审批 = 4，关闭 = 5，取消关闭 = 6，拒绝 = 7，任务分配 = 8，取消分配 = 9</param>
/// <param name="orderNoStr">登记单号</param>
/// <param name="approverOptionStr">审批意见</param>
/// <returns>返回结果状态（ -1：未通过，取消当前操作； 0：正常通过； >0：正常通过返回新的状态编号；）</returns>
public int OrderWorkFlowsHandle(SqlCommand cmd, OrderType orderType, LineType lineType, string orderNoStr, string approverOptionStr)
{
cmd.CommandText = string.Format("Select AutoId from BS_WorkFlows where WorkFlowsType = {0} and IsNull(Enabled, 0) = 1", (int)orderType);
DataTable workFlowsTable = BaseSQL.GetTableBySql(cmd);
if (workFlowsTable.Rows.Count == 0)
{
return(0);
}
int workFlowsIdInt = DataTypeConvert.GetInt(workFlowsTable.Rows[0]["AutoId"]);
int operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
int approverLevelInt = 1;
int isEndInt = 0;
switch (lineType)
{
case LineType.除:
cmd.CommandText = string.Format("Delete from BS_WorkFlowsDataCurrentNode where DataNo = '{0}'", orderNoStr);
cmd.ExecuteNonQuery();
return(0);
case LineType.保存:
operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
cmd.CommandText = string.Format("Select AutoId from BS_WorkFlowsNode where WorkFlowsId = {0} and IsNull(BeginNode, 0) = 1", workFlowsIdInt);
int autoIdInt_BeginNode = DataTypeConvert.GetInt(cmd.ExecuteScalar());
if (autoIdInt_BeginNode > 0)
{
Update_WorkFlowsDataCurrentNode(cmd, orderNoStr, autoIdInt_BeginNode, operationAfterOrderStatus, approverLevelInt, isEndInt);
}
return(0);
case LineType.提交:
operationAfterOrderStatus = (int)CommonHandler.OrderState.审批中;
break;
case LineType.取消提交:
operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
break;
case LineType.审批:
operationAfterOrderStatus = (int)CommonHandler.OrderState.已审批;
break;
case LineType.取消审批:
operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
break;
case LineType.关闭:
operationAfterOrderStatus = (int)CommonHandler.OrderState.已关闭;
break;
case LineType.取消关闭:
operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
break;
case LineType.拒绝:
operationAfterOrderStatus = (int)CommonHandler.OrderState.待提交;
break;
case LineType.任务分配:
operationAfterOrderStatus = (int)CommonHandler.OrderState.已审批;
break;
case LineType.取消分配:
operationAfterOrderStatus = (int)CommonHandler.OrderState.已审批;
break;
}
//查询当前操作所属的连接线信息
cmd.CommandText = string.Format("select line.*, IsNull(ApproverLevel, 1) as ApproverLevel from BS_WorkFlowsLine as line left join BS_WorkFlowsDataCurrentNode on BS_WorkFlowsDataCurrentNode.CurrentNodeId = line.NodeId where line.WorkFlowsId = {0} and line.LineType = {1} and DataNo = '{2}'", workFlowsIdInt, (int)lineType, orderNoStr);
DataTable lineTable = BaseSQL.GetTableBySql(cmd);
if (lineTable.Rows.Count == 0)
{
cmd.CommandText = string.Format("select line.*, 1 as ApproverLevel from BS_WorkFlowsLine as line where line.WorkFlowsId = {0} and line.LineType = {1}", workFlowsIdInt, (int)lineType);
lineTable = BaseSQL.GetTableBySql(cmd);
}
if (lineTable.Rows.Count == 0)
{
return(0);
}
int lineIdInt = DataTypeConvert.GetInt(lineTable.Rows[0]["AutoId"]);
int nodeIdInt_Current = DataTypeConvert.GetInt(lineTable.Rows[0]["NodeId"]);
int nodeIdInt_Next = DataTypeConvert.GetInt(lineTable.Rows[0]["LevelNodeId"]);
switch (lineType)
{
case LineType.审批: //采用多级操作
approverLevelInt = DataTypeConvert.GetInt(lineTable.Rows[0]["ApproverLevel"]);
break;
default:
approverLevelInt = 1;
break;
}
//查询流程图连接线下个状态节点信息，来判断是否结束标志等
cmd.CommandText = string.Format("select * from BS_WorkFlowsNode where AutoId = {0}", nodeIdInt_Next);
DataTable nextNodeTable = BaseSQL.GetTableBySql(cmd);
if (nextNodeTable.Rows.Count > 0)
{
int beginNodeInt = DataTypeConvert.GetInt(nextNodeTable.Rows[0]["BeginNode"]);
int nodeEnabledInt = DataTypeConvert.GetInt(nextNodeTable.Rows[0]["Enabled"]);
if (beginNodeInt == (int)NodePositionType.结束节点)
{
isEndInt = 1;
}
else
{
cmd.CommandText = string.Format("Select Count(*) from BS_WorkFlowsLine where NodeId = {0} and LevelNodeId in (select AutoId from BS_WorkFlowsNode where IsNull(BeginNode, 0) = 2 and ISNULL(Enabled, 0) = 0)", nodeIdInt_Next);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
isEndInt = 1;
}
}
if (nodeEnabledInt == 0)
{
switch (lineType)
{
case LineType.关闭:
cmd.Transaction.Rollback();
MessageHandler.ShowMessageBox("关闭状态已经在流程图中停用，关闭操作取消。");
return(-1);
case LineType.提交:
cmd.CommandText = string.Format("Select AutoId, BeginNode from BS_WorkFlowsNode where WorkFlowsId = {0} and AutoId in (Select LevelNodeId from BS_WorkFlowsLine where LineType = {1})", workFlowsIdInt, (int)LineType.审批);
DataTable approveNodeTable = BaseSQL.GetTableBySql(cmd);
if (approveNodeTable.Rows.Count > 0)
{
operationAfterOrderStatus = (int)CommonHandler.OrderState.已审批;
nodeIdInt_Next = DataTypeConvert.GetInt(approveNodeTable.Rows[0]["AutoId"]);
if (DataTypeConvert.GetInt(approveNodeTable.Rows[0]["BeginNode"]) == (int)NodePositionType.结束节点)
{
isEndInt = 1;
}
else
{
cmd.CommandText = string.Format("Select Count(*) from BS_WorkFlowsLine where NodeId = {0} and LevelNodeId in (select AutoId from BS_WorkFlowsNode where IsNull(BeginNode, 0) = 2 and ISNULL(Enabled, 0) = 0)", nodeIdInt_Next);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
isEndInt = 1;
}
}
}
break;
}
}
}
if (DataTypeConvert.GetInt(lineTable.Rows[0]["Enabled"]) == 1)
{
WorkFlowsHandleDAO.QueryOrderViewName viewName = (WorkFlowsHandleDAO.QueryOrderViewName)((int)orderType);
WorkFlowsHandleDAO.QueryOrderPrimaryKey primaryKey = (WorkFlowsHandleDAO.QueryOrderPrimaryKey)((int)orderType);
cmd.CommandText = string.Format("Select AutoId, Condition, ConditionText from BS_WorkFlowsLineCondition where LineId = {0}", lineIdInt);
DataTable conditionTable = BaseSQL.GetTableBySql(cmd);
for (int i = 0; i < conditionTable.Rows.Count; i++)
{
string tempStr = DataHandler.SQLStringReplaceHandle(DataTypeConvert.GetString(conditionTable.Rows[i]["Condition"]));
if (tempStr == "")
{
tempStr = " 1=1";
}
cmd.CommandText = string.Format("Select Count(*) from {0} where {1} = '{2}' and ({3})", viewName, primaryKey, orderNoStr, tempStr);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
int conditionIdInt = DataTypeConvert.GetInt(conditionTable.Rows[i]["AutoId"]);
cmd.CommandText = string.Format("Select Count(*) from BS_WorkFlowsLineHandle where LineId = {0} and ConditionId = {1} and MultiLevelApprover = {2} and ((LineHandleCate = 0 and HandleOwner = '{3}') or (LineHandleCate = 1 and HandleOwner = '{4}'))", lineIdInt, conditionIdInt, approverLevelInt, SystemInfo.user.LoginID, SystemInfo.user.RoleNo);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
switch (lineType)
{
case LineType.审批: //采用多级操作
approverLevelInt++;
cmd.CommandText = string.Format("select COUNT(*) from BS_WorkFlowsLineHandle where LineId = {0} and ConditionId = {1} and MultiLevelApprover ={2}", lineIdInt, conditionIdInt, approverLevelInt);
if (DataTypeConvert.GetInt(cmd.ExecuteScalar()) > 0)
{
operationAfterOrderStatus = (int)CommonHandler.OrderState.审批中;
nodeIdInt_Next = nodeIdInt_Current;
isEndInt = 0;
}
else
{
approverLevelInt = 1;
}
break;
}
}
else
{
cmd.Transaction.Rollback();
MessageHandler.ShowMessageBox(string.Format("您没有操作登记单[{0}]的[{1}]权限，请联系管理员调整权限后再进行操作。", orderNoStr, lineType));
return(-1);
}
break;
}
}
}
Update_WorkFlowsDataCurrentNode(cmd, orderNoStr, nodeIdInt_Next, operationAfterOrderStatus, approverLevelInt, isEndInt);
Insert_WorkFlowsDataHandle(cmd, orderNoStr, lineType, SystemInfo.user.AutoId, approverOptionStr);
//if ((int)orderType == 20)
//{
// cmd.Transaction.Rollback();
// MessageHandler.ShowMessageBox("提示");
// return -1;
//}
return(operationAfterOrderStatus);
}
