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