iDempiere ERP v2.0 Groovy Rule Engine

import java.sql.Timestamp
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.DB
import org.compiere.model.MInOut
import org.compiere.model.MInOutLine
import org.compiere.model.MProduct
int v_count = 0

String sql = "SELECT l.ad_client_id, l.ad_org_id, l.jd_ordergenshipmentline_id, l.jd_ordergenshipmentline_uu, l.c_bpartner_id, l.po_reference, l.documentno, l.m_product_id, l.m_product_value, l.m_product_name, l.qtyordered, l.qtydelivered, l.qtyundelivered, l.qtyshipment, l.c_orderline_id, l.c_order_id, o.c_bpartner_location_id, o.m_warehouse_id, o.poreference, o.deliveryrule,o.freightcostrule, o.deliveryviarule, o.priorityrule, o.c_doctype_id, d.m_locator_id, d.c_uom_id, od.c_doctypeshipment_id FROM adempiere.jd_ordergenshipmentline l INNER JOIN adempiere.c_order o ON o.c_order_id=l.c_order_id INNER JOIN adempiere.m_product d ON d.m_product_id=l.m_product_id INNER JOIN adempiere.c_doctype  od ON od.c_doctype_id=o.c_doctype_id "
// 請加上條件
// WHERE l.jd_ordergenshipment_id=? "
// A_Ctx  A_Trx  A_TrxName  A_Record_ID  A_AD_Client_ID  A_AD_User_ID  A_AD_PInstance_ID A_Table_ID
//
MInOut shipment = null;
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, A_Record_ID);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
   if  (shipment ==null){
       shipment = new  MInOut (A_Ctx, 0, A_TrxName);
       shipment.setAD_Client_ID (rs.getInt("ad_client_id"));
       shipment.setAD_Org_ID (rs.getInt("ad_org_id"));
       shipment.setC_DocType_ID(rs.getInt("c_doctypeshipment_id"));
       shipment.setDescription('jd_wsgenshipment');
       shipment.setC_Order_ID(rs.getInt("c_order_id"));
       shipment.setC_BPartner_ID(rs.getInt("c_bpartner_id"));
       shipment.setC_BPartner_Location_ID(rs.getInt("c_bpartner_location_id"));
       shipment.setM_Warehouse_ID(rs.getInt("m_warehouse_id"));
       shipment.setPOReference(rs.getString("poreference"));
       shipment.setDeliveryRule(rs.getString("deliveryrule"));
       shipment.setFreightCostRule(rs.getString("freightcostrule"));
       shipment.setDeliveryViaRule(rs.getString("deliveryviarule"));
       shipment.setPriorityRule(rs.getString("priorityrule"));
       shipment.save(A_TrxName);
    }
    sline = new  MInOutLine (shipment );
    sline.setDescription("jd_wsgenshipment");
    sline.setC_OrderLine_ID(rs.getInt("c_orderline_id"));
    sline.setM_Locator_ID(rs.getInt("m_locator_id"));
    sline.setM_Product_ID(rs.getInt("m_product_id"));
    sline.setC_UOM_ID(rs.getInt("c_uom_id"));
    sline.setMovementQty(rs.getBigDecimal("qtyshipment"));
    sline.setQtyEntered(rs.getBigDecimal("qtyshipment"));
    sline.save(A_TrxName);
}
rs.close();
pstmt.close();

result=sql

iDempiere 抓 醫療系統 Informix

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
def t_start= System.currentTimeMillis()
// PostgreSQL// A_Ctx   A_PO  A_Type  A_Event
def postgres= Sql.newInstance('jdbc:postgresql://127.0.0.1/taya1201','adempiere','adempiere','org.postgresql.Driver' )
def metadata = postgres.connection.metaData
String[] tableTypes = ["TABLE"]
ResultSet rs = metadata.getTables(null, "adempiere", "%", tableTypes );
// 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, 'adempiere', tblName, null);
// ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)  throws SQLException
   while(tblMetadata.next()){
      String REMARKS = tblMetadata.getString("REMARKS");
      String TABLE_CAT= tblMetadata.getString("TABLE_CAT");
      String TABLE_SCHEM= tblMetadata.getString("TABLE_SCHEM");
      String TABLE_NAME= tblMetadata.getString("TABLE_NAME");
      String COLUMN_NAME= tblMetadata.getString("COLUMN_NAME");
      int DATA_TYPE= tblMetadata.getInt("DATA_TYPE");
      String TYPE_NAME= tblMetadata.getString("TYPE_NAME");
      String COLUMN_SIZE= tblMetadata.getString("COLUMN_SIZE");
      String DECIMAL_DIGITS= tblMetadata.getString("DECIMAL_DIGITS");
      String NUM_PREC_RADIX= tblMetadata.getString("NUM_PREC_RADIX");
      String NULLABLE= tblMetadata.getString("NULLABLE");
     
      def v1 = 0
//    sql.rows("select * from PROJECT where name like 'Gra%' ")
      if (DATA_TYPE==java.sql.Types.VARCHAR || DATA_TYPE==java.sql.Types.LONGVARCHAR ||
           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 '%公司'  "
      postgres.query(sqlcount )
       { ResultSet rscount ->
          while(rscount .next())
             v1 = rscount .getInt('v1')
        }
        if (v1 > 0) println  sqlcount + " = " + v1  + "  " + "\t"
        }
   } // while(tblMetadata.next())
} // while(rs.next())
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)