OracleCOST: 11月 2015

2015年11月19日星期四

groovy:NQY_R_Text

//groovy:NQY_R_Text
import groovy.sql.Sql
import java.sql.Timestamp
def sqlOracle = Sql.newInstance( 'jdbc:oracle:thin:@192.168.170.2:1521:HEALTH', 'IDEMPIERE','IDEMPIERE', 'oracle.jdbc.OracleDriver' )
def m_created = new Timestamp(System.currentTimeMillis());
def dataList = []
def f0,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20
def v0,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20
def i0 = 0
def fileName = ''
def dir = new File("C:\\PAP")
dir.eachFileRecurse (FileType.FILES) { file ->
// list << file
// println file.path
// String result = names.substring(0, names.indexOf('-'))
   if ((file.path).indexOf('新泰宜') > 0 &&
       (file.path).indexOf('子抹電子檔') > 0 )
      println file.path
      fileName = file.path

}
sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
}

// def fileName = 'C:/PAP/NQY_1.CSV'
def file = new File(fileName).getText('UTF-8')
def lineCount = 0
file.eachLine() { line ->
    def field = line.tokenize(",")
    lineCount++
    // println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
    f0=null
    f1=null
    f2=null
    f3=null
    f4=null
    if (field[0] != null) f0 = field[0].trim()
    if (field[1] != null) f1 = field[1].trim()
    if (field[2] != null) f2 = field[2].trim()
    if (field[3] != null) f3 = field[3].trim()
    if (field[4] != null) f4 = field[4].trim()
    if (f0!=null && (v0=="序號" || i0>0) ) {
        i0 = f0.isInteger() ? (f0 as int) : null
        if (i0!=null) {
           println "line: ${i0} "
           sqlOracle.execute('insert into NQY_PAP(序號, 細胞病理編號, 個案姓名, 身份證字號, 出生日期, 採檢日期, 收到日期, 確診日期, 收到天數, 檢驗天數, 抹片品質, 尚可或難以判讀原因,可能感染, 診斷結果, 支付方式)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[f0,f1,f2,f3,f4 ,field[5],field[6],field[7],field[8],field[9],field[10],field[11],field[12],field[13],field[14]])
        }
     }
     v0=f0;v1=f1;
}
result="

groovy:NQY_R_Directory

//groovy:NQY_R_Directory
import groovy.io.FileType
import groovy.sql.Sql
import java.sql.Timestamp
import java.util.Calendar;
import java.util.Date;
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')

println(Calendar.getInstance().get(Calendar.HOUR_OF_DAY))   // 17
println(Calendar.getInstance().get(Calendar.MINUTE))              // 12
println(Calendar.getInstance().get(Calendar.SECOND))          // 23

def filepath = ''
def list = []
def dir = new File("C:\\PAP")

dir.eachFileRecurse (FileType.FILES) { file ->
// println file.path
// String result = names.substring(0, names.indexOf('-'))
     if ((file.path).indexOf('新泰宜') > 0 &&
       (file.path).indexOf('子抹電子檔') > 0 ){
          list << file
          // println file.path
       }
}
println "矩陣讀出...."
list.each {
   println it.path
   def fileName = it.path
   def v_c1 = 0
   sql.eachRow("select count(*) as c1 from nqy_pap_imp_log where filename='"+fileName+"'" ) { row -> // 是否已寫入過
   println "($row.c1)"
   }
   def file = new File(fileName).getText('Big5')
   def lineCount = 0
   file.eachLine() { line ->
      def field = line.tokenize(",")
       lineCount++
       println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
   }
}

/*
// CREATE TABLE nqy_pap_imp_log (filename VARCHAR2(200),created DATE);

list.each {
   println it.path
   def fileName = it.path
   def file = new File(fileName).getText('Big5')
// def file = new File(fileName).getText('UTF-8')
   def lineCount = 0
   file.eachLine() { line ->
      def field = line.tokenize(",")
      lineCount++
   // println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
      f0=null
      f1=null
      f2=null
      f3=null
      f4=null
      if (field[0] != null) f0 = field[0].trim()
      if (field[1] != null) f1 = field[1].trim()
      if (field[2] != null) f2 = field[2].trim()
      if (field[3] != null) f3 = field[3].trim()
      if (field[4] != null) f4 = field[4].trim()
      if (f0!=null && (v0=="序號" || i0>0) ) {
          i0 = f0.isInteger() ? (f0 as int) : null
          if (i0!=null) {
              println "line: ${i0} "
          }
          v0=f0;v1=f1;
      }
   }
}*/
return ""

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 "成功"

NQY_UPDATE_PAP

CREATE OR REPLACE PROCEDURE IDEMPIERE.NQY_UPDATE_PAP(P_REGNO VARCHAR2,P_SDATE_B VARCHAR2, P_SDATE_E VARCHAR2)
AS
-- 乳攝檢查檔更新鑑驗檔 MAMO 值
-- EXECUTE IDEMPIERE.NQY_UPDATE_PAP ('00073449','1040826','1040826');
--   COMMIT
-- select * from all_tab_cols where column_name='HOSRT' and length(table_name)=7
v_key           varchar2(80);
v_result        varchar2(20);
v_date          varchar2(20);
BEGIN
dbms_output.put_line('--------------------');
--v_search_string := P_REGNO;
-- INNER JOIN FOB003K c ON b.ORDER_NO=a.ORDER_NO -- b.CASENO
-- DELETE IDEMPIERE.NQY_PAP COMMIT
-- SELECT * FROM IDEMPIERE.NQY_PAP

FOR a1 IN (
             SELECT
x.序號         ,-- VARCHAR2(40),
x.細胞病理編號 ,-- VARCHAR2(40),
x.個案姓名     ,-- VARCHAR2(40),
x.身份證字號   ,-- VARCHAR2(40),
x.出生日期     ,-- VARCHAR2(40),
SUBSTR(x.採檢日期,1,3)||SUBSTR(x.採檢日期,5,2)||SUBSTR(x.採檢日期,8,2) AS 採檢日期,-- VARCHAR2(40),
x.收到日期     ,-- VARCHAR2(40),
x.確診日期     ,-- VARCHAR2(40),
x.收到天數     ,-- VARCHAR2(40),
x.檢驗天數     ,-- VARCHAR2(40),
x.抹片品質     ,-- VARCHAR2(40),
x.尚可或難以判讀原因 ,--VARCHAR2(40),
x.可能感染     ,--VARCHAR2(40),
x.診斷結果     ,--VARCHAR2(40),
x.支付方式     ,--VARCHAR2(40)
y.REGNO
              FROM IDEMPIERE.NQY_PAP x --M200161967
        INNER JOIN IPD.FTH008K       y ON TRIM(x.身份證字號)=y.IDNO
)LOOP

     dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||'xxx');

     FOR a2 IN (
              SELECT DISTINCT b.ORDER_NO,
                      b.CASENO, C.REGNO, b.SDATE, B.OBDATE, b.CODE --b.CODE_TYPE,
               FROM IPD.FOB003K b
         INNER JOIN IPD.FOB002K c ON C.CASENO=B.CASENO
              WHERE 1=1
                AND c.REGNO = a1.REGNO
                AND b.OBDATE= a1.採檢日期
                AND (SUBSTR(b.CODE,1,4)='PAPS' OR b.CODE='031' OR b.CODE='31')
                AND b.CANCEL_MARK IS NULL
             --   AND EXISTS(SELECT 1
             --                FROM IPD.FEX002K d
             --               WHERE d.CLASS = 'PAP'
             --                 AND(d.RESULT IS NULL OR d.RESULT='') --- 還有空白的才做
             --                 AND d.PKEY_CASENO=b.CASENO )
           ORDER BY c.REGNO
     )LOOP

       dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||' : '||a2.ORDER_NO||' : '||a2.CASENO||' : '||a2.REGNO||' : '||a2.CODE);
       v_result := '';
       IF    INSTR(a1.診斷結果,'Category 0:') > 0 THEN
             v_result := '0';
       ELSIF INSTR(a1.診斷結果,'Category 1:') > 0 THEN
             v_result := '1';
       ELSIF INSTR(a1.診斷結果,'Category 2:') > 0 THEN
             v_result := '2';
       ELSIF INSTR(a1.診斷結果,'Category 3:') > 0 THEN
             v_result := '3';
       ELSIF INSTR(a1.診斷結果,'Category 4:') > 0 THEN
             v_result := '4';
       ELSIF INSTR(a1.診斷結果,'Category 5:') > 0 THEN
             v_result := '5';
       END IF;

    --   COMMIT
       v_date := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR( ADD_MONTHS(SYSDATE,-120)    ,'YYYYMMDD'),'99999999') -19110000,'9999999'));
       IF LENGTH(v_date) = 5 THEN
          v_date := '00'||v_date;
       ELSIF LENGTH(v_date) = 6 THEN
          v_date := '0'||v_date;
       END IF;
       /*
       UPDATE FEX002K d
          SET d.RESULT = v_result,
              MEDICAL_DIVISION = 'L006',
              REPORT_DATE = v_date,
              REPORT_TIME = NULL,
              REPORT_USRID= 'L006',
              CONFIRM_FLAG = 'Y',
              CONFIRM_DATE = v_date,
              CONFIRM_TIME = NULL,
              CONFIRM_USRID= 'L006'
        WHERE d.CLASS = 'MAMO'
          AND(d.RESULT IS NULL OR d.RESULT = '')
          AND d.PKEY_CASENO=a1.CASENO;
      */


     END LOOP;
