Dbutil

package com.cnten.wf.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import com.cnten.wf.flowdefine.Flow;
import com.cnten.wf.flowdefine.Step;
import com.cnten.wf.instance.DynamicStep;
import com.cnten.wf.instance.FlowInstance;
import com.cnten.wf.instance.History;
import com.cnten.wf.instance.StepState;
import com.cnten.wf.instance.Task;


public class Dbutil {


public void dbSaveTask(Task task) throws SQLException {
task.TaskID = GetNewSequence("SEQWF_STEP_TASK_TB");
String strSql = "insert into WF_STEP_TASK_TB(TASK_ID,FLOW_ENTITY_ID,STEP_ID,FLOW_ID,BUSINESS_ENTITY_ID,TASK_NAME,EXECUTOR,TASK_URL,EXE_STATE,CREATEDATE ) values(?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = null;
Connection conn = null;
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.valueOf(task.TaskID));
pstmt.setInt(2, Integer.valueOf(task.FlowInstanceID));
pstmt.setInt(3, Integer.valueOf(task.stepID));
pstmt.setInt(4, Integer.valueOf(task.FlowID));
pstmt.setInt(5, Integer.valueOf(task.BusinessEntityID));
pstmt.setString(6, DBFormat.Format(task.TaskName, 1));
pstmt.setString(7,  task.UserID );
pstmt.setString(8, DBFormat.Format(task.Url, 1));
pstmt.setInt(9, task.TaskState.getValue());
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(10,timp);
pstmt.execute();
conn.close();
}


public String dbGetFlowID(String formCode) {
// String strSql = "select PROC_CODE from SY_FUNC_WF_PROC where FUNC_CODE='"
// + formCode + "'";
String strSql = "select FLOW_ID from WF_FLOW_TB where BO_ID='"
+ formCode + "'";
String procCode = null;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
while (rs.next()) {
procCode = rs.getString(1);
}
Dbutil.close(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return procCode;
}


public FlowInstance dbCreateFlowInstance(FlowInstance f) {
f.InstanceID = GetNewSequence("SEQWF_FLOW_ENTITY_TB");
String strSql = "insert into WF_FLOW_ENTITY_TB( FLOW_ENTITY_ID, FLOW_ENTITY_STATE, FLOW_ID, BUSINESS_ENTITY_ID, FLOW_ENTITY_NAME,CREATOR,CREATE_TIME ,BO_ID)  values(?,?,?,?,?,?,?,? )";// ����ҪBO_ID
// "insert into WF_FLOW_ENTITY_TB( FLOW_ENTITY_ID, FLOW_ENTITY_STATE, FLOW_ID, BUSINESS_ENTITY_ID, FLOW_ENTITY_NAME,CREATOR,BO_ID)  values({0},{1},{2},{3},'{4}','{5}',{6})";
PreparedStatement prest = null;
Connection conn = Dbutil.getConnection();


try {
prest = conn.prepareStatement(strSql);
prest.setInt(1, Integer.valueOf(f.InstanceID));
prest.setInt(2, f.ExeState.getValue());
prest.setInt(3, Integer.valueOf(f.FlowID));
prest.setString(4,  f.BusinessEntityID);//prest.setInt(4, Integer.valueOf(f.BusinessEntityID));
prest.setString(5, f.FlowEntityName);
prest.setString(6,  f.Creator );
 
Timestamp timp = new Timestamp(new Date().getTime());
prest.setTimestamp(7,timp);

prest.setString(8, "1");
prest.execute();
prest.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(conn);
}
return f;
}


public String dbUpdateFlowInstance(FlowInstance flowInstance) {
String strSql = "update WF_FLOW_ENTITY_TB  set FLOW_ENTITY_STATE="
+ flowInstance.ExeState.getValue() + " where FLOW_ENTITY_ID="
+ flowInstance.InstanceID;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stmt.execute(strSql);
} catch (SQLException e) {
e.printStackTrace();
return "�쳣";
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return null;
}


public List<HashMap<String, Object>> dbGetFlowSteps(String flowID) {
String strSql = "select step.step_id,step.step_name,step.step_type,step.step_usercount,step.step_performtype,step.step_deptfilter,step.step_rejecttype,step.step_controls,STEP_BRANCHMERGE from wf_step_tb step where step.flow_id='"
+ flowID + "' order by  step.step_id";
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);


HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return stepLists;
}


