德意志血統 iDempiere 超越土產ERP 困境的最佳選擇: 2013

2013年12月13日星期五

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

2013年12月6日星期五

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)

2013年11月13日星期三

idempiere ERP "船期與貨況追蹤"

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

// MSSQL jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
mssql =Sql.newInstance('jdbc:sqlserver://192.72.1.42:1433;databaseName=Origin','aaa','111111',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )

// PostgreSQL
postsql =Sql.newInstance('jdbc:postgresql://127.0.0.1/taya1028','adempiere','adempiere','org.postgresql.Driver' )
def ptable= postsql.dataSet("船期與貨況追蹤") //上傳

def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
def id=0
def uu=' '
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");

mssql.eachRow( 'SELECT ShipmentID, InvoiceNo, 客戶單號, 料號, 船公司, 船名, 航次, 貨況追蹤.序號, 貨櫃號碼, 起運港, 結關日, 預定開航日, 實際開航日, 抵達港, 預計抵港日, 實際抵港日, 清關日 FROM 貨況追蹤' )
{
// select * from adempiere.AD_Sequence WHERE Name='船期與貨況追蹤'--1000346
postsql .query("select adempiere.nextid(1000321, 'N') as id , adempiere.generate_uuid() as uu ")
{ ResultSet rs -> if (rs.next()){ id = rs.getInt('id')
uu = rs.getString('uu') } }
postsql .query("SELECT p.packinglist_id, p.單號,p.單據日期,p.客戶_id,p.客戶英文全名,p.客戶全名,p.客戶送貨地址,p.傳真,p.電話, p.出口公司,p.起運港,p.抵達港,p.結關日,p.嘜頭_id,p.正嘜,p.側嘜, l.訂單_id,l.包裝編號,l.品號_id,l.品名,l.規格, o.訂單單號, o.客戶單號 FROM adempiere.packinglist p INNER JOIN adempiere.packinglistline l ON p.packinglist_id=l.packinglist_id INNER JOIN adempiere.訂單 o ON o.訂單_id=l.訂單_id WHERE p.單號='"+ it.InvoiceNo + "'")
{ ResultSet rs -> if (rs.next()){
id = rs.getInt('id')
uu = rs.getString('uu')
println " ${it.ShipmentID} ${it.InvoiceNo} "
ptable.add(
ad_client_id:client, ad_org_id:org, isactive: 'Y', created:m_created , createdby:uid, updated:m_created, updatedby:uid,
"船期與貨況追蹤_id":id, "船期與貨況追蹤_uu":uu,
ShipmentID:it.ShipmentID, InvoiceNo:it.InvoiceNo ,
"客戶單號":rs.getString('客戶單號'),
"料號":rs.getString('料號'),
packinglist_id:rs.getInt('packinglist_id'),
"船公司":it.船公司, "船名":it.船名, "航次":it.航次, // shippingorder:
"貨櫃號碼":it.貨櫃號碼, "起運港":it.起運港, "結關日":it.結關日,
"預定開航日":it.預定開航日, "實際開航日":it.實際開航日,
"抵達港":it.抵達港,
// "預計抵港日":it.預計抵港日,
"實際抵港日":it.實際抵港日,
"清關日":it.清關日
)
} }
}

def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)

2013年11月11日星期一

idempiere ERP '船期與貨況追蹤'

// 建立 '資料表' 來接受上傳

String tableName = '船期與貨況追蹤'

String tableDefinition = """CREATE TEXT TABLE $tableName (
  packinglist_id NUMERIC(10,0),
  "船公司"        VARCHAR(40),
  "船名"          VARCHAR(40),
  "航次"          VARCHAR(20),
   shippingorder  VARCHAR(20),
  "貨櫃號碼"       VARCHAR(20),
  "起運港"         VARCHAR(40),
  "結關日"         DATE,
  "預定開航日"  DATE,
  "實際開航日"  DATE,
  "抵達港"      VARCHAR(40),
  "預定抵港日"  date,
  "實際抵港日"  date,
  "清關日"      date,
  ad_client_id NUMERIC(10,0) NOT NULL,
  ad_org_id    NUMERIC(10,0) NOT NULL,
  isactive     CHAR(1) NOT NULL DEFAULT 'Y'::bpchar,
  created      timestamp without time zone NOT NULL DEFAULT now(),
  createdby    NUMERIC(10,0) NOT NULL,
  updated      timestamp without time zone NOT NULL DEFAULT now(),
  updatedby    NUMERIC(10,0) NOT NULL,
  "船期與貨況追蹤_id" NUMERIC(10,0),
  "船期與貨況追蹤_uu" VARCHAR(36)

 //atomic_number    INTEGER PRIMARY KEY,

);"""

 

