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

2014年1月27日星期一

groovy:getUserForOrder

groovy:getUserForOrder
Callout
=================
import org.compiere.util.DB
import org.compiere.util.Env
import java.math.BigDecimal

int id =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("訂單確認者_ID", new Integer(id) );

result=""

groovy:getCfmUserForOrder

groovy:getCfmUserForOrder
Callout
====================
import org.compiere.util.DB
import org.compiere.util.Env
import java.math.BigDecimal

A_Tab.setValue("審核狀態", "已確認" );

int uid =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
int rid =Env.getContextAsInt(A_Ctx, "#AD_Role_ID");
if (uid==1000729 || rid==1000011)
{
A_Tab.setValue("訂單確認者_ID", new Integer(uid) );

}

result=""

groovy:getVendorForMaterialReceipt

groovy:getVendorForMaterialReceipt
Callout
=========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"付款條件_id\", \"交易條件_id\" FROM \"供應商註記\" WHERE \"供應商_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("付款條件_id") > 0) A_Tab.setValue("付款條件_ID",new Integer(rs.getInt("付款條件_id")));
if (rs.getInt("交易條件_id") > 0) A_Tab.setValue("交易條件_ID",new Integer(rs.getInt("交易條件_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:getPOLineForMRLine

groovy:getPOLineForMRLine
Callout
====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"品名\",\"規格\" FROM \"採購單明細\" WHERE \"採購單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));

if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));

if (rs. getString ("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getMOforMaterialIssue

groovy:getMOforMaterialIssue
Callout
====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\",\"需領用量\" FROM \"需求用量\" WHERE \"需求用量_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));

if (rs.getBigDecimal ("需領用量") != null) A_Tab.setValue("需領料量",rs.getBigDecimal ("需領用量"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getMODemandforMaterialIssue

groovy:getMODemandforMaterialIssue
Callout
==========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\",\"需領用量\" FROM \"需求用量\" WHERE \"需求用量_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));

if (rs.getBigDecimal ("需領用量") != null) A_Tab.setValue("需領料量",rs.getBigDecimal ("需領用量"));

}
rs.close();
pstmt.close();
}
result=""

groovy:get採購單明細拉請購單明細品號與數量

groovy:get採購單明細拉請購單明細品號與數量
Callout
===================================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\",\"幣別_id\",\"專案_id\",\"庫別_id\",\"請購數量\",\"請購單價\" FROM \"請購單明細\" WHERE \"請購單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_id",new Integer(rs.getInt("幣別_id")));
if (rs.getInt("專案_id") > 0) A_Tab.setValue("專案_id",new Integer(rs.getInt("專案_id")));
if (rs.getInt("庫別_id") > 0) A_Tab.setValue("庫別_id",new Integer(rs.getInt("庫別_id")));
if (rs.getBigDecimal ("請購數量") != null) A_Tab.setValue("採購數量",rs.getBigDecimal ("請購數量"));
if (rs.getBigDecimal ("請購單價") != null) A_Tab.setValue("採購單價",rs.getBigDecimal ("請購單價"));

}
rs.close();
pstmt.close();
}
result=""

groovy:get採購單撈供應商

groovy:get採購單撈供應商
Callout
===================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"稅別碼_id\",\"付款條件_id\",\"交易條件_id\" FROM \"供應商\" WHERE \"供應商_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {

