傑佛瑞Blog TIPTOP ERP+ EasyFlow (BPM) + BI 系統整合開發經驗分享

電子簽核統計表範例

 public function StringToSQL(switchMode:String):String {

   var para1:String = f_sdate.text;

   var para2:String = f_edate.text;

   var USERID:String = myUserName;

  /* BEGIN: 組合SQL命令   要操作遠端物件去執行的 SQL命令字串  */ 

  var sql_str:String ="";  

  sql_str +=" SELECT distinct ('http://127.0.0.1:8080/NaNaWeb/GP/WMS/TraceProcess/TraceProcessForSearchForm'||'?'||'hdnMethod=searchSingleFormDetail'||'&'||'hdnFormDefId="+TableSchema.toString()+"'||'&'||'hdnProcessInstOID='|| PROCESSOID ||'&'||'hdnCurrentUserId=administrator') AS URL,('http://127.0.0.1:8080/NaNaWeb/GP/WMS/TraceProcess/ProcessInstanceTraceResult'||'?'||'hdnMethod=traceProcessFromExternalWeb'||'&'||'hdnProcessInstOID='|| PROCESSOID ||'&'||'hdnCurrentUserId=administrator'||'&'||'hdnLocale=zh_TW') AS URLX,";

  sql_str +=" NVL(serialNumber,' ') AS X,nvl(to_char(f_applydate,'yyyy/mm/dd'),' ') AS X0,NVL(X1,' ') AS X1,NVL(X2,' ') AS X2,NVL(X3,' ') AS X3,NVL(X4,' ') AS X4,NVL(X5,' ') AS X5,NVL(X6,' ') AS X6,NVL(X7,' ') AS X7,NVL(X8,' ') AS X8,NVL(X9,' ') AS X9,DECODE(NVL(processState,9), '0', '未開始', '1', '進行中', '2', '已暫停', '3', '已完成', '4', '已撤銷', '5', '已中止',processState) AS X10,nvl(t3.username,'無') AS X11,NVL(X12,'未核准') AS X12 "; 

  sql_str +=" FROM ( SELECT DISTINCT LOCALRELEVANTDATA.valueoid,FORMINSTANCE.SERIALNUMBER AS A1,ProcessInstance.serialNumber, ProcessInstance.currentState AS processState, ProcessInstance.OID AS processOID, ParticipantActivityInstance.definitionId, Users.id, Users.userName "; 

  sql_str +=" FROM ef.ProcessInstance, ef.ParticipantActivityInstance, ef.WorkItem, ef.WorkAssignment, ef.Users,ef.LOCALRELEVANTDATA,ef.FORMINSTANCE ";

  sql_str +=" WHERE ParticipantActivityInstance.contextOID = ProcessInstance.contextOID "; 

  sql_str +=" AND ParticipantActivityInstance.currentState >= 0 ";

  sql_str +=" AND WorkItem.containerOID = ParticipantActivityInstance.OID "; 

  sql_str +=" AND WorkItem.currentState >= 0 AND WorkAssignment.workItemOID = WorkItem.OID "; 

  sql_str +=" AND WorkAssignment.isNotice >= 0 AND WorkAssignment.assigneeOID = Users.OID ";

  sql_str +=" and PROCESSINSTANCE.CONTEXTOID=LOCALRELEVANTDATA.CONTAINEROID and LOCALRELEVANTDATA.VALUEOID=FORMINSTANCE.OID) t1, ";

  sql_str +=" (SELECT DISTINCT ProcessInstance.OID AS processOID2, Users.id, Users.userName "; 

  sql_str +=" FROM EF.ProcessInstance, EF.ParticipantActivityInstance, EF.WorkItem, EF.WorkAssignment, EF.Users "; 

  sql_str +=" WHERE ParticipantActivityInstance.contextOID = ProcessInstance.contextOID ";

  sql_str +=" AND (ParticipantActivityInstance.currentState = 0 OR ParticipantActivityInstance.currentState = 1 "; 

  sql_str +=" OR ParticipantActivityInstance.currentState = 6) ";

  sql_str +=" AND WorkItem.containerOID = ParticipantActivityInstance.OID "; 

  sql_str +=" AND (WorkItem.currentState = 0 OR WorkItem.currentState = 1) ";

  sql_str +=" AND WorkAssignment.workItemOID = WorkItem.OID AND WorkAssignment.isNotice = 0 "; 

  sql_str +=" AND WorkAssignment.assigneeOID = Users.OID ";

  sql_str +=" and ProcessInstance.serialNumber like '"+TableSchema.toString()+"%' ) t3 , ";

  sql_str +=" ( select T1.oid,f_applier,f_applydate,nvl(f_applydept,' ') AS X1,users.id AS UUID, ";

  sql_str +=" nvl(users.username,'無') AS X2,nvl(sel_leavingtype,( select distinct NVL(max(g_sel_leavingtype),' ') from ef."+TableSchema.toString()+"_g_detail t2 "; 

  sql_str +=" where t1.formserialnumber = t2.formserialnumber and rownum <=1  )) AS X3,nvl(f_sdate,( select distinct min(g_cqh04) from ef."+TableSchema.toString()+"_g_detail t2 "; 

  sql_str +=" where t1.formserialnumber = t2.formserialnumber  )) AS X4, ";

  sql_str +=" nvl(f_stime,( select distinct min(g_cqh051) from ef."+TableSchema.toString()+"_g_detail t2 ";

  sql_str +=" where t1.formserialnumber = t2.formserialnumber  )) AS X5,nvl(f_edate,( select distinct max(g_cqh04) from ef."+TableSchema.toString()+"_g_detail t2  ";

  sql_str +=" where t1.formserialnumber = t2.formserialnumber  )) AS X6,nvl(f_etime,( select distinct max(g_cqh061) from ef."+TableSchema.toString()+"_g_detail t2 "; 

  sql_str +=" where t1.formserialnumber = t2.formserialnumber  )) AS X7, ";

  sql_str +=" decode(substr(NVL(txt_applied_hours,nvl(f_total_hours,0)),1,1),'.','0'||NVL(txt_applied_hours,nvl(f_total_hours,0)),nvl(to_char(nvl(txt_applied_hours,nvl(f_total_hours,0))),' '))  AS X8, ";

  sql_str +=" decode(substr(NVL(txt_applied_days,nvl(f_total_days,0)),1,1),'.','0'||nvl(txt_applied_days,nvl(f_total_days,0)),nvl(to_char(nvl(txt_applied_days,nvl(f_total_days,0))),' '))  AS X9,NVL(f_CONFX,'未核准') AS X12 ";

  sql_str +=" from EF."+TableSchema.toString()+" t1,EF.USERS WHERE f_applier = users.id(+) ";

  sql_str +=" ) t4 ";

  sql_str +=" where serialNumber like '"+TableSchema.toString()+"%' "; 

  sql_str +=" AND t3.processOID2(+) = processOID AND t1.valueoid = t4.oid(+)  AND t4.f_applier LIKE '"+USERID+"' AND";

  sql_str +=" f_applydate BETWEEN to_date('"+para1+"','yyyy/mm/dd')  AND to_date('"+para2+"','yyyy/mm/dd') AND ";

  sql_str +=" processState LIKE '"+f_status.selectedItem.data+"' order by 4 desc ";

  /* END: 組合SQL命令   要操作遠端物件去執行的 SQL命令字串  */ 

  return sql_str;

}