iDempiere HMR Integration 醫療系統整合 2

import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Types
import org.compiere.util.Env
import org.compiere.util.DB

int client=Env.getContextAsInt(A_Ctx, "AD_Client_ID");
int org=Env.getContextAsInt(A_Ctx, "AD_Org_ID");
int uid=Env.getContextAsInt(A_Ctx, "#AD_User_ID");

def t_start= System.currentTimeMillis()
String ss= DB.getSQLValueString(null,"select value from h_search where h_search_id = ?", A_Record_ID );
DB.executeUpdateEx( "delete from h_searchline where h_search_id="+A_Record_ID+ " " , A_TrxName);

def postsql = Sql.newInstance('jdbc:postgresql://127.0.0.1/taya1201','adempiere','adempiere','org.postgresql.Driver' )
def ptable= postsql.dataSet("h_searchline")   //上傳
def informix = Sql.newInstance( 'jdbc:informix-sqli://192.168.170.211:1534/hisopddb:informixserver=hisopdsrv;CLIENT_LOCALE=zh_tw.big5;','informix','db@w94u6', 'com.informix.jdbc.IfxDriver' );
def metadata = informix.connection.metaData
String[] tableTypes = ["TABLE"]
ResultSet rs = metadata.getTables(null, null, null, tableTypes );  //"userlist"
// getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
// "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
String tblName = null;
while(rs.next()){
   tblName = rs.getString('TABLE_NAME');
   println ">>>>>> "+tblName
   ResultSet tblMetadata = metadata.getColumns(null, '%', tblName, null);
// ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)  throws SQLException
   while(tblMetadata.next()){
      String TABLE_SCHEM= tblMetadata.getString("TABLE_SCHEM");
      String TABLE_NAME = tblMetadata.getString("TABLE_NAME");
      String COLUMN_NAME= tblMetadata.getString("COLUMN_NAME");
      println TABLE_SCHEM +"."+TABLE_NAME+"."+COLUMN_NAME;
      /*
      int DATA_TYPE= tblMetadata.getInt("DATA_TYPE");
      def v1 = 0
      def vline = 0
      if (DATA_TYPE==java.sql.Types.VARCHAR  || DATA_TYPE==java.sql.Types.CHAR ||
          DATA_TYPE==java.sql.Types.NVARCHAR || DATA_TYPE==java.sql.Types.NCHAR  )
      {
      def sqlcount = "SELECT COUNT(*)  as v1 FROM "+TABLE_SCHEM+"."+TABLE_NAME+" WHERE "+COLUMN_NAME+" LIKE '%"+ ss +"%' "
      informix .query(sqlcount )
      { ResultSet rscount ->
             while(rscount .next())
                   v1 = rscount .getInt('v1')
      }
      if (v1 > 0){
             String vsql =  sqlcount + "  ::  " + v1
             vline = vline  + 10
             int id = 0
             String uu = null
      // select * from adempiere.ad_sequence where name like 'H_Search' --1000353
      postsql.query("select adempiere.nextid(1000353, 'N') as id , adempiere.generate_uuid() as uu ")
      { ResultSet rsuu -> if (rsuu.next()){
                 id = rsuu.getInt('id')
                 uu = rsuu.getString('uu')  }   }

      ptable.add(
      ad_client_id:client, ad_org_id:org,  isactive: 'Y', createdby:uid,  updatedby:uid,
      h_search_id:A_Record_ID,
      h_searchline_id:id, h_searchline_uu:uu,
      line:vline,
      value:ss,
      name:ss,
      description:" [OPD] "+TABLE_SCHEM+"."+TABLE_NAME+"."+COLUMN_NAME+ "  找到:"+v1+"筆 : "
      )
                println  vsql  + "\t"
      }// if (v1 > 0){
      }// if (DATA_TYPE
   */
   } // while(tblMetadata.next())
} // while(rs.next())
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)