if (rs.getInt("稅別碼_id") > 0) A_Tab.setValue("稅別碼",new Integer(rs.getInt(""稅別碼_id")));

if (rs.getInt("付款條件_id") > 0) A_Tab.setValue("付款條件_ID",new Integer(rs.getInt(""付款條件_id")));

if (rs.getInt("交易條件_id") > 0) A_Tab.setValue("交易條件_ID",new Integer(rs.getInt(""交易條件_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:getVendorForPO

groovy:getVendorForPO
Callout
=================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"稅別碼_id\",\"付款條件_id\", \"交易條件_id\" FROM \"供應商註記\" WHERE \"供應商_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("稅別碼_id") > 0) A_Tab.setValue("稅別碼_ID",new Integer(rs.getInt("稅別碼_id")));
if (rs.getInt("付款條件_id") > 0) A_Tab.setValue("付款條件_ID",new Integer(rs.getInt("付款條件_id")));
if (rs.getInt("交易條件_id") > 0) A_Tab.setValue("交易條件_ID",new Integer(rs.getInt("交易條件_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:get採購單拉廠別帶出送廠別資訊

groovy:get採購單拉廠別帶出送廠別資訊
Callout
==============================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"地址\" FROM \"廠別\" WHERE \"廠別_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {

if (rs.getString("地址") != null) A_Tab.setValue("送貨地址",rs.getString("地址"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getCfm

groovy:getCfm
Callout
==========
import org.compiere.util.DB
import org.compiere.util.Env
import java.math.BigDecimal

if ( A_Value != null && !(A_Value.equals(A_OldValue) ) )
{
int id =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("確認者_ID", new Integer(id) );
}

result=""

groovy:get採購單拉採購人員帶出聯絡人

groovy:get採購單拉採購人員帶出聯絡人
Callout
==============================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"員工姓名\" FROM \"採購人員\" WHERE \"採購人員_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {

if (rs.getString("員工姓名") != null) A_Tab.setValue("聯絡人",rs.getString("員工姓名"));

}
rs.close();
pstmt.close();
}
result=""

groovy:get入庫單拉製令資料

groovy:get入庫單拉製令資料
Callout
=====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\",\"預計產量\" FROM \"製造命令\" WHERE \"製造命令_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));

if (rs.getBigDecimal ("預計產量") != null) A_Tab.setValue("入庫數量",rs.getBigDecimal ("預計產量"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getCSV

groovy:getCSV
程序
===========
import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB

def dbUrl = 'jdbc:postgresql://127.0.0.1:5432/taya1028'
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:\\大亞upload\\船期與貨況追蹤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 "完成"

groovy:get進貨單明細拉採購單明細品號與數量

groovy:get進貨單明細拉採購單明細品號與數量
Callout
====================================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"品名\",\"規格\",\"採購數量\",\"單位_id\",\"採購單價\",\"庫別_id\",\"幣別_id\" FROM \"採購單明細\" WHERE \"採購單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));
if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_ID",new Integer(rs.getInt("單位_id")));
if (rs.getInt("庫別_id") > 0) A_Tab.setValue("庫別_ID",new Integer(rs.getInt("庫別_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_ID",new Integer(rs.getInt("幣別_id")));
if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));

if (rs. getString ("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));

if (rs.getBigDecimal ("採購數量") != null) A_Tab.setValue("進貨數量",rs.getBigDecimal ("採購數量"));
if (rs.getBigDecimal ("採購單價") != null) A_Tab.setValue("單位進價",rs.getBigDecimal ("採購單價"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getCust4PackingList

groovy:getCust4PackingList
Callout
===================
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

if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql = "SELECT c.\"客戶全名\",c.\"客戶英文全名\",r.\"客戶發票地址\", r.\"客戶送貨地址\",r.\"電話\",r.\"傳真\" FROM \"客戶\" c INNER JOIN \"客戶註記\" r ON r.\"客戶_id\"=c.\"客戶_id\" WHERE c.\"客戶_id\"=? ";

PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getString("客戶全名") != null) A_Tab.setValue("客戶全名",rs.getString("客戶全名"));
if (rs.getString("客戶英文全名") != null)A_Tab.setValue("客戶英文全名",rs.getString("客戶英文全名"));
// if (rs.getString("客戶發票地址") != null) A_Tab.setValue("客戶發票地址",rs.getString("客戶發票地址"));
if (rs.getString("客戶送貨地址") != null) A_Tab.setValue("客戶送貨地址",rs.getString("客戶送貨地址"));
if (rs.getString("電話") != null) A_Tab.setValue("電話",rs.getString("電話"));
if (rs.getString("傳真") != null) A_Tab.setValue("傳真",rs.getString("傳真"));
}
rs.close();
pstmt.close();
}
result=""

result=""

groovy:getPackingList4Invoice

groovy:getPackingList4Invoice
Callout
=====================
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
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql = "SELECT \"單號\",\"單據日期\",\"客戶_id\",\"客戶英文全名\",\"客戶全名\",\"客戶送貨地址\",\"傳真\",\"電話\",\"起運港\",\"抵達港\",\"嘜頭_id\",\"正嘜\",\"側嘜\",\"總數量\",\"單位_id\" FROM PackingList WHERE PackingList_ID=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getString("單號") != null) A_Tab.setValue("單號",rs.getString("單號"));
if (rs.getTimestamp("單據日期") != null) A_Tab.setValue("單據日期",rs.getTimestamp("單據日期"));
if (rs.getString("客戶_id") != null) A_Tab.setValue("客戶_ID",rs.getString("客戶_id"));
if (rs.getString("客戶全名") != null) A_Tab.setValue("客戶全名",rs.getString("客戶全名"));
if (rs.getString("客戶英文全名") != null)A_Tab.setValue("客戶英文全名",rs.getString("客戶英文全名"));

if (rs.getString("客戶送貨地址") != null) A_Tab.setValue("客戶送貨地址",rs.getString("客戶送貨地址"));
if (rs.getString("電話") != null) A_Tab.setValue("電話",rs.getString("電話"));
if (rs.getString("傳真") != null) A_Tab.setValue("傳真",rs.getString("傳真"));

if (rs.getString("起運港") != null) A_Tab.setValue("起運港",rs.getString("起運港"));
if (rs.getString("抵達港") != null) A_Tab.setValue("抵達港",rs.getString("抵達港"));
if (rs.getInt("嘜頭_id") > 0 ) A_Tab.setValue("嘜頭_id", new Integer(rs.getInt("嘜頭_id")));
if (rs.getString("正嘜") != null) A_Tab.setValue("正嘜",rs.getString("正嘜"));
if (rs.getString("側嘜") != null) A_Tab.setValue("側嘜",rs.getString("側嘜"));

if (rs.getBigDecimal("總數量") != null) A_Tab.setValue("總數量",rs.getBigDecimal("總數量"));
if (rs.getInt("單位_id") > 0 ) A_Tab.setValue("單位_ID", new Integer(rs.getInt("單位_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:getMOforMaterialIssue-FC

groovy:getMOforMaterialIssue-FC
Callout
========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\",\"需領用量\" FROM \"需求用量\" WHERE \"需求用量_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_ID",new Integer(rs.getInt("品號_id")));

if (rs.getBigDecimal ("需領用量") != null) A_Tab.setValue("需領料量",rs.getBigDecimal ("需領用量"));

if (rs.getBigDecimal ("需領用量") != null) A_Tab.setValue("實領料量",rs.getBigDecimal ("需領用量"));

}
rs.close();
pstmt.close();
}
result=""

groovy:getShipSchedulerBackup

groovy:getShipSchedulerBackup
程序
======================
import groovy.sql.Sql
import java.sql.Timestamp
import org.compiere.util.DB

// 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()

mssql.eachRow( 'SELECT ShipmentID, InvoiceNo, 客戶單號, 料號, 船公司, 船名, 航次, 貨況追蹤.序號, 貨櫃號碼, 起運港, 結關日, 預定開航日, 實際開航日, 抵達港, 預計抵港日, 實際抵港日, 清關日 FROM 貨況追蹤' )
{
println " ${it.ShipmentID} ${it.InvoiceNo} "
/// postsql.execute('INSERT INTO z_sstock(sk_no, sk_name) VALUES (?,?)', [it.sk_no,it.sk_name])
ptable.add(
PLNo:it.ShipmentID, "客戶單號":it.客戶單號 ,"料號":it.料號,"船公司":it.船公司, "船名":it.船名, "航次":it.航次, // SONo:
"貨櫃號碼":it.貨櫃號碼, "起運港":it.起運港, "結關日":it.結關日, "預定開航日":it.預定開航日, "實際開航日":it.實際開航日,
"抵達港":it.抵達港, "預計抵港日":it.預計抵港日,"實際抵港日":it.實際抵港日,
"清關日":it.清關日
)
}

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

groovy:getShipScheduler

groovy:getShipScheduler
程序
=================
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)

groovy:製造命令需求領用

groovy:製造命令需求領用
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB

// PostgreSQL// A_Ctx A_PO A_Type A_Event
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 parent_id=((Integer)A_PO.getValue("製造命令_ID")).intValue();
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 "品號_id","品名","規格","需領用量","已領用量","未領用量" 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') }
}
println " ${it.品號_id} ${it.品名} "

ptable.add(
"品號_id": it.品號_id, "品名": it.品名, "規格":it.規格, "需領用量":it.需領用量, "已領用量":it.已領用量, "未領用量":it.已領用量
ad_client_id:client, ad_org_id:org, isactive: 'Y', created:m_created , createdby:uid, updated:m_created, updatedby:uid,
"需求用量_id":id, "需求用量_uu":uu, "製造命令_id":record_ID
)
}

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

groovy:船期與貨況追蹤

groovy:船期與貨況追蹤
程序
==================
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)

groovy:銷退單拉客戶資料

groovy:銷退單拉客戶資料
Callout
===================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT a.\"客戶全名\", a.\"客戶英文全名\",b.\"統一編號\",b.\"聯絡人\",b.\"客戶送貨地址\",c.\"稅別碼_id\" FROM \"客戶\" a LEFT OUTER JOIN \"客戶註記\" b ON a.\"客戶_id\"=b.\"客戶_id\" LEFT OUTER JOIN \"客戶交易註記\" c ON a.\"客戶_id\"=c.\"客戶_id\" WHERE a.\"客戶_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 if (rs.next()) {
if (rs.next()) {

if (rs.getString("客戶全名") != null) A_Tab.setValue("客戶全名",rs. getString ("客戶全名"));

if (rs.getString("客戶英文全名") != null) A_Tab.setValue("客戶英文全名",rs. getString ("客戶英文全名"));

if (rs.getString("統一編號") != null) A_Tab.setValue("統一編號",rs. getString ("統一編號"));

if (rs.getInt("稅別碼_id") > 0) A_Tab.setValue("稅別碼_id",new Integer(rs.getInt("稅別碼_id")));

if (rs.getString("聯絡人") != null) A_Tab.setValue("聯絡人",rs. getString ("聯絡人"));

if (rs.getString("客戶送貨地址") != null) A_Tab.setValue("取貨地址",rs. getString ("客戶送貨地址"));

}
rs.close();
pstmt.close();
}
result=""

groovy:銷退單明細拉銷貨單明細

groovy:銷退單明細拉銷貨單明細
Callout
========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"訂單別_id\",\"訂單_id\",\"訂單明細_id\",\"品號_id\",\"品名\",\"規格\",\"單位_id\",\"客戶單號\",\"客戶品號\",\"單價\",\"批號\" FROM \"銷貨單明細\" WHERE \"銷貨單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()) {
if (rs.next()) {

if (rs.getInt("訂單別_id") > 0) A_Tab.setValue("訂單別_id",new Integer(rs.getInt("訂單別_id")));

if (rs.getInt("訂單_id") > 0) A_Tab.setValue("訂單_id",new Integer(rs.getInt("訂單_id")));

if (rs.getInt("訂單明細_id") > 0) A_Tab.setValue("訂單明細_id",new Integer(rs.getInt("訂單明細_id")));

if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));

if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_id",new Integer(rs.getInt("單位_id")));

if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));

if (rs.getString("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));

if (rs.getString("客戶單號") != null) A_Tab.setValue("客戶單號",rs. getString ("客戶單號"));
if (rs.getString("客戶品號") != null) A_Tab.setValue("客戶品號",rs. getString ("客戶品號"));
if (rs.getString("批號") != null) A_Tab.setValue("批號",rs. getString ("批號"));
if (rs.getBigDecimal ("單價") != null) A_Tab.setValue("單價",rs.getBigDecimal ("單價"));

}
rs.close();
pstmt.close();
}
result=""

groovy:採購變更單拉採購單資料

groovy:採購變更單拉採購單資料
Callout
=========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"供應商_id\", \"廠別_id\",\"採購人員_id\",\"幣別_id\",\"稅別碼_id\", \"訂金比率\",\"營業稅率\",\"送貨地址\",\"聯絡人\",\"交易條件_id\",\"付款條件_id\" FROM \"採購單\" WHERE \"採購單_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("供應商_id") > 0) A_Tab.setValue("供應商_id",new Integer(rs.getInt("供應商_id")));
if (rs.getInt("廠別_id") > 0) A_Tab.setValue("廠別_id",new Integer(rs.getInt("廠別_id")));
if (rs.getInt("採購人員_id") > 0) A_Tab.setValue("採購人員_id",new Integer(rs.getInt("採購人員_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_id",new Integer(rs.getInt("幣別_id")));
if (rs.getInt("稅別碼_id") > 0) A_Tab.setValue("稅別碼_id",new Integer(rs.getInt("稅別碼_id")));
if (rs.getInt("交易條件_id") > 0) A_Tab.setValue("交易條件_id",new Integer(rs.getInt("交易條件_id")));
if (rs.getInt("付款條件_id") > 0) A_Tab.setValue("付款條件_id",new Integer(rs.getInt("付款條件_id")));
if (rs.getBigDecimal ("訂金比率") != null) A_Tab.setValue("訂金比率",rs.getBigDecimal ("訂金比率"));
if (rs.getBigDecimal ("營業稅率") != null) A_Tab.setValue("營業稅率",rs.getBigDecimal ("營業稅率"));
if (rs.getString("送貨地址") != null) A_Tab.setValue("送貨地址",rs. getString ("送貨地址"));
if (rs.getString("聯絡人") != null) A_Tab.setValue("聯絡人",rs. getString ("聯絡人"));

}
rs.close();
pstmt.close();
}
result=""

groovy:採購變更單明細拉採購單資料

groovy:採購變更單明細拉採購單資料
Callout
============================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"品名\",\"規格\",\"庫別_id\",\"幣別_id\",\"單位_id\", \"專案_id\",\"庫別名稱\",\"採購數量\",\"已交數量\",\"採購單價\",\"營業稅率\",\"驗退數量\",\"批號\",\"單位進價\",\"原幣進貨金額\",\"原幣扣款金額\",\"進貨費用\",\"合計\",\"原幣未稅金額\",\"原幣稅額\",\"本幣未稅金額\",\"本幣稅額\",\"扣款說明\",\"暫不付款\",\"檢驗狀態\",\"結帳碼\" FROM \"採購單明細\" WHERE \"採購單明細_id\"=? ";

PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));
if (rs.getInt("庫別_id") > 0) A_Tab.setValue("庫別_id",new Integer(rs.getInt("庫別_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_id",new Integer(rs.getInt("幣別_id")));
if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_id",new Integer(rs.getInt("單位_id")));
if (rs.getInt("專案_id") > 0) A_Tab.setValue("專案_id",new Integer(rs.getInt("專案_id")));
if (rs.getBigDecimal ("採購數量") != null) A_Tab.setValue("採購數量",rs.getBigDecimal ("採購數量"));
if (rs.getBigDecimal ("已交數量") != null) A_Tab.setValue("已交數量",rs.getBigDecimal ("已交數量"));
if (rs.getBigDecimal ("採購單價") != null) A_Tab.setValue("採購單價",rs.getBigDecimal ("採購單價"));
if (rs.getBigDecimal ("營業稅率") != null) A_Tab.setValue("營業稅率",rs.getBigDecimal ("營業稅率"));
if (rs.getBigDecimal ("驗退數量") != null) A_Tab.setValue("驗退數量",rs.getBigDecimal ("驗退數量"));
if (rs.getBigDecimal ("原幣進貨金額") != null) A_Tab.setValue("原幣進貨金額",rs.getBigDecimal ("原幣進貨金額"));
if (rs.getBigDecimal ("原幣扣款金額") != null) A_Tab.setValue("原幣扣款金額",rs.getBigDecimal ("原幣扣款金額"));
if (rs.getBigDecimal ("進貨費用") != null) A_Tab.setValue("進貨費用",rs.getBigDecimal ("進貨費用"));
if (rs.getBigDecimal ("合計") != null) A_Tab.setValue("合計",rs.getBigDecimal ("合計"));
if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));
if (rs.getString("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));
if (rs.getString("庫別名稱") != null) A_Tab.setValue("庫別名稱",rs. getString ("庫別名稱"));
if (rs.getString("批號") != null) A_Tab.setValue("批號",rs. getString ("批號"));
if (rs.getString("扣款說明") != null) A_Tab.setValue("扣款說明",rs. getString ("扣款說明"));
if (rs.getString("暫不付款") != null) A_Tab.setValue("暫不付款",rs. getString ("暫不付款"));
if (rs.getString("檢驗狀態") != null) A_Tab.setValue("檢驗狀態",rs. getString ("檢驗狀態"));
if (rs.getString("結帳碼") != null) A_Tab.setValue("結帳碼",rs. getString ("結帳碼"));
}
rs.close();
pstmt.close();
}
result=""

groovy:退貨單拉供應商資料

groovy:退貨單拉供應商資料
Callout
=====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT a.\"付款條件_id\", a.\"交易條件_id\",a.\"聯絡人一\",b.\"統一編號\" FROM \"供應商註記\" a LEFT OUTER JOIN \"供應商\" b ON a.\"供應商_id\"=b.\"供應商_id\" WHERE a.\"供應商_id\"=? ";

PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {

if (rs.getInt("付款條件_id") > 0) A_Tab.setValue("付款條件_id",new Integer(rs.getInt("付款條件_id")));
if (rs.getInt("交易條件_id") > 0) A_Tab.setValue("交易條件_id",new Integer(rs.getInt("交易條件_id")));
if (rs.getString("聯絡人一") != null) A_Tab.setValue("聯絡人",rs. getString ("聯絡人一"));
if (rs.getString("統一編號") != null) A_Tab.setValue("統一編號",rs. getString ("統一編號"));

}
rs.close();
pstmt.close();
}
result=""

groovy:退貨單明細拉進貨單資料

groovy:退貨單明細拉進貨單資料
Callout
========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"品名\",\"規格\",\"單位_id\",\"庫別_id\", \"批號\",\"單位進價\" FROM \"進貨單明細\" WHERE \"進貨單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));
if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_id",new Integer(rs.getInt("單位_id")));
if (rs.getInt("庫別_id") > 0) A_Tab.setValue("庫別_id",new Integer(rs.getInt("庫別_id")));
if (rs.getBigDecimal ("單位進價") != null) A_Tab.setValue("單位進價",rs.getBigDecimal ("單位進價"));
if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));
if (rs.getString("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));
if (rs.getString("批號") != null) A_Tab.setValue("批號",rs. getString ("批號"));
}
rs.close();
pstmt.close();
}
result=""

groovy:退料單明細拉領料單資料

groovy:退料單明細拉領料單資料
Callout
========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"品名\",\"規格\",\"單位_id\",\"庫別_id\" FROM \"領料單明細\" WHERE \"領料單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));
if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_id",new Integer(rs.getInt("單位_id")));
if (rs.getInt("庫別_id") > 0) A_Tab.setValue("庫別_id",new Integer(rs.getInt("庫別_id")));
if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));
if (rs.getString("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));
}
rs.close();
pstmt.close();
}
result=""