public List<HashMap<String, Object>> dbGetFlowStepLines(String flowID) {
String strSql = "select router.router_id,router.router_name,router.router_condition,router.router_nextstep,router.step_id from wf_router_tb router where exists(select 1 from wf_step_tb step where step.flow_id='"
+ flowID + "'  and router.step_id =step.step_id  )";
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return stepLists;
}


public HashMap<String, Object> dbFillFlowInfo(Flow flow) {


String strSQL = "select FLOW_ID,BO_ID,FLOW_NAME,FLOW_TYPE,FLOW_FORM,FLOW_CHART,FLOW_COMMENT,FLOW_CREATER,FLOW_CREATETIME,FLOW_VERSION,FLOW_TABLE,FLOW_TABLE_PK,FORM_OBJECT_PROVIDER,FLOW_USER_PROVIDER,FLOW_SENDMSG_PROVIDER  from WF_FLOW_TB where FLOW_ID="
+ flow.FlowID;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
HashMap<String, Object> mapObject = new HashMap<String, Object>();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSQL);


HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
if (stepLists.size() > 0) {
mapObject = stepLists.get(0);
}
return mapObject;
}


public HashMap<String, Object> dbGetFormObject(String tableName,
String pkFieldName, String pkValue) {
String strSQL = "select * from " + tableName + " where " + pkFieldName
+ "=" + pkValue;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
HashMap<String, Object> mapObject = new HashMap<String, Object>();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSQL);


HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
if (stepLists.size() > 0) {
mapObject = stepLists.get(0);
}
return mapObject;
}