END LOOP;
END;
/

2015年11月17日星期二

醫院系統轉檔_20151117_02

// def fileName = 'C:/PAP/NQY_1.CSV'

def file = new File(fileName).getText('UTF-8')

def lineCount = 0

file.eachLine() { line ->

def field = line.tokenize(",")

lineCount++

// println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "

f0=null

f1=null

f2=null

f3=null

f4=null

if (field[0] != null) f0 = field[0].trim()

if (field[1] != null) f1 = field[1].trim()

if (field[2] != null) f2 = field[2].trim()

if (field[3] != null) f3 = field[3].trim()

if (field[4] != null) f4 = field[4].trim()

if (f0!=null && (v0=="序號" || i0>0) ) {

i0 = f0.isInteger() ? (f0 as int) : null

if (i0!=null) {

println "line: ${i0} "

sqlOracle.execute('insert into NQY_PAP(序號, 細胞病理編號, 個案姓名, 身份證字號, 出生日期, 採檢日期, 收到日期, 確診日期, 收到天數, 檢驗天數, 抹片品質, 尚可或難以判讀原因,可能感染, 診斷結果, 支付方式)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[f0,f1,f2,f3,f4 ,field[5],field[6],field[7],field[8],field[9],field[10],field[11],field[12],field[13],field[14]])

}

v0=f0;v1=f1;

}

result=""

醫院系統轉檔 20151117_01

醫院系統轉檔

//groovy:NQY_R_Directory
import groovy.io.FileType
import groovy.sql.Sql
import java.sql.Timestamp
import java.util.Calendar;
import java.util.Date;
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')

println(Calendar.getInstance().get(Calendar.HOUR_OF_DAY)) // 17
println(Calendar.getInstance().get(Calendar.MINUTE)) // 12
println(Calendar.getInstance().get(Calendar.SECOND)) // 23

def filepath = ''
def list = []
def dir = new File("C:\\PAP")

dir.eachFileRecurse (FileType.FILES) { file ->
// println file.path
// String result = names.substring(0, names.indexOf('-'))
if ((file.path).indexOf('新泰宜') > 0 &&
(file.path).indexOf('子抹電子檔') > 0 ){
list << file
// println file.path
}
}
println "矩陣讀出...."
list.each {
println it.path
def fileName = it.path
def v_c1 = 0
sql.eachRow("select count(*) as c1 from nqy_pap_imp_log where filename='"+fileName+"'" ) { row -> // 是否已寫入過
println "($row.c1)"
}
def file = new File(fileName).getText('Big5')
def lineCount = 0
file.eachLine() { line ->
def field = line.tokenize(",")
lineCount++
println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
}
}

/*
// CREATE TABLE nqy_pap_imp_log (filename VARCHAR2(200),created DATE);

list.each {
println it.path
def fileName = it.path
def file = new File(fileName).getText('Big5')
// def file = new File(fileName).getText('UTF-8')
def lineCount = 0
file.eachLine() { line ->
def field = line.tokenize(",")
lineCount++
// println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
f0=null
f1=null
f2=null
f3=null
f4=null
if (field[0] != null) f0 = field[0].trim()
if (field[1] != null) f1 = field[1].trim()
if (field[2] != null) f2 = field[2].trim()
if (field[3] != null) f3 = field[3].trim()
if (field[4] != null) f4 = field[4].trim()
if (f0!=null && (v0=="序號" || i0>0) ) {
i0 = f0.isInteger() ? (f0 as int) : null
if (i0!=null) {
println "line: ${i0} "
}
v0=f0;v1=f1;
}
}
}*/
return ""

訂閱：意見 (Atom)

2015年11月19日 星期四