groovy:品號編碼

groovy:品號編碼
Callout
============
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
//@script:groovy:getQuotationForOrder
if (A_Value == null )
A_Tab.setValue("編碼",null);
else
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"編碼\" FROM \"品號編碼明細\" WHERE \"品號編碼明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {

if (rs.getString("編碼") != null) A_Tab.setValue("編碼",rs. getString ("編碼"));

}
rs.close();
pstmt.close();
}
result=""

groovy:製令變更拉製令

groovy:製令變更拉製令
Callout
=================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT a.\"狀態碼\",a.\"品號_id\",a.\"品名\",a.\"規格\",a.\"預計產量\",a.\"單位_id\",a.\"備註\", b.\"預計開工\", b.\"預計完工\" FROM \"製造命令\" a LEFT OUTER JOIN \"生產排程資料\" b ON a.\"製造命令_id\" =b.\"製造命令_id\" WHERE a.\"製造命令_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));
if (rs.getInt("單位_id") > 0) A_Tab.setValue("單位_id",new Integer(rs.getInt("單位_id")));
if (rs.getString("品名") != null) A_Tab.setValue("品名",rs. getString ("品名"));
if (rs.getString("規格") != null) A_Tab.setValue("規格",rs. getString ("規格"));
if (rs.getString("狀態碼") != null) A_Tab.setValue("狀態碼",rs. getString ("狀態碼"));
if (rs.getString("備註") != null) A_Tab.setValue("備註",rs. getString ("備註"));
if (rs.getBigDecimal ("預計產量") != null) A_Tab.setValue("預計產量",rs.getBigDecimal ("預計產量"));
if (rs.getTimestamp ("預計完工") != null) A_Tab.setValue("預計完工",rs.getTimestamp ("預計完工"));
if (rs.getTimestamp ("預計開工") != null) A_Tab.setValue("預計開工",rs.getTimestamp ("預計開工"));
}
rs.close();
pstmt.close();
}
result=""