/**
* ȡ��SEQֵ
* 
* @param newSequence
* @return
*/
public static String GetNewSequence(String newSequence) {
String strSql = "SELECT " + newSequence.trim() + ".NEXTVAL FROM DUAl";
String SEQWF = null;
Statement stmt;
ResultSet rs;
try {
stmt = Dbutil.getConnection()
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
while (rs.next()) {
SEQWF = rs.getString(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
System.err.println("SEQWF" + SEQWF);
return SEQWF;
}


public static List<HashMap<String, Object>> GetDynamicSteps(
FlowInstance flowInstance) throws SQLException {
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
String strSql = "SELECT DYNAMIC_STEP_ID,STEP_ID,STEP_EXESTATE,CUR_EXECTOR,NEXT_EXECTOR,FINISHEDUSERS,UNFINISHEDUSERS from WF_STEP_EXESTATE_TB "
+ "where FLOW_ENTITY_ID= ?";
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.parseInt(flowInstance.InstanceID));
rs = pstmt.executeQuery();
while (rs.next()) {
map = new HashMap<String, Object>();
map.put("DYNAMIC_STEP_ID", rs.getInt("DYNAMIC_STEP_ID"));
map.put("STEP_ID", rs.getInt("STEP_ID"));
map.put("STEP_EXESTATE", rs.getInt("STEP_EXESTATE"));
map.put("CUR_EXECTOR", rs.getString("CUR_EXECTOR"));
map.put("NEXT_EXECTOR", rs.getInt("NEXT_EXECTOR"));
map.put("FINISHEDUSERS", rs.getString("FINISHEDUSERS"));
map.put("UNFINISHEDUSERS", rs.getString("UNFINISHEDUSERS"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
return mapList;
}


public static HashMap<String, Object> FillFlowInstanceInfo(String flowID,
String pkValue) throws SQLException {
HashMap<String, Object> map = new HashMap<String, Object>();
String strSql = "SELECT FLOW_ENTITY_ID,FLOW_ENTITY_STATE,FLOW_ID,BUSINESS_ENTITY_ID,FLOW_ENTITY_NAME,CREATOR,BO_ID from WF_FLOW_ENTITY_TB "
+ "where FLOW_ID=? and BUSINESS_ENTITY_ID=? ";
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.parseInt(flowID));
pstmt.setInt(2, Integer.parseInt(pkValue));
rs = pstmt.executeQuery();
if (rs.next()) {
map.put("FLOW_ENTITY_ID", rs.getInt("FLOW_ENTITY_ID"));
map.put("FLOW_ENTITY_STATE", rs.getInt("FLOW_ENTITY_STATE"));
map.put("FLOW_ID", rs.getInt("FLOW_ID"));
map.put("BUSINESS_ENTITY_ID", rs.getString("BUSINESS_ENTITY_ID"));//map.put("BUSINESS_ENTITY_ID", rs.getInt("BUSINESS_ENTITY_ID"));
map.put("FLOW_ENTITY_NAME", rs.getString("FLOW_ENTITY_NAME"));
map.put("CREATOR", rs.getString("CREATOR"));
map.put("BO_ID", rs.getInt("BO_ID"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
return map;
}


public static String UpdateTaskState(Task task) throws SQLException {
String strSql = "UPDATE WF_STEP_TASK_TB set EXE_STATE=?,USER_COMMENT=?,USER_ATTACHMENT=? "
+ " where FLOW_ENTITY_ID=? and EXECUTOR=? and STEP_ID=?";
String strMsg = "";
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, task.TaskState.getValue());
pstmt.setString(2, task.UserComment);
pstmt.setString(3, task.UserAttach);
pstmt.setInt(4, Integer.parseInt(task.FlowInstanceID));
pstmt.setString(5,  task.UserID );
pstmt.setInt(6, Integer.parseInt(task.stepID));
pstmt.execute();
} catch (SQLException e) {
strMsg = "error!"+e.getMessage();
e.printStackTrace();
} finally {
conn.close();
}
return strMsg;
}


public static String SaveHistory(History history) throws SQLException {


String trace_id = GetNewSequence("SEQWF_STEP_TRACE_TB");
String strSql = "INSERT INTO WF_STEP_TRACE_TB(STEP_ID,STEP_STATE,EXECUTOR,EXE_REMARK,BEGIN_TIME,FLOW_ENTITY_ID,TRACE_ID) "
+ "values(?,?,?,?,?,?,?)";
String strMsg = "";
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setString(1, DBFormat.Format(history.stepID, 1));
pstmt.setInt(2, history.Operation.getValue());
pstmt.setString(3, DBFormat.Format(history.userID, 1));
pstmt.setString(4, DBFormat.Format(history.Comment, 2));
 
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(5,timp);
pstmt.setInt(6, Integer.parseInt(history.InstanceId));
pstmt.setInt(7, Integer.parseInt(trace_id));
pstmt.execute();
} catch (SQLException e) {
strMsg = "error!";
e.printStackTrace();
} finally {
conn.close();
}
return strMsg;
}


public static String SaveDynamicStep(DynamicStep dynamicStep)
throws SQLException {
String strSql = null;
String strMsg = "";
if (dynamicStep.DynamicStepID == null
|| dynamicStep.DynamicStepID == "") {
dynamicStep.DynamicStepID = null;
// ��ȡ���к� DataAccess.GetNewSequence("WF_STEP_EXESTATE_TB");
dynamicStep.DynamicStepID = GetNewSequence("SEQ_WF_STEP_EXESTATE");
strSql = "INSERT INTO WF_STEP_EXESTATE_TB(FLOW_ENTITY_ID,STEP_ID,STEP_EXESTATE,CREATE_TIME,CUR_EXECTOR,NEXT_EXECTOR,FINISHEDUSERS,UNFINISHEDUSERS,DYNAMIC_STEP_ID) "
+ "VALUES(?,?,?,?,?,?,?,?,?) ";
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.valueOf(dynamicStep.InstanceId));
pstmt.setInt(2, Integer.valueOf(dynamicStep.stepID));
pstmt.setInt(3, dynamicStep.StepState.getValue());
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(4,timp);
pstmt.setString(5,  
DBFormat.Format(dynamicStep.CurExecutor,1)  );// pstmt.setInt(5, Integer.valueOf(DBFormat.Format(
                                                    // dynamicStep.CurExecutor, 1)));
pstmt.setString(6, null);
pstmt.setString(7, DBFormat.Format(
dynamicStep.FinishedCheckers, 1));
pstmt.setString(8, DBFormat.Format(
dynamicStep.UnfinishedCheckers, 1));
pstmt.setInt(9, Integer.parseInt(dynamicStep.DynamicStepID));
pstmt.execute();
} catch (SQLException e) {
strMsg = "error!";
e.printStackTrace();
} finally {
conn.close();
}
} else {
strSql = "UPDATE WF_STEP_EXESTATE_TB SET STEP_EXESTATE=?,UPDATE_TIME=?,CUR_EXECTOR=?,"
+ "NEXT_EXECTOR=?,FINISHEDUSERS=?,UNFINISHEDUSERS=? WHERE DYNAMIC_STEP_ID=?";
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, dynamicStep.StepState.getValue());// 
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(2,timp);
pstmt.setString(3,  DBFormat.Format(dynamicStep.CurExecutor,1) );
pstmt.setString(4, DBFormat.Format(null, 1));
pstmt.setString(5, dynamicStep.FinishedCheckers);
pstmt.setString(6, dynamicStep.UnfinishedCheckers);
pstmt.setInt(7, Integer.parseInt(dynamicStep.DynamicStepID));
pstmt.execute();
} catch (SQLException e) {
strMsg = "error!";
e.printStackTrace();
} finally {
conn.close();
}
}
return strMsg;
}


