NQY_UPDATE_PAP_GROOVY

import groovy.sql.Sql
import java.sql.Timestamp
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')
sql.eachRow('SELECT x.序號,x.細胞病理編號,x.個案姓名,x.身份證字號,x.出生日期 ,SUBSTR(x.採檢日期,1,3)||SUBSTR(x.採檢日期,5,2)||SUBSTR(x.採檢日期,8,2) AS 採檢日期, x.收到日期,x.確診日期,x.收到天數,x.檢驗天數,x.抹片品質,x.尚可或難以判讀原因,x.可能感染,x.診斷結果,x.支付方式,y.REGNO '
     +'FROM IDEMPIERE.NQY_PAP x '
     +'INNER JOIN IPD.FTH008K y ON TRIM(x.身份證字號)=y.IDNO ') { r1 ->
    println "[有檢驗報告:] ${r1.細胞病理編號.padRight(10)} ${r1.個案姓名} ${r1.REGNO} ${r1.採檢日期}"
   def p1=  r1.REGNO
   def p2=  r1.採檢日期
   sql.eachRow('SELECT DISTINCT  b.ORDER_NO, b.CASENO, C.REGNO, b.SDATE, B.OBDATE,  b.CODE '
       +'FROM IPD.FOB003K b '
       +'INNER JOIN IPD.FOB002K c ON C.CASENO=B.CASENO '
       +'WHERE 1=1 '
       +'AND c.REGNO =? '
       +'AND b.OBDATE=?  '
       +'AND (SUBSTR(b.CODE,1,4)= ? OR b.CODE=? OR b.CODE=? ) '
       +'AND b.CANCEL_MARK IS NULL '
       +'ORDER BY c.REGNO ', [ p1, p2, 'PAPS', '031', '31' ] ) { r2 ->
       def v_result = ''
       if         (r1.診斷結果.indexOf('Category 0:') > 0) v_result = '0';
       else if (r1.診斷結果.indexOf('Category 1:') > 0) v_result = '1';
       else if (r1.診斷結果.indexOf('Category 2:') > 0) v_result = '2';
       else if (r1.診斷結果.indexOf('Category 3:') > 0) v_result = '3';
       else if (r1.診斷結果.indexOf('Category 4:') > 0) v_result = '4';
       else if (r1.診斷結果.indexOf('Category 5:') > 0) v_result = '5';
       //println "${r2.ORDER_NO.padRight(10)}  ${r2.ORDER_NO} ${r2.CASENO} ${r2.CODE} ${v_result}"
   }
}
return "成功"