groovy:計算金額

groovy:計算金額
Callout
=============
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
//@script:groovy:getQuotationForOrder
BigDecimal qty = null;
BigDecimal price = null;
BigDecimal disrate = null;
BigDecimal taxrate = null;

if (A_Value != null && A_Field.getColumnName().equals("數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("單價");
disrate = A_Tab.getValue("折扣率");
taxrate = A_Tab.getValue("營業稅率");
}

if (A_Value != null && A_Field.getColumnName().equals("單價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("數量");
disrate = A_Tab.getValue("折扣率");
taxrate = A_Tab.getValue("營業稅率");
}

if (A_Value != null && A_Field.getColumnName().equals("折扣率") )
{
disrate = (BigDecimal) A_Value;
qty = A_Tab.getValue("數量");
price = A_Tab.getValue("單價");
taxrate = A_Tab.getValue("營業稅率");
}

if (A_Value != null && A_Field.getColumnName().equals("營業稅率") )
{
taxrate = (BigDecimal) A_Value;
qty = A_Tab.getValue("數量");
price = A_Tab.getValue("單價");
disrate = A_Tab.getValue("折扣率");
}

if (price != null && qty != null)
{
if (disrate==null) disrate = new BigDecimal(0);
if (taxrate==null) taxrate = new BigDecimal(0);
disrate = (new BigDecimal(1)).subtract(disrate.divide(new BigDecimal(100)));
taxrate = (new BigDecimal(1)).add(taxrate.divide(new BigDecimal(100)));
BigDecimal amt = price.multiply(qty).multiply(disrate).multiply(taxrate);
A_Tab.setValue("金額", amt);
}else{
A_Tab.setValue("金額", null);
}

result=""

groovy:sumHead

groovy:sumHead
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "報價單_ID");
int parent_id=((Integer)A_PO.get_Value("報價單_ID")).intValue();

String sql = "SELECT SUM(金額) FROM 報價單明細 WHERE 報價單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal amt = rs.getBigDecimal (1);
if (amt==null) amt = new BigDecimal(0);
DB.executeUpdateEx("UPDATE 報價單 SET 報價金額="+amt +" WHERE 報價單_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

//DB.executeUpdateEx("UPDATE 報價單 SET 備註= '"+parent_id+"' WHERE 報價單_id =1000028", A_PO.get_TrxName());
result=""

groovy:sum淨重& 毛重-packinglist

groovy:sum淨重& 毛重-packinglist
Model Validator Table Event
========================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "packinglist_ID");
int parent_id=((Integer)A_PO.get_Value("packinglist_ID")).intValue();

String sql = "SELECT SUM(淨重),SUM(毛重) FROM packinglistline WHERE packinglist_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal nw = rs.getBigDecimal (1);
BigDecimal gw = rs.getBigDecimal (2);
if (nw==null) nw = new BigDecimal(0);
if (gw==null) gw = new BigDecimal(0);
DB.executeUpdateEx("UPDATE packinglist SET 總淨重="+nw +" , 總毛重="+gw +" WHERE packinglist_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

result=""

groovy:金額計算

groovy:金額計算
Callout
============
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
//@script:groovy:getQuotationForOrder
BigDecimal qty = null;
BigDecimal price = null;
BigDecimal disrate = null;
if (A_Value != null && A_Field.getColumnName().equals("數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("單價");
disrate = A_Tab.getValue("折扣率");
}
if (A_Value != null && A_Field.getColumnName().equals("單價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("數量");
disrate = A_Tab.getValue("折扣率");
}
if (A_Value != null && A_Field.getColumnName().equals("折扣率") )
{
disrate = (BigDecimal) A_Value;
qty = A_Tab.getValue("數量");
price = A_Tab.getValue("單價");
}
if (price != null && qty != null)
{
if (disrate==null) disrate = new BigDecimal(0);
disrate = (new BigDecimal(1)).subtract(disrate.divide(new BigDecimal(100)));
BigDecimal amt = price.multiply(qty).multiply(disrate);
A_Tab.setValue("金額", amt);
}else{
A_Tab.setValue("金額", null);
}
result=""

groovy:稅別碼帶出營業稅率

groovy:稅別碼帶出營業稅率
Callout
=====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"營業稅率\" FROM \"稅別碼\" WHERE \"稅別碼_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {

if (rs.getBigDecimal ("營業稅率") != null) A_Tab.setValue("營業稅率",rs.getBigDecimal ("營業稅率"));

}
rs.close();
pstmt.close();
}
result=""

groovy:稅額計算-報價單

groovy:稅額計算-報價單
Callout
==================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal taxrate = null;
if (A_Value != null && A_Field.getColumnName().equals("報價金額") )
{
price = (BigDecimal) A_Value;
taxrate = A_Tab.getValue("營業稅率");
}
if (A_Value != null && A_Field.getColumnName().equals("營業稅率") )
{
taxrate = (BigDecimal) A_Value;
price = A_Tab.getValue("報價金額");
}
if (price != null)
{
if (taxrate ==null) disrate = new BigDecimal(0);
taxrate = (taxrate.divide(new BigDecimal(100)));
BigDecimal amt = price.multiply(taxrate);
A_Tab.setValue("稅額", amt);
}else{
A_Tab.setValue("稅額", null);
}
result=""

groovy:合計報價單明細金額與數量

groovy:合計報價單明細金額與數量
Model Validator Table Event
==========================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "報價單_ID");
int parent_id=((Integer)A_PO.get_Value("報價單_ID")).intValue();

String sql = "SELECT SUM(金額),SUM(數量) FROM 報價單明細 WHERE 報價單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);
BigDecimal qty = rs.getBigDecimal (2);
if (price==null) price = new BigDecimal(0);
if (qty==null) qty = new BigDecimal(0);
DB.executeUpdateEx("UPDATE 報價單 SET 報價金額="+price+" , 總數量="+qty+" , 稅額="+price+" *營業稅率/100, 合計="+price+" * (1 + 營業稅率/100) WHERE 報價單_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

result=""

groovy:合計訂單明細

groovy:合計訂單明細
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "訂單_ID");
int parent_id=((Integer)A_PO.get_Value("訂單_ID")).intValue();

String sql = "SELECT SUM(金額),SUM(數量),SUM(重量),SUM(毛重) FROM 訂單明細 WHERE 訂單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);
BigDecimal qty = rs.getBigDecimal (2);
BigDecimal weight = rs.getBigDecimal (3);
BigDecimal gw = rs.getBigDecimal (4);

if (price==null) price = new BigDecimal(0);
if (qty==null) qty = new BigDecimal(0);
if (weight==null) weight = new BigDecimal(0);
if (gw==null) gw = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 訂單 SET 訂單金額="+price+" , 訂單稅額="+price+" *營業稅率/100, 合計="+price+" * (1 + 營業稅率/100), 總重量="+weight+" , 總毛重="+gw+" WHERE 訂單_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

groovy:稅額計算-訂單

groovy:稅額計算-訂單
Callout
================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal taxrate = null;
if (A_Value != null && A_Field.getColumnName().equals("訂單金額") )
{
price = (BigDecimal) A_Value;
taxrate = A_Tab.getValue("營業稅率");
}
if (A_Value != null && A_Field.getColumnName().equals("營業稅率") )
{
taxrate = (BigDecimal) A_Value;
price = A_Tab.getValue("訂單金額");
}
if (price != null)
{
if (taxrate ==null) disrate = new BigDecimal(0);
taxrate = (taxrate.divide(new BigDecimal(100)));
BigDecimal amt = price.multiply(taxrate);
A_Tab.setValue("稅額", amt);
}else{
A_Tab.setValue("稅額", null);
}
result=""

groovy:合計請購單明細

groovy:合計請購單明細
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "訂單_ID");
int parent_id=((Integer)A_PO.get_Value("訂單_ID")).intValue();

