"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.MUser
String sql = "select bp.c_bpartner_id, bpl.c_bpartner_location_id, p.ct_contact from c_bpartner bp inner join adempiere.z1_pcust p on p.ct_no = bp.value inner join adempiere.c_bpartner_location bpl on bpl.c_bpartner_id = bp.c_bpartner_id where length(p.ct_contact) > 1";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, A_AD_Client_ID);
ResultSet rs = pstmt.executeQuery();
// 只試作 10筆
while (rs.next()) {
String name = rs.getString("ct_contact");
int c_bpartner_id= rs.getInt("c_bpartner_id");
int c_bpartner_location_id= rs.getInt("c_bpartner_location_id");
MUser user = new MUser(A_Ctx, 0, A_TrxName);
user.setAD_Client_ID (1000000);
user.setName (name);
user.setValue (name);
user.setC_BPartner_ID (c_bpartner_id);
user.setC_BPartner_Location_ID (c_bpartner_location_id);
user.save();
}
rs.close();
pstmt.close();
result="""
2013年10月11日 星期五
groovy_8
"import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Timestamp DateRequired = (Timestamp) A_Value;
Timestamp DateDoc= (Timestamp)A_Tab.getValue("DateDoc");
if (DateRequired .compareTo(DateDoc) > 0 )
A_Tab.fireDataStatusEEvent("需求日>單據日","需求日"+DateRequired +">單據日"+DateDoc, false);
}
result="" "
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Timestamp DateRequired = (Timestamp) A_Value;
Timestamp DateDoc= (Timestamp)A_Tab.getValue("DateDoc");
if (DateRequired .compareTo(DateDoc) > 0 )
A_Tab.fireDataStatusEEvent("需求日>單據日","需求日"+DateRequired +">單據日"+DateDoc, false);
}
result="" "
groovy_9
"import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Integer SK_BOM_ID= (Integer)A_Tab.getValue("SK_BOM_ID");
Integer SK_BOMLine_ID= (Integer)A_Tab.getValue("SK_BOMLine_ID");
BigDecimal BOMQty=(BigDecimal) A_Value;
BigDecimal OldBOMQty=(BigDecimal) A_OldValue;
if (BOMQty==null) BOMQty=new BigDecimal(0);
if (OldBOMQty==null) OldBOMQty=new BigDecimal(0);
sql = "SELECT SUM(BOMQty) FROM SK_BOMLine WHERE SK_BOM_ID=? AND SK_BOMLine_ID <> ? ";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [SK_BOM_ID,SK_BOMLine_ID] );
BigDecimal tQty = sQty .add(BOMQty);
if ( tQty.compareTo(new BigDecimal(1) ) !=0 )
A_Tab.fireDataStatusEEvent("合計不等於一","合計:"+ tQty+"不等於一", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result="" "
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Integer SK_BOM_ID= (Integer)A_Tab.getValue("SK_BOM_ID");
Integer SK_BOMLine_ID= (Integer)A_Tab.getValue("SK_BOMLine_ID");
BigDecimal BOMQty=(BigDecimal) A_Value;
BigDecimal OldBOMQty=(BigDecimal) A_OldValue;
if (BOMQty==null) BOMQty=new BigDecimal(0);
if (OldBOMQty==null) OldBOMQty=new BigDecimal(0);
sql = "SELECT SUM(BOMQty) FROM SK_BOMLine WHERE SK_BOM_ID=? AND SK_BOMLine_ID <> ? ";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [SK_BOM_ID,SK_BOMLine_ID] );
BigDecimal tQty = sQty .add(BOMQty);
if ( tQty.compareTo(new BigDecimal(1) ) !=0 )
A_Tab.fireDataStatusEEvent("合計不等於一","合計:"+ tQty+"不等於一", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result="" "
groovy_7
"import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
String sql = null;
enum Day {
SUNDAY, MONDAY, TUESDAY, WEDNESDAY,
THURSDAY, FRIDAY, SATURDAY
}
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Integer SK_BOM_ID= (Integer)A_Tab.getValue("SK_BOM_ID");
Integer SK_BOMLine_ID= (Integer)A_Tab.getValue("SK_BOMLine_ID");
BigDecimal BOMQty=(BigDecimal) A_Value;
BigDecimal OldBOMQty=(BigDecimal) A_OldValue;
if (BOMQty==null) BOMQty=new BigDecimal(0);
if (OldBOMQty==null) OldBOMQty=new BigDecimal(0);
sql = "SELECT SUM(BOMQty) FROM SK_BOMLine WHERE SK_BOM_ID=? AND SK_BOMLine_ID <> ? ";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [SK_BOM_ID,SK_BOMLine_ID] );
BigDecimal tQty = sQty .add(BOMQty);
if ( tQty.compareTo(new BigDecimal(1) ) !=0 )
A_Tab.fireDataStatusEEvent("合計不等於一","合計:"+ tQty+"不等於一", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result="" "
import org.compiere.util.DB
import org.compiere.util.Msg
String sql = null;
enum Day {
SUNDAY, MONDAY, TUESDAY, WEDNESDAY,
THURSDAY, FRIDAY, SATURDAY
}
// A_WindowNo A_Tab A_Field A_Value A_OldValue A_Ctx BOMQty
if (A_Value!=null && A_OldValue!=A_Value){
Integer SK_BOM_ID= (Integer)A_Tab.getValue("SK_BOM_ID");
Integer SK_BOMLine_ID= (Integer)A_Tab.getValue("SK_BOMLine_ID");
BigDecimal BOMQty=(BigDecimal) A_Value;
BigDecimal OldBOMQty=(BigDecimal) A_OldValue;
if (BOMQty==null) BOMQty=new BigDecimal(0);
if (OldBOMQty==null) OldBOMQty=new BigDecimal(0);
sql = "SELECT SUM(BOMQty) FROM SK_BOMLine WHERE SK_BOM_ID=? AND SK_BOMLine_ID <> ? ";
BigDecimal sQty = DB.getSQLValueBD (null, sql, [SK_BOM_ID,SK_BOMLine_ID] );
BigDecimal tQty = sQty .add(BOMQty);
if ( tQty.compareTo(new BigDecimal(1) ) !=0 )
A_Tab.fireDataStatusEEvent("合計不等於一","合計:"+ tQty+"不等於一", false);
// A_Tab.setValue("Description", "目前總數:"+tQty ); // +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result="" "
groovy_6
"import groovy.sql.Sql
import java.sql.Timestamp
//jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
//MSSQL
sqlmssql =Sql.newInstance('jdbc:sqlserver://192.168.22.66:1433;databaseName=LYTDB00','sa','80485934',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
//PostgreSQL
sqlpostgresql =Sql.newInstance('jdbc:postgresql://192.168.22.90/idempiere','adempiere','adempiere','org.postgresql.Driver' )
def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
sqlmssql.eachRow( 'select * from spdt' )
{ println "$it.sd_sfg -- ${it.sd_date} -- ${it.sd_spno} -- ${it.sd_seq} -- ${it.sd_wkord} -- ${it.sd_skno} -- ${it.sd_sknm} -- ${it.sd_pfg} -- ${it.sd_wsno} -- ${it.sd_wsnm} -- ${it.sd_ucost} -- ${it.sd_skqty} -- ${it.sd_qty} -- ${it.sd_bqty} -- ${it.sd_memo} -- ${it.sd_ncost} -- ${it.sd_ufg} -- ${it.sd_ufgnm} -- ${it.sd_rqty} -- ${it.sd_fml1} -- ${it.sd_fml2} -- ${it.sd_hpfg} -- ${it.sd_hwsno} -- " //讓程式在背景執行
sqlpostgresql.execute('INSERT INTO z_spdt(sd_sfg, sd_date, sd_spno, sd_seq, sd_wkord, sd_skno, sd_sknm, sd_pfg, sd_wsno, sd_wsnm, sd_ucost, sd_skqty, sd_qty, sd_bqty, sd_memo, sd_ncost, sd_ufg, sd_ufgnm, sd_rqty, sd_fml1, sd_fml2, sd_hpfg, sd_hwsno) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[it.sd_sfg, it.sd_date, it.sd_spno, it.sd_seq, it.sd_wkord, it.sd_skno, it.sd_sknm, it.sd_pfg, it.sd_wsno, it.sd_wsnm, it.sd_ucost, it.sd_skqty, it.sd_qty, it.sd_bqty, it.sd_memo, it.sd_ncost, it.sd_ufg, it.sd_ufgnm, it.sd_rqty, it.sd_fml1, it.sd_fml2, it.sd_hpfg, it.sd_hwsno])}
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)"
import java.sql.Timestamp
//jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
//MSSQL
sqlmssql =Sql.newInstance('jdbc:sqlserver://192.168.22.66:1433;databaseName=LYTDB00','sa','80485934',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
//PostgreSQL
sqlpostgresql =Sql.newInstance('jdbc:postgresql://192.168.22.90/idempiere','adempiere','adempiere','org.postgresql.Driver' )
def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
sqlmssql.eachRow( 'select * from spdt' )
{ println "$it.sd_sfg -- ${it.sd_date} -- ${it.sd_spno} -- ${it.sd_seq} -- ${it.sd_wkord} -- ${it.sd_skno} -- ${it.sd_sknm} -- ${it.sd_pfg} -- ${it.sd_wsno} -- ${it.sd_wsnm} -- ${it.sd_ucost} -- ${it.sd_skqty} -- ${it.sd_qty} -- ${it.sd_bqty} -- ${it.sd_memo} -- ${it.sd_ncost} -- ${it.sd_ufg} -- ${it.sd_ufgnm} -- ${it.sd_rqty} -- ${it.sd_fml1} -- ${it.sd_fml2} -- ${it.sd_hpfg} -- ${it.sd_hwsno} -- " //讓程式在背景執行
sqlpostgresql.execute('INSERT INTO z_spdt(sd_sfg, sd_date, sd_spno, sd_seq, sd_wkord, sd_skno, sd_sknm, sd_pfg, sd_wsno, sd_wsnm, sd_ucost, sd_skqty, sd_qty, sd_bqty, sd_memo, sd_ncost, sd_ufg, sd_ufgnm, sd_rqty, sd_fml1, sd_fml2, sd_hpfg, sd_hwsno) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[it.sd_sfg, it.sd_date, it.sd_spno, it.sd_seq, it.sd_wkord, it.sd_skno, it.sd_sknm, it.sd_pfg, it.sd_wsno, it.sd_wsnm, it.sd_ucost, it.sd_skqty, it.sd_qty, it.sd_bqty, it.sd_memo, it.sd_ncost, it.sd_ufg, it.sd_ufgnm, it.sd_rqty, it.sd_fml1, it.sd_fml2, it.sd_hpfg, it.sd_hwsno])}
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)"
groovy_5
"import groovy.sql.Sql
import java.sql.Timestamp
//jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
//MSSQL
sqlmssql =Sql.newInstance('jdbc:sqlserver://192.168.22.66:1433;databaseName=LYTDB00','sa','80485934',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
//PostgreSQL
sqlpostgresql =Sql.newInstance('jdbc:postgresql://192.168.22.90/idempiere','adempiere','adempiere','org.postgresql.Driver' )
def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
sqlmssql.eachRow( 'select * from sphd' )
{ println "$it.sh_sfg -- ${it.sh_gfg} -- ${it.sh_date} -- ${it.sh_spno} -- ${it.sh_mnno} -- ${it.sh_skno} -- ${it.sh_sknm} -- ${it.sh_afno} -- ${it.sh_dsno} -- ${it.sh_opfg} -- ${it.sh_owsno} -- ${it.sh_owsnm} -- ${it.sh_owkord} -- ${it.sh_pfg} -- ${it.sh_wsno} -- ${it.sh_wsnm} -- ${it.sh_wkord} -- ${it.sh_sskno} -- ${it.sh_ssknm} -- ${it.sh_qty} -- ${it.sh_ufg} -- ${it.sh_rqty} -- ${it.sh_pcost} -- ${it.sh_ecost} -- ${it.sh_wchg} -- ${it.sh_inno} -- ${it.sh_bno} -- ${it.sh_memo} -- ${it.sh_tcost} -- ${it.sh_acost} -- ${it.sh_dtqty} -- ${it.sh_ncost} -- ${it.sh_tfg} -- ${it.sh_psno} -- ${it.sh_dpno} -- ${it.sh_dpnm} -- ${it.sh_tlno} -- ${it.sh_ifg} -- ${it.sh_rate_nm} -- ${it.sh_rate} -- ${it.sh_mcost} -- " //讓程式在背景執行
sqlpostgresql.execute('INSERT INTO z_sphd(sh_sfg, sh_gfg, sh_date, sh_spno, sh_mnno, sh_skno, sh_sknm, sh_afno, sh_dsno, sh_opfg, sh_owsno, sh_owsnm, sh_owkord, sh_pfg, sh_wsno, sh_wsnm, sh_wkord, sh_sskno, sh_ssknm, sh_qty, sh_ufg, sh_rqty, sh_pcost, sh_ecost, sh_wchg, sh_inno, sh_bno, sh_memo, sh_tcost, sh_acost, sh_dtqty, sh_ncost, sh_tfg, sh_psno, sh_dpno, sh_dpnm, sh_tlno, sh_ifg, sh_rate_nm, sh_rate, sh_mcost) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[it.sh_sfg, it.sh_gfg, it.sh_date, it.sh_spno, it.sh_mnno, it.sh_skno, it.sh_sknm, it.sh_afno, it.sh_dsno, it.sh_opfg, it.sh_owsno, it.sh_owsnm, it.sh_owkord, it.sh_pfg, it.sh_wsno, it.sh_wsnm, it.sh_wkord, it.sh_sskno, it.sh_ssknm, it.sh_qty, it.sh_ufg, it.sh_rqty, it.sh_pcost, it.sh_ecost, it.sh_wchg, it.sh_inno, it.sh_bno, it.sh_memo, it.sh_tcost, it.sh_acost, it.sh_dtqty, it.sh_ncost, it.sh_tfg, it.sh_psno, it.sh_dpno, it.sh_dpnm, it.sh_tlno, it.sh_ifg, it.sh_rate_nm, it.sh_rate, it.sh_mcost])}
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)"
import java.sql.Timestamp
//jdbc:sqlserver://" + dbHost + ":" + dbPort + ";databaseName=" + dbName + ";";
//MSSQL
sqlmssql =Sql.newInstance('jdbc:sqlserver://192.168.22.66:1433;databaseName=LYTDB00','sa','80485934',
'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
//PostgreSQL
sqlpostgresql =Sql.newInstance('jdbc:postgresql://192.168.22.90/idempiere','adempiere','adempiere','org.postgresql.Driver' )
def isActive='Y'
def H_ID=1000000
def m_created = new Timestamp(System.currentTimeMillis());
def t_start = System.currentTimeMillis()
sqlmssql.eachRow( 'select * from sphd' )
{ println "$it.sh_sfg -- ${it.sh_gfg} -- ${it.sh_date} -- ${it.sh_spno} -- ${it.sh_mnno} -- ${it.sh_skno} -- ${it.sh_sknm} -- ${it.sh_afno} -- ${it.sh_dsno} -- ${it.sh_opfg} -- ${it.sh_owsno} -- ${it.sh_owsnm} -- ${it.sh_owkord} -- ${it.sh_pfg} -- ${it.sh_wsno} -- ${it.sh_wsnm} -- ${it.sh_wkord} -- ${it.sh_sskno} -- ${it.sh_ssknm} -- ${it.sh_qty} -- ${it.sh_ufg} -- ${it.sh_rqty} -- ${it.sh_pcost} -- ${it.sh_ecost} -- ${it.sh_wchg} -- ${it.sh_inno} -- ${it.sh_bno} -- ${it.sh_memo} -- ${it.sh_tcost} -- ${it.sh_acost} -- ${it.sh_dtqty} -- ${it.sh_ncost} -- ${it.sh_tfg} -- ${it.sh_psno} -- ${it.sh_dpno} -- ${it.sh_dpnm} -- ${it.sh_tlno} -- ${it.sh_ifg} -- ${it.sh_rate_nm} -- ${it.sh_rate} -- ${it.sh_mcost} -- " //讓程式在背景執行
sqlpostgresql.execute('INSERT INTO z_sphd(sh_sfg, sh_gfg, sh_date, sh_spno, sh_mnno, sh_skno, sh_sknm, sh_afno, sh_dsno, sh_opfg, sh_owsno, sh_owsnm, sh_owkord, sh_pfg, sh_wsno, sh_wsnm, sh_wkord, sh_sskno, sh_ssknm, sh_qty, sh_ufg, sh_rqty, sh_pcost, sh_ecost, sh_wchg, sh_inno, sh_bno, sh_memo, sh_tcost, sh_acost, sh_dtqty, sh_ncost, sh_tfg, sh_psno, sh_dpno, sh_dpnm, sh_tlno, sh_ifg, sh_rate_nm, sh_rate, sh_mcost) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[it.sh_sfg, it.sh_gfg, it.sh_date, it.sh_spno, it.sh_mnno, it.sh_skno, it.sh_sknm, it.sh_afno, it.sh_dsno, it.sh_opfg, it.sh_owsno, it.sh_owsnm, it.sh_owkord, it.sh_pfg, it.sh_wsno, it.sh_wsnm, it.sh_wkord, it.sh_sskno, it.sh_ssknm, it.sh_qty, it.sh_ufg, it.sh_rqty, it.sh_pcost, it.sh_ecost, it.sh_wchg, it.sh_inno, it.sh_bno, it.sh_memo, it.sh_tcost, it.sh_acost, it.sh_dtqty, it.sh_ncost, it.sh_tfg, it.sh_psno, it.sh_dpno, it.sh_dpnm, it.sh_tlno, it.sh_ifg, it.sh_rate_nm, it.sh_rate, it.sh_mcost])}
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)"
groovy_4
"
// A_Tab.setValue("Help", 'abcdefg'.getAt( 3 ))
import org.compiere.util.DB
import java.util.List
def sql = " select name||','||value from m_product where m_product_id=? ";
String r = DB.getSQLValueString(null, sql, [50002] );
// String r = DB.getSQLValueString(null," select name||','||value from m_product where m_product_id=? ",50002);
if (r==null) r = "Can't Find, nothing";
def s = r.tokenize(',;') ;
// 'he,she;it,;they'.tokenize(',;') ;
def dt = Date.parse("E MMM dd H:m:s z yyyy", "Tue Aug 10 16:02:43 PST 2010")
def date = new Date()
def formattedDate = date.format('yyyy-MM-dd')
A_Tab.setValue("Help", s.first()+" , "+s.last() + " , " + dt + " , " + formattedDate )
"
// A_Tab.setValue("Help", 'abcdefg'.getAt( 3 ))
import org.compiere.util.DB
import java.util.List
def sql = " select name||','||value from m_product where m_product_id=? ";
String r = DB.getSQLValueString(null, sql, [50002] );
// String r = DB.getSQLValueString(null," select name||','||value from m_product where m_product_id=? ",50002);
if (r==null) r = "Can't Find, nothing";
def s = r.tokenize(',;') ;
// 'he,she;it,;they'.tokenize(',;') ;
def dt = Date.parse("E MMM dd H:m:s z yyyy", "Tue Aug 10 16:02:43 PST 2010")
def date = new Date()
def formattedDate = date.format('yyyy-MM-dd')
A_Tab.setValue("Help", s.first()+" , "+s.last() + " , " + dt + " , " + formattedDate )
"
訂閱：
文章 (Atom)