//create a new file database and a table corresponding to the csv file

Sql sql = Sql.newInstance("jdbc:hsqldb:file:${testdbDir.absolutePath}/testdb", 'sa', ''

    ,'org.hsqldb.jdbcDriver')

sql.execute(tableDefinition)

 

//set the source to the csv file

sql.execute("SET TABLE elements SOURCE '${TEST_FILE_NAME};all_quoted=true'".toString())

 

//querying the database that's wrapping our CSV file

def elementsOver200Mass = sql.rows("SELECT * FROM $tableName WHERE atomic_mass > ?", [200])

def elementsBetween10And20 = sql.rows(

    "SELECT * FROM $tableName WHERE atomic_mass <= ? AND atomic_mass >= ?", [20, 10])

 

//simple db aggregates

def count = 0

sql.eachRow("SELECT count(1) FROM $tableName WHERE atomic_mass <= ?", [20]){row->

    count = row[0]

}

def avg = 0

sql.eachRow("SELECT avg(atomic_mass) FROM $tableName".toString()){row->

    avg = row[0]

}

2013年11月10日星期日

idempiere groovy cvs

import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB

def dbUrl = 'jdbc:postgresql://localhost:5432/taya1110'
def dbUser = 'adempiere'
def dbPassword = 'adempiere'
def driverClass = 'org.postgresql.Driver'
def sql = Sql.newInstance(dbUrl, dbUser, dbPassword, driverClass)
try { sql.execute("drop table \"船期與貨況追蹤上傳\"") } catch (Exception e) {}
// PL No.,客戶單號,料號,船公司,船名,航次,S/O no.,貨櫃號碼,起運港,結關日,預定開航日,實際開航日,抵達港,預計抵港日,實際抵港日,清關日
sql.execute('''create table \"船期與貨況追蹤上傳\"(
PLNo varchar(20),
\"客戶單號\" varchar(20),
\"料號\" varchar(20),
\"船公司\" varchar(20),
\"船名\" varchar(20),
\"航次\" varchar(20),
SONo varchar(20),
\"貨櫃號碼\" varchar(20),
\"起運港\" varchar(20),
\"結關日\" varchar(20),
\"預定開航日\" varchar(20),
\"實際開航日\" varchar(20),
\"抵達港\" varchar(20),
\"預計抵港日\" varchar(20),
\"實際抵港日\" varchar(20),
\"清關日\" varchar(20)
)''')
def ptable= sql.dataSet("船期與貨況追蹤上傳")
int v1=0
new File("C:\\Users\\albert\\Downloads\\船期與貨況追蹤1021110.csv").splitEachLine(",")
{fields ->
if (v1 > 0){
ptable.add(
PLNo:fields[0], "客戶單號":fields[1], "料號":fields[2], "船公司":fields[3], "船名":fields[4],,"航次":fields[5],
SONo:fields[6],"貨櫃號碼":fields[7],"起運港":fields[8],
"結關日": fields[9],
"預定開航日":fields[10],
"實際開航日":fields[11],
"抵達港":fields[12],
"預計抵港日":fields[13],
"實際抵港日":fields[14],
"清關日":fields[15]
)
}
v1=v1+1
}
return "完成"

訂閱：文章 (Atom)

2013年12月13日 星期五

iDempiere ERP v2.0 Groovy Rule Engine

2013年12月6日 星期五

iDempiere 抓 醫療系統 Informix

2013年11月13日 星期三