String sql = "SELECT SUM(金額),SUM(數量),SUM(重量),SUM(毛重) FROM 訂單明細 WHERE 訂單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);
BigDecimal qty = rs.getBigDecimal (2);
BigDecimal weight = rs.getBigDecimal (3);
BigDecimal gw = rs.getBigDecimal (4);

if (price==null) price = new BigDecimal(0);
if (qty==null) qty = new BigDecimal(0);
if (weight==null) weight = new BigDecimal(0);
if (gw==null) gw = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 訂單 SET 訂單金額="+price+" , 訂單稅額="+price+" *營業稅率/100, 合計="+price+" * (1 + 營業稅率/100), 總重量="+weight+" , 總毛重="+gw+" WHERE 訂單_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

groovy:請購金額計算

groovy:請購金額計算
Callout
================
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
//@script:groovy:getQuotationForOrder
BigDecimal qty = null;
BigDecimal price = null;
if (A_Value != null && A_Field.getColumnName().equals("請購數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("請購單價");
}
if (A_Value != null && A_Field.getColumnName().equals("請購單價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("請購數量");
}
if (price != null && qty != null)
{
BigDecimal amt = price.multiply(qty);
A_Tab.setValue("請購金額", amt);
}else{
A_Tab.setValue("請購金額", null);
}
result=""

groovy:本幣金額計算-請購

groovy:本幣金額計算-請購
Callout
====================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal taxrate = null;
if (A_Value != null && A_Field.getColumnName().equals("匯率") )
{
taxrate = (BigDecimal) A_Value;
price = A_Tab.getValue("請購金額");
}
if (A_Value != null && A_Field.getColumnName().equals("請購金額") )
{
price = (BigDecimal) A_Value;
taxrate = A_Tab.getValue("匯率");
}
if (taxrate==null) taxrate = new BigDecimal(1)
if (price != null)
{
BigDecimal amt = price.multiply(taxrate);
A_Tab.setValue("本幣金額", amt);
}else{
A_Tab.setValue("本幣金額", null);
}
result=""

groovy:採購金額計算-採購單

groovy:採購金額計算-採購單
Callout
======================
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
//@script:groovy:getQuotationForOrder
BigDecimal qty = null;
BigDecimal price = null;
if (A_Value != null && A_Field.getColumnName().equals("採購數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("採購單價");
}
if (A_Value != null && A_Field.getColumnName().equals("採購單價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("採購數量");
}
if (price != null && qty != null)
{
BigDecimal amt = price.multiply(qty);

A_Tab.setValue("採購金額", amt);
}else{
A_Tab.setValue("採購金額", null);
}
result=""

groovy:採購單帶出營業稅率

groovy:採購單帶出營業稅率
Callout
=====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"營業稅率\" FROM \"採購單\" WHERE \"採購單_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {

if (rs.getBigDecimal ("營業稅率") != null) A_Tab.setValue("營業稅率",rs.getBigDecimal ("營業稅率"));

}
rs.close();
pstmt.close();
}
result=""

groovy:本幣金額計算-採購

groovy:本幣金額計算-採購
Callout
====================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal taxrate = null;
if (A_Value != null && A_Field.getColumnName().equals("匯率") )
{
taxrate = (BigDecimal) A_Value;
price = A_Tab.getValue("採購金額");
}
if (A_Value != null && A_Field.getColumnName().equals("採購金額") )
{
price = (BigDecimal) A_Value;
taxrate = A_Tab.getValue("匯率");
}
if (taxrate==null) taxrate = new BigDecimal(1)
if (price != null)
{
BigDecimal amt = price.multiply(taxrate);
A_Tab.setValue("本幣金額", amt);
}else{
A_Tab.setValue("本幣金額", null);
}
result=""

groovy:合計採購單金額

groovy:合計採購單金額
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "採購單_ID");
int parent_id=((Integer)A_PO.get_Value("採購單_ID")).intValue();

String sql = "SELECT SUM(採購金額),SUM(本幣金額) FROM 採購單明細 WHERE 採購單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal purprice = rs.getBigDecimal (1);
BigDecimal price = rs.getBigDecimal (2);

if (purprice ==null) purprice = new BigDecimal(0);
if (price ==null) price = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 採購單 SET 原幣未稅金額="+purprice+" , 本幣未稅金額="+price+", 原幣稅額="+purprice+" *營業稅率/100, 本幣稅額="+price+" *營業稅率/100, 合計="+price+"*(1+營業稅率/100) WHERE 採購單_id ="+parent_id, A_PO.get_TrxName());
}

rs.close();
pstmt.close();

groovy:合計請購單金額

groovy:合計請購單金額
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "請購單_ID");
int parent_id=((Integer)A_PO.get_Value("請購單_ID")).intValue();

String sql = "SELECT SUM(本幣金額) FROM 請購單明細 WHERE 請購單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);

if (price ==null) price = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 請購單 SET 本幣金額合計="+price+" WHERE 請購單_id ="+parent_id, A_PO.get_TrxName());
}

rs.close();
pstmt.close();

groovy:進貨金額計算

