[AgentFlow]SQL_流程

/*
 * 查詢目前流程數量
 */
SELECT p.name,
       m.username,
       t.keyword,
       t.state,
       (to_date('1970/01/01', 'yyyy/mm/dd') + (t.starttime / 1000 / 86400)) as starttime,
       (to_date('1970/01/01', 'yyyy/mm/dd') + (t.endtime / 1000 / 86400)) as endtime
  FROM task t, pro_geninf p, mem_geninf m
 WHERE m.memid in
       (select c.memid
          from rol_geninf a, rol_mem b, mem_geninf c, dep_geninf d
         where a.rolid = b.rolid
           and b.memid = c.memid
           and a.depid = d.depid
           and d.id like '01%'
           and c.resign = 'false')
   AND t.proid = p.proid
   AND t.state in ('running')
   AND t.rootid = t.tskid
   AND t.memid = m.memid
 order by p.name, t.state, starttime desc

/*
 * 查詢某task開始與結束時間並轉換
 */
SELECT to_char((to_date('01-01-1970', 'DD-MM-YYYY') +
               (starttime + (8 * 60 * 60 * 1000)) / 86400000),
               'yyyy/mm/dd hh24:mi:ss') as starttime,
       to_char((to_date('01-01-1970', 'DD-MM-YYYY') +
               (endtime + (8 * 60 * 60 * 1000)) / 86400000),
               'yyyy/mm/dd hh24:mi:ss') as endtime
  FROM task
 WHERE tskid = 'Tsk000002966923'
 
/*
 * 將long時間格式轉為正常格式
 */
SELECT to_char((to_date('01-01-1970', 'DD-MM-YYYY') +
               (1427852861000 + (8 * 60 * 60 * 1000)) / 86400000),
               'yyyy/mm/dd hh24:mi:ss') as 時間
  FROM dual;

/*
 * 將現在時間格式轉為Long
 */  
SELECT sysdate 當前時間,
       (sysdate - to_date('01-01-1970', 'DD-MM-YYYY')) * 86400000 - (8 * 60 * 60 * 1000) Long_Time
  FROM dual;

/*
 * 查詢目前身上代辦事項
 */
SELECT ART_GENINF.NAME, ART_STATE.NAME, MEM_GENINF.USERNAME, TASK.STARTTIME
  FROM ARTINSTANCE
  JOIN ART_GENINF
    ON ART_GENINF.ARTID = ARTINSTANCE.ARTID
  JOIN TASK_ARTINS
    ON TASK_ARTINS.astid = ARTINSTANCE.ASTID
   AND TASK_ARTINS.INSID = ARTINSTANCE.INSID
  JOIN ART_STATE
    ON ART_STATE.ASTID = ARTINSTANCE.ASTID
  JOIN TASK
    ON TASK.TSKID = TASK_ARTINS.TSKID
  JOIN MEM_GENINF
    ON MEM_GENINF.MEMID = TASK.MEMID
 WHERE LOGINID = 'a0682'