public static String CreateDistributeUser(DynamicStep dyStep, Step step,
String userId, List<String> asignUserIds) throws SQLException {
String strMsg = null;
String strSql = "INSERT INTO wf_delegate_user_tb(DYNAMIC_STEP_ID,STEP_ID,EXE_USER_ID,DEL_USER_ID,DISTRIBUTE_TIME) "
+ "values(?,?,?,?,?)";
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
for (Iterator<String> iter = asignUserIds.iterator(); iter
.hasNext();) {
dyStep.DynamicStepID = GetNewSequence("SEQ_DYNAMIC_STEP_USER");
String asignUserId = (String) iter.next();
pstmt.setString(1, dyStep.DynamicStepID);
pstmt.setString(2, step.StepID);
pstmt.setString(3, userId);
pstmt.setString(4, asignUserId);
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(5,timp);
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn.close();
}
return strMsg;
}


public static List<HashMap<String, Object>> GetUserTaskRawData(
String flowID, String exeUserId, StepState taskState)
throws SQLException {
String strSql = "SELECT TASK_ID,FLOW_ENTITY_ID,STEP_ID,FLOW_ID,BUSINESS_ENTITY_ID,TASK_NAME,EXECUTOR,TASK_URL,EXE_STATE,USER_COMMENT, USER_ATTACHMENT,CREATEDATE from WF_STEP_TASK_TB "
+ "where FLOW_ID=? and EXECUTOR=? and EXE_STATE=?";
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.parseInt(flowID));
pstmt.setString(2,  exeUserId );//pstmt.setInt(2, Integer.parseInt(exeUserId));
pstmt.setInt(3, taskState.getValue());
rs = pstmt.executeQuery();
while (rs.next()) {
map = new HashMap<String, Object>();
map.put("TASK_ID", rs.getInt("TASK_ID"));
map.put("FLOW_ENTITY_ID", rs.getInt("FLOW_ENTITY_ID"));
map.put("STEP_ID", rs.getInt("STEP_ID"));
map.put("FLOW_ID", rs.getInt("FLOW_ID"));
map.put("BUSINESS_ENTITY_ID", rs.getString ("BUSINESS_ENTITY_ID"));//map.put("BUSINESS_ENTITY_ID", rs.getInt("BUSINESS_ENTITY_ID"));
map.put("TASK_NAME", rs.getString("TASK_NAME"));
map.put("EXECUTOR", rs.getString("EXECUTOR"));
map.put("TASK_URL", rs.getString("TASK_URL"));
map.put("EXE_STATE", rs.getInt("EXE_STATE"));
map.put("USER_COMMENT", rs.getString("USER_COMMENT"));
map.put("USER_ATTACHMENT", rs.getString("USER_ATTACHMENT"));
map.put("CREATEDATE", rs.getTimestamp("CREATEDATE"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
return mapList;
}


public static List<HashMap<String, Object>> GetUserTaskRawData(
String flowID, String exeUserId, String pkValue, StepState taskState)
throws SQLException {


String strSql = "SELECT TASK_ID,FLOW_ENTITY_ID,STEP_ID,FLOW_ID,BUSINESS_ENTITY_ID,TASK_NAME,EXECUTOR,TASK_URL,EXE_STATE,USER_COMMENT, USER_ATTACHMENT,CREATEDATE from WF_STEP_TASK_TB "
+ "where FLOW_ID=? and EXECUTOR=? and EXE_STATE=? and BUSINESS_ENTITY_ID=?";
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.parseInt(flowID));
pstmt.setString(2, exeUserId );//pstmt.setInt(2, Integer.parseInt(exeUserId));
pstmt.setInt(3, taskState.getValue());
pstmt.setString(4,  pkValue);
rs = pstmt.executeQuery();
while (rs.next()) {
map = new HashMap<String, Object>();
map.put("TASK_ID", rs.getInt("TASK_ID"));
map.put("FLOW_ENTITY_ID", rs.getInt("FLOW_ENTITY_ID"));
map.put("STEP_ID", rs.getInt("STEP_ID"));
map.put("FLOW_ID", rs.getInt("FLOW_ID"));
map.put("BUSINESS_ENTITY_ID", rs.getString("BUSINESS_ENTITY_ID"));//map.put("BUSINESS_ENTITY_ID", rs.getInt("BUSINESS_ENTITY_ID"));
map.put("TASK_NAME", rs.getString("TASK_NAME"));
map.put("EXECUTOR", rs.getString("EXECUTOR"));
map.put("TASK_URL", rs.getString("TASK_URL"));
map.put("EXE_STATE", rs.getInt("EXE_STATE"));
map.put("USER_COMMENT", rs.getString("USER_COMMENT"));
map.put("USER_ATTACHMENT", rs.getString("USER_ATTACHMENT"));
map.put("CREATEDATE", rs.getTimestamp("CREATEDATE"));//map.put("CREATEDATE", rs.getDate("CREATEDATE"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}


return mapList;


}


public static List<HashMap<String, Object>> GetFlowEvents(Flow flow)
throws SQLException {
String strSql = "SELECT EXTEND_REF_ID,PATH,CODE_TYPE,EVENT_TYPE FROM WF_FLOW_EXTEND_TB "
+ "WHERE FLOW_EXTEND_TYPE=1 AND EXTEND_REF_ID= ?";
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {


conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setString(1, flow.FlowID);
rs = pstmt.executeQuery();
while(rs.next()) {
map = new HashMap<String, Object>();
map.put("EXTEND_REF_ID", rs.getInt("EXTEND_REF_ID"));
map.put("PATH", rs.getString("PATH"));
map.put("CODE_TYPE", rs.getInt("CODE_TYPE"));
map.put("EVENT_TYPE", rs.getInt("EVENT_TYPE"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
return mapList;
}


public static List<HashMap<String, Object>> GetStepEvents(Flow flow)
throws SQLException {
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
StringBuilder sbStepID = new StringBuilder();
for (Step step : flow.Steps) {
sbStepID.append(step.StepID);
sbStepID.append(",");
}
if (sbStepID.length() != 0) {
sbStepID.deleteCharAt(sbStepID.length() - 1);
String strSql = "SELECT EXTEND_REF_ID ,PATH,CODE_TYPE,EVENT_TYPE FROM WF_FLOW_EXTEND_TB WHERE FLOW_EXTEND_TYPE=2 "
+ "AND EXTEND_REF_ID in("
+ sbStepID.toString()
+ ") order by EXTEND_REF_ID";


HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
rs = pstmt.executeQuery();
while (rs.next()) {
map = new HashMap<String, Object>();
map.put("EXTEND_REF_ID", rs.getInt("EXTEND_REF_ID"));
map.put("PATH", rs.getString("PATH"));
map.put("CODE_TYPE", rs.getInt("CODE_TYPE"));
map.put("EVENT_TYPE", rs.getInt("EVENT_TYPE"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
}
return mapList;
}


public static List<HashMap<String, Object>> GetUserActivedTask(
FlowInstance flowInstance, DynamicStep dyStep, String... exeUserIds)
throws SQLException {
StringBuffer userIds = new StringBuffer();
for (int i = 0; i < exeUserIds.length; i++) {
if (i == (exeUserIds.length - 1)) {
userIds.append("'");
userIds.append(exeUserIds[i]);
userIds.append("'");
} else {
userIds.append("'");
userIds.append(exeUserIds[i]). append("'").append(",");
}
}


String strSql = "SELECT TASK_ID, FLOW_ENTITY_ID,STEP_ID,FLOW_ID,BUSINESS_ENTITY_ID,TASK_NAME,EXECUTOR,TASK_URL,EXE_STATE,USER_COMMENT,USER_ATTACHMENT from WF_STEP_TASK_TB "
+ "where  FLOW_ENTITY_ID=? and EXECUTOR in ("
+ userIds.toString() + ") and STEP_ID=? and EXE_STATE=?";
List<HashMap<String, Object>> mapList = new ArrayList<HashMap<String, Object>>();
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.parseInt(flowInstance.InstanceID));
pstmt.setInt(2, Integer.parseInt(dyStep.stepID));
pstmt.setInt(3, StepState.actived.getValue());
rs = pstmt.executeQuery();
while (rs.next()) {
map = new HashMap<String, Object>();
map.put("TASK_ID", rs.getInt("TASK_ID"));
map.put("FLOW_ENTITY_ID", rs.getInt("FLOW_ENTITY_ID"));
map.put("STEP_ID", rs.getInt("STEP_ID"));
map.put("FLOW_ID", rs.getInt("FLOW_ID"));
map.put("BUSINESS_ENTITY_ID", rs.getString("BUSINESS_ENTITY_ID"));//map.put("BUSINESS_ENTITY_ID", rs.getInt("BUSINESS_ENTITY_ID"));
map.put("TASK_NAME", rs.getString("TASK_NAME"));
map.put("EXECUTOR", rs.getString("EXECUTOR"));
map.put("TASK_URL", rs.getString("TASK_URL"));
map.put("EXE_STATE", rs.getInt("EXE_STATE"));
map.put("USER_COMMENT", rs.getString("USER_COMMENT"));
map.put("USER_ATTACHMENT", rs.getString("USER_ATTACHMENT"));
mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
conn.close();
}
return mapList;
}


public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String url = "jdbc:oracle:thin:@192.168.0.61:1521:orcl";
String user = "LEOPARD";
String password = "LEOPARD";
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}


public static void close(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void commit(Connection conn) {
if (conn != null) {
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void rollback(Connection conn) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void setAutoCommit(Connection conn, boolean autoCommit) {
if (conn != null) {
try {
conn.setAutoCommit(autoCommit);
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static void close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public static HashMap<String, String> getFormCodeInfo(String strSql) {
// TODO Auto-generated method stub


HashMap<String, String> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {


conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
// pstmt.setString(1, flow.FlowID);
rs = pstmt.executeQuery();
if (rs.next()) {
map = new HashMap<String, String>();
map.put("TABLE_CODE_ACTION", rs.getString("TABLE_CODE_ACTION"));
map.put("TABLE_KEY_CODE", rs.getString("TABLE_KEY_CODE"));


// mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}
return map;
}


public static HashMap<String, Object> getFormObject(String strSql) {
// TODO Auto-generated method stub
HashMap<String, Object> map = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
try {


conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
// pstmt.setString(1, flow.FlowID);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();


if (rs.next()) {


map = new HashMap<String, Object>();
for (int i = 1; i <= colCount; i++) {
String columnName = rsmd.getColumnName(i);
map.put(columnName, rs.getObject(i));


}


// mapList.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}
return map;
}


}