groovy:進貨金額計算
Callout
================
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
//@script:groovy:getQuotationForOrder
BigDecimal qty = null;
BigDecimal price = null;
if (A_Value != null && A_Field.getColumnName().equals("驗收數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("單位進價");
}
if (A_Value != null && A_Field.getColumnName().equals("單位進價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("驗收數量");
}
if (price != null && qty != null)
{
BigDecimal amt = price.multiply(qty);
A_Tab.setValue("原幣進貨金額", amt);
}else{
A_Tab.setValue("原幣進貨金額", null);
}
result=""

groovy:進貨單明細的本幣計算

groovy:進貨單明細的本幣計算
Callout
=======================
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
//@script:groovy:getQuotationForOrder
BigDecimal exrate = null;
BigDecimal price = null;
BigDecimal discount = null;
if (A_Value != null && A_Field.getColumnName().equals("匯率") )
{
exrate = (BigDecimal) A_Value;
price = A_Tab.getValue("原幣進貨金額");
discount = A_Tab.getValue("原幣扣款金額");
}
if (A_Value != null && A_Field.getColumnName().equals("原幣進貨金額") )
{
price = (BigDecimal) A_Value;
exrate = A_Tab.getValue("匯率");
discount = A_Tab.getValue("原幣扣款金額");
}
if (A_Value != null && A_Field.getColumnName().equals("原幣扣款金額") )
{
discount = (BigDecimal) A_Value;
exrate = A_Tab.getValue("匯率");
price = A_Tab.getValue("原幣進貨金額");
}
if (price != null )
{
if (exrate==null) exrate = new BigDecimal(1);
BigDecimal tprice = price.multiply(exrate);
BigDecimal tdiscount = discount.multiply(exrate) ;
A_Tab.setValue("本幣進貨金額", tprice );
A_Tab.setValue("本幣扣款金額", tdiscount );
}else{
A_Tab.setValue("本幣進貨金額", null);
A_Tab.setValue("本幣扣款金額", null);
}
result=""

groovy:合計進貨單金額

groovy:合計進貨單金額
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "進貨單_ID");
int parent_id=((Integer)A_PO.get_Value("進貨單_ID")).intValue();

String sql = "SELECT SUM(原幣進貨金額),SUM(本幣進貨金額) FROM 進貨單明細 WHERE 進貨單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);
BigDecimal tprice = rs.getBigDecimal (2);

if (price ==null) purprice = new BigDecimal(0);
if (tprice ==null) price = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 進貨單 SET 原幣總進貨金額="+price+" , 本幣總進貨金額="+tprice+", 原幣稅額="+price+" *營業稅率/100, 本幣稅額="+tprice+" *營業稅率/100, 合計="+tprice+"*(1+營業稅率/100) WHERE 進貨單_id ="+parent_id, A_PO.get_TrxName());
}

rs.close();
pstmt.close();

groovy:採購變更單明細自動帶出採購單號

groovy:採購變更單明細自動帶出採購單號
Callout
===============================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"採購單_id\ FROM \"採購變更單\" WHERE \"採購變更單_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("採購單_id") > 0) A_Tab.setValue("採購單_id",new Integer(rs.getInt("採購單_id")));

}
rs.close();
pstmt.close();
}
result=""

groovy:setStatus

groovy:setStatus
Callout
===========
import org.compiere.util.DB
import org.compiere.util.Env
import java.math.BigDecimal

if ( A_Value != null && !(A_Value.equals(A_OldValue) ) )
{
// int id =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("Status", new Boolean(true) );
}

result=""

groovy:訂單帶出客戶單號

groovy:訂單帶出客戶單號
Callout
===================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"客戶單號\",\"匯率\" FROM \"訂單\" WHERE \"訂單_id\"=? ";

PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {

if (rs.getString("客戶單號") != null) A_Tab.setValue("客戶單號",rs. getString ("客戶單號"));
if (rs.getBigDecimal ("匯率") != null) A_Tab.setValue("匯率",rs.getBigDecimal ("匯率"));
}
rs.close();
pstmt.close();
}
result=""

groovy:出貨通知的金額計算

groovy:出貨通知的金額計算
Callout
=====================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal qty = null;
if (A_Value != null && A_Field.getColumnName().equals("單價") )
{
price = (BigDecimal) A_Value;
qty = A_Tab.getValue("實際出貨數量");
}
if (A_Value != null && A_Field.getColumnName().equals("實際出貨數量") )
{
qty = (BigDecimal) A_Value;
price = A_Tab.getValue("單價");
}
if (price != null && qty != null)
{
BigDecimal amt = price.multiply(qty);
A_Tab.setValue("金額", amt );
}else{
A_Tab.setValue("金額", null);
}
result=""

groovy:合計出貨通知明細

groovy:合計出貨通知明細
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
int parent_id=((Integer)A_PO.get_Value("出貨通知_ID")).intValue();

String sql = "SELECT SUM(金額) FROM 出貨通知明細 WHERE 出貨通知_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);

if (price==null) price = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 出貨通知 SET 銷貨金額="+price+" , 銷貨稅額="+price+" *營業稅率/100, 合計="+price+" * (1 + 營業稅率/100) WHERE 出貨通知_id ="+parent_id, A_PO.get_TrxName());
}
rs.close();
pstmt.close();

groovy:出貨通知的原幣金額與本幣金額計算

groovy:出貨通知的原幣金額與本幣金額計算
Callout
=================================
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
//@script:groovy:getQuotationForOrder
BigDecimal price = null;
BigDecimal exrate = null;
if (A_Value != null && A_Field.getColumnName().equals("金額") )
{
price = (BigDecimal) A_Value;
exrate = A_Tab.getValue("匯率");
}
if (A_Value != null && A_Field.getColumnName().equals("匯率") )
{
exrate = (BigDecimal) A_Value;
price = A_Tab.getValue("金額");
}
if (price != null )
{
if (exrate ==null) exrate = new BigDecimal(1);
BigDecimal amt = price.multiply(exrate);
A_Tab.setValue("原幣銷貨金額", price );
A_Tab.setValue("本幣銷貨金額", amt );
}else{
A_Tab.setValue("原幣銷貨金額", null);
A_Tab.setValue("本幣銷貨金額", null);
}
result=""

groovy:合計銷貨單金額

groovy:合計銷貨單金額
Model Validator Table Event
====================
import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import org.compiere.util.Env
import org.compiere.util.DB
// A_Ctx A_PO A_Type A_Event

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");
// int parent_id=Env.getContextAsInt(A_Ctx, "銷貨單_ID");
int parent_id=((Integer)A_PO.get_Value("銷貨單_ID")).intValue();

String sql = "SELECT SUM(原幣銷貨金額),SUM(本幣銷貨金額) FROM 銷貨單明細 WHERE 銷貨單_id =? ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_PO.get_TrxName());
pstmt.setInt(1, parent_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BigDecimal price = rs.getBigDecimal (1);
BigDecimal tprice = rs.getBigDecimal (2);

if (price ==null) price = new BigDecimal(0);
if (tprice ==null) tprice = new BigDecimal(0);

DB.executeUpdateEx("UPDATE 銷貨單 SET 原幣銷貨金額="+price+" , 本幣銷貨金額="+tprice+", 原幣銷貨稅額="+price+" *營業稅率/100, 本幣銷貨稅額="+tprice+" *營業稅率/100, 本幣合計="+tprice+"*(1+營業稅率/100), 原幣合計="+price+"*(1+營業稅率/100) WHERE 銷貨單_id ="+parent_id, A_PO.get_TrxName());
}

rs.close();
pstmt.close();

groovy:出貨通知拉出貨通知資料

groovy:出貨通知拉出貨通知資料
Callout
========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"客戶_id\", \"部門_id\",\"業務人員_id\",\"廠別_id\",\"幣別_id\" FROM \"出貨通知\" WHERE \"出貨通知_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("客戶_id") > 0) A_Tab.setValue("客戶_id",new Integer(rs.getInt("客戶_id")));
if (rs.getInt("部門_id") > 0) A_Tab.setValue("部門_id",new Integer(rs.getInt("部門_id")));
if (rs.getInt("業務人員_id") > 0) A_Tab.setValue("業務人員_id",new Integer(rs.getInt("業務人員_id")));
if (rs.getInt("廠別_id") > 0) A_Tab.setValue("廠別_id",new Integer(rs.getInt("廠別_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_id",new Integer(rs.getInt("幣別_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:主管確認拉出狀態

groovy:主管確認拉出狀態
Callout
===================
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
import org.compiere.util.Env
//@script:groovy:getQuotationForOrder

if (A_Value != null ){
int id = ((Integer) A_Value).intValue();
if (id==1000000 && (Integer)A_Tab.getValue("銷貨單_ID")!=null ) { // 拉到 [確認] 才做
int ordid=((Integer)A_Tab.getValue("銷貨單_ID")).intValue();

String sql1 ="SELECT a.\"品號_id\", a.\"數量\", a.\"庫別_id\", a.\"銷貨單明細_id\" FROM \"銷貨單明細\" a WHERE a.\"銷貨單_id\"=? ";
PreparedStatement pstmt1 = DB.prepareStatement(sql1, null);
pstmt1.setInt(1, ordid);
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next())
{
int lineid = rs1. getInt ("銷貨單明細_id");
int pdid = rs1. getInt ("品號_id");
int whid = rs1. getInt ("庫別_id");
BigDecimal qty1 = rs1. getBigDecimal ("數量");
String sql2 ="SELECT a.\"數量\", \"儲位內容_id\" FROM \"儲位內容\" a INNER JOIN \"儲位\" b ON (a.\"儲位_id\"=b.\"儲位_id\") INNER JOIN \"庫別\" c ON (b.\"庫別_id\"=c.\"庫別_id\") WHERE a.\"品號_id\"=? AND c.\"庫別_id\"=? ";
PreparedStatement pstmt2 = DB.prepareStatement(sql2, null);
pstmt2.setInt(1, pdid );
pstmt2.setInt(2, whid);
ResultSet rs2 = pstmt2.executeQuery();
if (rs2.next()) {
int locid= rs2. getInt ("儲位內容_id");
BigDecimal qty2 = rs2. getBigDecimal ("數量");
BigDecimal qty3 = qty2.subtract(qty1);
sqlx = "UPDATE \"銷貨單明細\" SET \"更新前數量\"='"+ qty2 +"',\"更新後數量\"='"+qty3 +"' WHERE \"銷貨單明細_id\"="+lineid +" ";
DB.executeUpdate (sqlx );
sqly = "UPDATE \"儲位內容\" SET \"數量\"='"+ qty3 +"' WHERE \"儲位內容_id\"="+locid +" ";
DB.executeUpdate (sqly );
}
rs2.close();
pstmt2.close();
}
rs1.close();
pstmt1.close();
}
String sql ="SELECT \"狀態\" FROM \"主管確認\" WHERE \"主管確認_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
if (rs.getString("狀態") != null) A_Tab.setValue("狀態",rs. getString ("狀態"));
int id1 =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("確認者_ID", new Integer(id1) );
}
rs.close();
pstmt.close();
}
result=""

groovy: 報價單明細_ID帶出報價單明細資料

groovy: 報價單明細_ID帶出報價單明細資料
Callout
=================================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"品號_id\", \"數量\",\"贈品量\",\"單價\",\"折扣率\" FROM \"報價單明細\" WHERE \"報價單明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("品號_id") > 0) A_Tab.setValue("品號_id",new Integer(rs.getInt("品號_id")));
if (rs.getBigDecimal ("數量") != null) A_Tab.setValue("數量",rs.getBigDecimal ("數量"));
if (rs.getBigDecimal ("贈品量") != null) A_Tab.setValue("贈品量",rs.getBigDecimal ("贈品量"));
if (rs.getBigDecimal ("單價") != null) A_Tab.setValue("單價",rs.getBigDecimal ("單價"));
if (rs.getBigDecimal ("折扣率") != null) A_Tab.setValue("折扣率",rs.getBigDecimal ("折扣率"));
}
rs.close();
pstmt.close();
}
result=""

groovy:出貨通知明細拉出貨通知明細資料

groovy:出貨通知明細拉出貨通知明細資料
Callout
===============================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"訂單_id\", \"訂單明細_id\",\"業務人員_id\",\"廠別_id\",\"幣別_id\" FROM \"出貨通知明細\" WHERE \"出貨通知明細_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next()) {
if (rs.getInt("客戶_id") > 0) A_Tab.setValue("客戶_id",new Integer(rs.getInt("客戶_id")));
if (rs.getInt("部門_id") > 0) A_Tab.setValue("部門_id",new Integer(rs.getInt("部門_id")));
if (rs.getInt("業務人員_id") > 0) A_Tab.setValue("業務人員_id",new Integer(rs.getInt("業務人員_id")));
if (rs.getInt("廠別_id") > 0) A_Tab.setValue("廠別_id",new Integer(rs.getInt("廠別_id")));
if (rs.getInt("幣別_id") > 0) A_Tab.setValue("幣別_id",new Integer(rs.getInt("幣別_id")));
}
rs.close();
pstmt.close();
}
result=""

groovy:單據日期帶編號-報價單

groovy:單據日期帶編號-報價單
Callout
=======================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
Timestamp dt = (Timestamp ) A_Value;
String sql ="SELECT max(\"報價單號\") as 報價單號 FROM \"報價單\" WHERE \"單據日期\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setTimestamp (1, dt);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next())
{
if (rs.getString("報價單號") != null)
{
String no1=rs.getString("報價單號")
int no2=Integer.parseInt(no1,10)
int no3 = no2+1
String no4 = ""+no3
A_Tab.setValue("報價單號",no4)
}else{
Timestamp now = new Timestamp(System.currentTimeMillis());
def dt = ""+now
def dt1 = dt.substring(0,4)
def dt2 = dt.substring(5,7)
def dt3 = dt.substring(8,10)
dt = dt1+dt2+dt3
String no4 = dt+"01"
A_Tab.setValue("報價單號",no4)
}
}
rs.close();
pstmt.close();
}
result=""

groovy:單據日期帶編號-訂單

groovy:單據日期帶編號-訂單
Callout
=====================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
Timestamp dt = (Timestamp ) A_Value;
String sql ="SELECT max(\"訂單單號\") as 訂單單號 FROM \"訂單\" WHERE \"單據日期\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setTimestamp (1, dt);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next())
{
if (rs.getString("訂單單號") != null)
{
String no1=rs.getString("訂單單號")
int no2=Integer.parseInt(no1,10)
int no3 = no2+1
String no4 = ""+no3
A_Tab.setValue("訂單單號",no4)
}
}
rs.close();
pstmt.close();
}
result=""

groovy:單據日期帶編號-出貨通知

groovy:單據日期帶編號-出貨通知
Callout
=========================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
Timestamp dt = (Timestamp ) A_Value;
String sql ="SELECT max(\"出貨通知單號\") as 出貨通知單號 FROM \"出貨通知\" WHERE \"單據日期\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setTimestamp (1, dt);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next())
{
if (rs.getString("出貨通知單號") != null)
{
String no1=rs.getString("出貨通知單號")
int no2=Integer.parseInt(no1,10)
int no3 = no2+1
String no4 = ""+no3
A_Tab.setValue("出貨通知單號",no4)
}
}
rs.close();
pstmt.close();
}
result=""

groovy:單據日期帶編號-銷貨單

groovy:單據日期帶編號-銷貨單
Callout
=======================
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
//@script:groovy:getQuotationForOrder
if (A_Value != null )
{
Timestamp dt = (Timestamp ) A_Value;
String sql ="SELECT max(\"出貨通知單號\") as 出貨通知單號 FROM \"出貨通知\" WHERE \"單據日期\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setTimestamp (1, dt);
ResultSet rs = pstmt.executeQuery();
// 只作 1筆 while (rs.next()){
if (rs.next())
{
if (rs.getString("出貨通知單號") != null)
{
String no1=rs.getString("出貨通知單號")
int no2=Integer.parseInt(no1,10)
int no3 = no2+1
String no4 = ""+no3
A_Tab.setValue("出貨通知單號",no4)
}
}
rs.close();
pstmt.close();
}
result=""

groovy:主管確認拉出狀態-含銷貨扣庫存

groovy:主管確認拉出狀態-含銷貨扣庫存
Callout
==============================
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
import org.compiere.util.Env
//@script:groovy:getQuotationForOrder

if (A_Value != null ){
int id = ((Integer) A_Value).intValue();
if (id==1000000 && (Integer)A_Tab.getValue("銷貨單_ID")!=null ) { // 拉到 [確認] 才做
int ordid=((Integer)A_Tab.getValue("銷貨單_ID")).intValue();

String sql1 ="SELECT a.\"品號_id\", a.\"數量\", a.\"庫別_id\", a.\"銷貨單明細_id\" FROM \"銷貨單明細\" a WHERE a.\"銷貨單_id\"=? ";
PreparedStatement pstmt1 = DB.prepareStatement(sql1, null);
pstmt1.setInt(1, ordid);
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next())
{
int lineid = rs1. getInt ("銷貨單明細_id");
int pdid = rs1. getInt ("品號_id");
int whid = rs1. getInt ("庫別_id");
BigDecimal qty1 = rs1. getBigDecimal ("數量");
String sql2 ="SELECT a.\"數量\", \"儲位內容_id\" FROM \"儲位內容\" a INNER JOIN \"儲位\" b ON (a.\"儲位_id\"=b.\"儲位_id\") INNER JOIN \"庫別\" c ON (b.\"庫別_id\"=c.\"庫別_id\") WHERE a.\"品號_id\"=? AND c.\"庫別_id\"=? ";
PreparedStatement pstmt2 = DB.prepareStatement(sql2, null);
pstmt2.setInt(1, pdid );
pstmt2.setInt(2, whid);
ResultSet rs2 = pstmt2.executeQuery();
if (rs2.next()) {
int locid= rs2. getInt ("儲位內容_id");
BigDecimal qty2 = rs2. getBigDecimal ("數量");
BigDecimal qty3 = qty2.subtract(qty1);
sqlx = "UPDATE \"銷貨單明細\" SET \"更新前數量\"='"+ qty2 +"',\"更新後數量\"='"+qty3 +"' WHERE \"銷貨單明細_id\"="+lineid +" ";
DB.executeUpdate (sqlx );
sqly = "UPDATE \"儲位內容\" SET \"數量\"='"+ qty3 +"' WHERE \"儲位內容_id\"="+locid +" ";
DB.executeUpdate (sqly );
}
rs2.close();
pstmt2.close();
}
rs1.close();
pstmt1.close();
}
String sql ="SELECT \"狀態\" FROM \"主管確認\" WHERE \"主管確認_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
if (rs.getString("狀態") != null) A_Tab.setValue("狀態",rs. getString ("狀態"));
int id1 =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("確認者_ID", new Integer(id1) );
}
rs.close();
pstmt.close();
}
result=""

groovy:確認功能

groovy:確認功能
Callout
============
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
import org.compiere.util.Env
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
String sql ="SELECT \"狀態\" FROM \"確認\" WHERE \"確認_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
if (rs.getString("狀態") != null) A_Tab.setValue("狀態",rs. getString ("狀態"));
if (id==1000000 )
{
int id1 =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("確認者_ID", new Integer(id1) );
}
if (id==1000001 )
{
A_Tab.setValue("確認者_ID", null );
}
}
rs.close();
pstmt.close();
}
result=""

groovy:銷貨單回寫訂單&出貨通知

groovy:銷貨單回寫訂單&出貨通知
Callout
==========================
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
import org.compiere.util.Env
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
// String sql ="SELECT \"狀態\" FROM \"確認\" WHERE \"確認_id\"=? ";
// PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
// if (rs.getString("狀態") != null) A_Tab.setValue("狀態",rs. getString ("狀態"));
if (id==1000000 )
{// 拉到 [確認] 才做

int id1 =Env.getContextAsInt(A_Ctx, "#AD_User_ID");
A_Tab.setValue("確認者_ID", new Integer(id1) );

int ordid=((Integer)A_Tab.getValue("銷貨單_ID")).intValue();
String sql1 ="SELECT a.\"出貨通知明細_id\", a.\"訂單明細_id\", a.\"數量\", a.\"贈品量\" FROM \"銷貨單明細\" a WHERE a.\"銷貨單_id\"=? ";
PreparedStatement pstmt1 = DB.prepareStatement(sql1, null);
pstmt1.setInt(1, ordid);
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next())
{
int lineid = rs1. getInt ("出貨通知明細_id");
int pdid = rs1. getInt ("訂單明細_id");
BigDecimal qty1 = rs1. getBigDecimal ("數量");
BigDecimal qty2 = rs1. getBigDecimal ("贈品量");
BigDecimal qty3 = qty2.add(qty1);
sqlx = "UPDATE \"出貨通知明細\" SET \"實際出貨數量\"='"+ qty1 +"',\"實際贈備品數量\"='"+qty2 +"' WHERE \"出貨通知明細_id\"="+lineid +" ";
DB.executeUpdate (sqlx );
sqly = "UPDATE \"訂單明細\" SET \"已銷貨數量\"='"+ qty3 +"' WHERE \"訂單明細_id\"="+pdid +" ";
DB.executeUpdate (sqly );
}
rs1.close();
pstmt1.close();
}
}
rs.close();
pstmt.close();
}
result=""

groovy:入庫單入庫

groovy:入庫單入庫
Callout
==============
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
import org.compiere.util.Env
if (A_Value != null )
{
int id = ((Integer) A_Value).intValue();
// String sql ="SELECT \"狀態\" FROM \"確認\" WHERE \"確認_id\"=? ";
// PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
// if (rs.getString("狀態") != null) A_Tab.setValue("狀態",rs. getString ("狀態"));
if (id==1000000 )
{ // 拉到 [確認] 才做
int ordid=((Integer)A_Tab.getValue("入庫單_ID")).intValue();

String sql1 ="SELECT a.\"品號_id\", a.\"入庫數量\", a.\"庫別_id\", a.\"入庫單明細_id\" FROM \"入庫單明細\" a WHERE a.\"入庫單_id\"=? ";
PreparedStatement pstmt1 = DB.prepareStatement(sql1, null);
pstmt1.setInt(1, ordid);
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next())
{
int lineid = rs1. getInt ("入庫單明細_id");
int pdid = rs1. getInt ("品號_id");
int whid = rs1. getInt ("庫別_id");
BigDecimal qty1 = rs1. getBigDecimal ("入庫數量");
String sql2 ="SELECT a.\"數量\", \"儲位內容_id\" FROM \"儲位內容\" a INNER JOIN \"儲位\" b ON (a.\"儲位_id\"=b.\"儲位_id\") INNER JOIN \"庫別\" c ON (b.\"庫別_id\"=c.\"庫別_id\") WHERE a.\"品號_id\"=? AND c.\"庫別_id\"=? ";
PreparedStatement pstmt2 = DB.prepareStatement(sql2, null);
pstmt2.setInt(1, pdid );
pstmt2.setInt(2, whid);
ResultSet rs2 = pstmt2.executeQuery();
if (rs2.next()) {
int locid= rs2. getInt ("儲位內容_id");
BigDecimal qty2 = rs2. getBigDecimal ("數量");
BigDecimal qty3 = qty2.add(qty1);
sqlx = "UPDATE \"入庫單明細\" SET \"原庫存數量\"='"+ qty2 +"',\"更新後庫存數量\"='"+qty3 +"' WHERE \"入庫單明細_id\"="+lineid +" ";
DB.executeUpdate (sqlx );
sqly = "UPDATE \"儲位內容\" SET \"數量\"='"+ qty3 +"' WHERE \"儲位內容_id\"="+locid +" ";
DB.executeUpdate (sqly );
}
rs2.close();
pstmt2.close();
}
rs1.close();
pstmt1.close();
}
String sql ="SELECT \"狀態\" FROM \"主管確認\" WHERE \"主管確認_id\"=? ";
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
}
rs.close();
pstmt.close();
}
result=""

訂閱：文章 (Atom)

2014年1月27日 星期一

2014年1月27日星期一