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

2013年10月11日星期五

xxx_i

-- Function: adempiere.tg_sk_bom_fg_i(numeric)

-- DROP FUNCTION adempiere.tg_sk_bom_fg_i(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_bom_fg_i(p_instance numeric)
RETURNS SETOF adempiere.tg_sk_bom_fg_t AS
$BODY$
/*
原設計是單頭就是一個月份..現在改成直接抓單身..只要確認最後一次產生的單頭且區間要 >= 列印起訖
CURRENT_LANG = en_US , zh_CN , zh_TW
set search_path=adempiere
select * from adempiere.tg_sk_bom_fg_t
-- "程式完成:: p_instance=1000000 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=TG-G016 - TG-G016"
select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
select * from adempiere.ad_pinstance_para where ad_pinstance_id = 1000000
DROP table adempiere.tg_sk_bom_fg_t CASCADE
SELECT * FROM adempiere.sk_fgbom_i(1000000)
*/
DECLARE

p RECORD;
q RECORD;
r RECORD;
v_message VARCHAR (2000);
ResultStr VARCHAR (2000);

p_Record_ID NUMERIC(10) := null;
p_AD_User_ID NUMERIC(10) := null;
p_AD_Process_ID NUMERIC(10) := null;
p_AD_Client_ID NUMERIC(10) := null;
p_AD_Org_ID NUMERIC(10) := null;
p_DocDate DATE;
p_DocDateTo DATE;
p_DocumentNo VARCHAR (200); --起迄
p_DocumentNoTo VARCHAR (200);
p_DateOrdered DATE; --起迄
p_DateOrderedTo DATE;
p_M_Product_ID NUMERIC(10) := 0; --起迄
p_M_ProductTo_ID NUMERIC(10) := 0;
p_C_BPartner_ID NUMERIC(10) := 0; --起迄
p_C_BPartnerTo_ID NUMERIC(10) := 0;
p_SK_NO VARCHAR (20); --起迄
p_SK_NOTo VARCHAR (20); --起迄
p_M_Product_Value VARCHAR (20); --起迄
p_M_ProductEnd_Value VARCHAR (20);
p_C_BPartner_Value VARCHAR (20); --起迄
p_C_BPartnerEnd_Value VARCHAR (20);
p_instance_id NUMERIC(10) := 0;
v_sk_no VARCHAR (20);
BEGIN
IF p_instance is null THEN
p_instance_id:=0;
ELSE
p_instance_id:=p_instance;
END IF;
v_message :='程式開始..';

IF p_instance_id > 0 THEN
BEGIN
ResultStr := 'p_instanceNotFound';
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'Y',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;

FOR p IN (
SELECT i.ad_pinstance_id, i.ad_process_id,
i.record_id, i.ad_user_id,
i.ad_client_id, i.ad_org_id,
pp.seqno, pp.parametername,
pp.p_string, pp.p_string_to,
pp.p_number, pp.p_number_to,
pp.p_date, pp.p_date_to,
pp.info, pp.info_to
FROM adempiere.ad_pinstance i
INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
WHERE i.ad_pinstance_id=p_instance_id
ORDER BY pp.seqno
) LOOP
v_message := '讀取傳遞參數表';
p_Record_ID :=p.record_id;
p_AD_Process_ID:=p.ad_process_id;
p_AD_User_ID :=p.ad_user_id;
p_AD_Client_ID :=p.ad_client_id;
p_AD_Org_ID :=p.ad_org_id;
IF p.parametername = 'DocDate' THEN
p_DocDate :=p.p_date;
p_DocDateTo :=p.p_date_to;
ELSE IF p.parametername = 'SK_NO' THEN
p_SK_NO :=p.p_string;
p_SK_NOTo :=p.p_string_to;
END IF;
END IF;
END LOOP;

IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN

p_DocDate := '2013.01.01'::DATE;
p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
FOR r IN (

--select * from adempiere.sk_fgjh

SELECT x.vcount , x.sd_skno
FROM (
SELECT COUNT(*) AS vcount ,sd_skno
FROM adempiere.sk_bom_fg
GROUP BY sd_skno
) x
ORDER BY vcount DESC LIMIT 1
) LOOP
-- v_message := '如果沒資料抓一筆來測試';
-- p_Record_ID := r.sk_fgjh_id;
p_SK_NO := r.sd_skno;
p_SK_NOTo := r.sd_skno;
END LOOP;

-- p_DocDate := '2013.01.01'::DATE;
-- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
-- p_SK_NO := '2Z-F8050-B070-1';
-- p_SK_NOTo := '2Z-F8050-B070-1';

END IF;

IF p_DocDateTo IS NULL AND p_DocDate IS NOT NULL THEN
p_DocDateTo := p_DocDate;
END IF;
IF p_SK_NOTo IS NULL AND p_SK_NO IS NOT NULL THEN
p_SK_NOTo := p_SK_NO;
END IF;

-- 1) TABLE
-- alter table adempiere.tg_sk_bom_fg_t add docdateto date

DELETE FROM adempiere.tg_sk_bom_fg_t WHERE ad_pinstance_id = p_instance;

v_sk_no := '';
-- 2) INSERT INOT TABLE SELECT
FOR r IN (
SELECT
-- ad_pinstance_id numeric(10,0) NOT NULL,
sk_bom_fg_id, -- numeric(10,0) NOT NULL,
sk_bom_fg_uu, -- character varying(36),
sp_date, -- date,
doc_no, -- character varying(40),
sd_skno, -- character varying(40),
exp_qty, -- numeric(14,4),
bomname, -- character varying(40),
bomvalue, -- character varying(40),
documentno, -- character varying(40),
sk_bom_id, -- numeric(10,0),
bomqty, -- numeric(14,4),
linename, -- character varying(40),
linevalue, -- character varying(40),
lineqty, -- numeric(14,4),
effectivedate, -- date,
expireddate, -- date,
sk_bomalternative_id, -- numeric(10,0),
alternativename, -- character varying(40),
ad_client_id, -- numeric(10,0) NOT NULL,
ad_org_id, -- numeric(10,0) NOT NULL,
isactive, -- character(1) NOT NULL DEFAULT 'Y'::bpchar,
created, -- timestamp without time zone NOT NULL DEFAULT now(),
createdby, -- numeric(10,0) NOT NULL,
updated, -- timestamp without time zone NOT NULL DEFAULT now(),
updatedby -- numeric(10,0) NOT NULL
FROM adempiere.sk_bom_fg
WHERE 1=1
-- AND sp_date <= p_DocDate --'2013-01-01' --
-- AND sp_date >= p_DocDateTo --'2013-01-31' --
AND sd_skno BETWEEN p_SK_NO AND p_SK_NOTo -- p_SK_NO := '2Z-F8050-B070-1';
-- AND sd_skno='1D-NGF899'
ORDER BY sd_skno, sp_date desc
--p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
)LOOP
INSERT INTO adempiere.tg_sk_bom_fg_t(
ad_pinstance_id, -- numeric(10,0) NOT NULL,
sk_bom_fg_id, -- numeric(10,0) NOT NULL,
sk_bom_fg_uu, -- character varying(36),
sp_date, -- date,
doc_no, -- character varying(40),
sd_skno, -- character varying(40),
exp_qty, -- numeric(14,4),
bomname, -- character varying(40),
bomvalue, -- character varying(40),
documentno, -- character varying(40),
sk_bom_id, -- numeric(10,0),
bomqty, -- numeric(14,4),
linename, -- character varying(40),
linevalue, -- character varying(40),
lineqty, -- numeric(14,4),
effectivedate, -- date,
expireddate, -- date,
sk_bomalternative_id, -- numeric(10,0),
alternativename, -- character varying(40),
ad_client_id, -- numeric(10,0) NOT NULL,
ad_org_id, -- numeric(10,0) NOT NULL,
isactive, -- character(1) NOT NULL DEFAULT 'Y'::bpchar,
created, -- timestamp without time zone NOT NULL DEFAULT now(),
createdby, -- numeric(10,0) NOT NULL,
updated, -- timestamp without time zone NOT NULL DEFAULT now(),
updatedby -- numeric(10,0) NOT NULL
) VALUES (
p_instance_id, -- numeric(10,0) NOT NULL,
r.sk_bom_fg_id, -- numeric(10,0) NOT NULL,
r.sk_bom_fg_uu, -- character varying(36),
r.sp_date, -- date,
r.doc_no, -- character varying(40),
r.sd_skno, -- character varying(40),
r.exp_qty, -- numeric(14,4),
r.bomname, -- character varying(40),
r.bomvalue, -- character varying(40),
r.documentno, -- character varying(40),
r.sk_bom_id, -- numeric(10,0),
r.bomqty, -- numeric(14,4),
r.linename, -- character varying(40),
r.linevalue, -- character varying(40),
r.lineqty, -- numeric(14,4),
r.effectivedate, -- date,
r.expireddate, -- date,
r.sk_bomalternative_id, -- numeric(10,0),
r.alternativename, -- character varying(40),
r.ad_client_id, -- numeric(10,0) NOT NULL,
r.ad_org_id, -- numeric(10,0) NOT NULL,
r.isactive, -- character(1) NOT NULL DEFAULT 'Y'::bpchar,
r.created, -- timestamp without time zone NOT NULL DEFAULT now(),
r.createdby, -- numeric(10,0) NOT NULL,
r.updated, -- timestamp without time zone NOT NULL DEFAULT now(),
r.updatedby -- numeric(10,0) NOT NULL
);
END LOOP;

v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;

IF p_instance_id > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;

-- 3) RETURN
RETURN QUERY SELECT *
FROM adempiere.tg_sk_bom_fg_t
WHERE ad_pinstance_id = p_instance;

--raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
IF p_instance_id > 0 THEN
BEGIN
ResultStr := v_message;
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION adempiere.tg_sk_bom_fg_i(numeric)
OWNER TO postgres;

xx_gen

-- Function: adempiere.tg_sk_bom_fg_gen(numeric)

-- DROP FUNCTION adempiere.tg_sk_bom_fg_gen(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_bom_fg_gen(pinstance numeric)
RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
--"程式失敗::INSERT 的目標資料行比運算式更多 ,SQLSTATE=42601 ,v_message=有用到的日期"
-- set search_path=adempiere
-- DELETE FROM adempiere.sk_gen_fgbom_log
-- DELETE FROM adempiere.SK_BOM_FG;
-- select * FROM adempiere.SK_BOM_FG;
-- select * from adempiere.sk_gen_fgbom_log
-- select adempiere.sk_gen_fgbom(1000000);
-- select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
/*
select a.sd_skno, a.bomname, a.bomvalue, a.version, a.exp_qty, b.linename, b.linevalue, b.lineqty, a.exp_qty *b.lineqty --, b.bom_qty
from
(select sd_skno, bomname, bomvalue, version, sum(exp_qty) as exp_qty
from adempiere.sk_bom_fg
group by sd_skno, bomname, bomvalue, version) a
--order by sd_skno, bomname, bomvalue, version
inner join
(select sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty --, sum(exp_qty*lineqty) as bom_qty
from adempiere.sk_bom_fg
group by sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty) b on a.sd_skno=b.sd_skno and a.bomname=b.bomname and a.bomvalue=b.bomvalue and a.version=b.version
order by a.sd_skno, a.bomname, a.bomvalue, a.version, b.linename, b.linevalue, b.lineqty
*/
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
ResultStr VARCHAR (200);
roleaccesslevelwin VARCHAR (200);
sqlins VARCHAR (4000);
sqlupd VARCHAR (4000);
sqlwindowaccess VARCHAR (4000);
p RECORD;
q RECORD;
r RECORD;

inv RECORD;
o_nextid NUMERIC(10) := 0;
o_nextheadid NUMERIC(10) := 0;
o_nextlineid NUMERIC(10) := 0;
p_Record_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
p_AD_Process_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_Org_ID NUMERIC(10) := 0;
p_DocDate DATE;
p_DocDateTo DATE;
p_DocumentNo VARCHAR (200); --起迄
p_DocumentNoTo VARCHAR (200);
p_DateOrdered DATE; --起迄
p_DateOrderedTo DATE;
p_M_Product_ID NUMERIC(10) := 0; --起迄
p_M_ProductTo_ID NUMERIC(10) := 0;
p_C_BPartner_ID NUMERIC(10) := 0;--起迄
p_C_BPartnerTo_ID NUMERIC(10) := 0;

p_M_Product_Value VARCHAR (200); --起迄
p_M_ProductEnd_Value VARCHAR (200);
p_C_BPartner_Value VARCHAR (200);--起迄
p_C_BPartnerEnd_Value VARCHAR (200);
v_message VARCHAR(400) := '';
v_NextNo NUMERIC(10) := 0;
p_IsAllActive VARCHAR(1) := 'Y'; --IsAllActive
p_AD_Tab_ID NUMERIC(10) := 0;
pinstance_id NUMERIC(10) := 0;
P_NOWQTY NUMERIC(14,4) := 0;
v_count1 NUMERIC(10) := 0;
v_count2 NUMERIC(10) := 0;
v_BOMAlternative_ID NUMERIC(10) := 0;
v_BOMAlternative_SW NUMERIC(10) := 0;
BEGIN
IF pinstance is null THEN
pinstance_id:=0;
ELSE
pinstance_id:=pinstance;
END IF;
v_message :='程式開始..';

IF pinstance_id > 0 THEN
BEGIN
ResultStr := 'PInstanceNotFound';
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'Y',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = pinstance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;

FOR p IN (
SELECT i.ad_pinstance_id, i.ad_process_id,
i.record_id, i.ad_user_id,
i.ad_client_id, i.ad_org_id,
pp.seqno, pp.parametername,
pp.p_string, pp.p_string_to,
pp.p_number, pp.p_number_to,
pp.p_date, pp.p_date_to,
pp.info, pp.info_to
FROM adempiere.ad_pinstance i
INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
WHERE i.ad_pinstance_id=pinstance_id
ORDER BY pp.seqno
) LOOP
v_message := '讀取傳遞參數表';
p_Record_ID :=p.record_id;
p_AD_Process_ID:=p.ad_process_id;
p_AD_User_ID :=p.ad_user_id;
p_AD_Client_ID :=p.ad_client_id;
p_AD_Org_ID :=p.ad_org_id;
IF p.parametername = 'DocDate' THEN
p_DocDate :=p_date;
p_DocDateTo :=p_date_to;
END IF;
END LOOP;
p_DocDate = coalesce(p_DocDate, current_date-'2month'::interval ); --假設兩個月前); --now()
-- SELECT date_trunc('month', current_date),date_trunc('month', current_date)+'1month'::interval-'1day'::interval
p_DocDate := date_trunc('month', p_DocDate);
p_DocDateTo := date_trunc('month', p_DocDate)+'1month'::interval-'1day'::interval;

p_DocDate := p_DocDate -'8month'::interval; --假設 8 個月前
-- p_DocDateTo := p_DocDateTo -'2month'::interval;

p_AD_User_ID :=coalesce(p_AD_User_ID,0);
p_AD_Client_ID :=coalesce(p_AD_Client_ID,1000000);
p_AD_Org_ID :=coalesce(p_AD_Org_ID,0);

sqlins := 'INSERT INTO adempiere.SK_BOM_FG('
|| 'SK_BOM_FG_ID, SK_BOM_FG_UU, SP_Date, Doc_No, SD_SKno, Exp_Qty,'
|| 'BOMName, BOMValue, BOMQty, DocumentNo, Version, SK_BOM_ID, LineName, LineValue, LineQty, Effectivedate, Expireddate, SK_BomAlternative_ID, AlternativeName,'
|| 'AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy'
|| ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26)';
--銷貨
FOR p IN (select trim(b.sd_skno) as sk_no,
a.sp_no as doc_no,
a.sp_date as sp_date,
null::numeric(14,4) as in_qty,
b.sd_qty as out_qty, --出口數量
b.sd_qty as exp_qty, --出口數量
null::date as out_date,
null::varchar(20) as bonding_no
from adempiere.z_sslip a
inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
inner join adempiere.z_sstock s on trim(s.sk_no)=trim(b.sd_skno)
where 1=1
-- and trim(b.sd_skno) = p.sk_no -- 必須同第一層的料號
-- and date_trunc('day',a.sp_date)= q.docdate -- 必須同第二層的日期
and a.sp_class = '1' and a.sp_slip_fg = '2' -- 成品出貨銷貨
and s.sk_ikind = '2' --成品
and date_trunc('day', a.sp_date) between date_trunc('day',p_DocDate) and date_trunc('day',p_DocDateTo) -- 當期有進出
order by b.sd_skno, a.sp_date, a.sp_no
) LOOP
v_message := '銷貨';

--SELECT * FROM adempiere.SK_BOM b
FOR q IN (
SELECT s.Name AS BOMName, s.Value AS BOMValue, b.BOMQty AS BOMQty, b.DocumentNo, b.Version, b.SK_BOM_ID,
sl.Name AS LineName, sl.Value AS LineValue, bl.BOMQty AS LineQty,
b.EffectiveDate, b.ExpiredDate, bl.SK_BOMAlternative_ID, nl.Name AS AlternativeName
FROM adempiere.SK_BOM b
INNER JOIN adempiere.SK_BOMLine bl ON b.SK_BOM_ID =bl.SK_BOM_ID
INNER JOIN adempiere.SK_Stock s ON s.SK_Stock_ID =b.SK_Stock_ID
INNER JOIN adempiere.SK_Stock sl ON sl.SK_Stock_ID=bl.SK_Stock_ID
LEFT OUTER JOIN adempiere.SK_BOMAlternative nl ON bl.SK_BOMAlternative_ID=nl.SK_BOMAlternative_ID
WHERE s.Value = p.sk_no -- BOM 產出成品
AND (b.effectivedate IS NULL OR date_trunc('day',p.sp_date ) >= date_trunc('day', b.effectivedate) )
AND (b.expireddate IS NULL OR date_trunc('day',p.sp_date ) <= date_trunc('day', b.expireddate) )
ORDER BY COALESCE(bl.SK_BOMAlternative_ID,0)
) LOOP
v_message := '有用到的日期';
v_count2 := v_count2 + 1;

-- IF 有替代性質代碼 IF 上一個替代性質跟目前這筆不相同 = 這是第一筆
-- ELSE 相同 = 這是第 N 筆
--
IF COALESCE(q.SK_BOMAlternative_ID,0) > 0 THEN
IF v_BOMAlternative_ID <> q.SK_BOMAlternative_ID THEN -- 第1筆
-- v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
v_BOMAlternative_SW := 1; -- 這是第 1 筆
ELSE
v_BOMAlternative_SW := v_BOMAlternative_SW + 1 ; -- 這是第 N 筆
END IF;
END IF;

IF COALESCE(q.SK_BOMAlternative_ID,0) = 0 OR v_BOMAlternative_SW = 1 THEN --
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_BOM_FG'; --1000200
SELECT adempiere.nextid(1000200, 'N') INTO o_nextid; -- SK_BOM_FG
EXECUTE sqlins USING o_nextid, adempiere.generate_uuid(),
p.sp_date, p.doc_no, p.sk_no, p.exp_qty,
q.BOMName, q.BOMValue, q.BOMQty, q.DocumentNo, q.Version, q.SK_BOM_ID,
q.LineName, q.LineValue, q.LineQty,
q.Effectivedate, q.Expireddate, q.SK_BomAlternative_ID,q.AlternativeName,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID;
END IF;
v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
END LOOP; --BOM
END LOOP; --銷貨

v_message :='程式完成::';
IF pinstance_id > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE ad_pinstance_id = pinstance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;

--raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
IF pinstance_id > 0 THEN
BEGIN
ResultStr := v_message;
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = pinstance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION adempiere.tg_sk_bom_fg_gen(numeric)
OWNER TO adempiere;

groovy_14

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

String sql = "select jray_xx_price_default(1000000)";
PreparedStatement pstmt = DB.prepareStatement(sql,null);
ResultSet rs = pstmt.executeQuery();
rs.close();
pstmt.close();

result="""

groovy_13

"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.MLocation

String sql = "select distinct(ct_addr1) from adempiere.z1_pcust";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, A_AD_Client_ID);
ResultSet rs = pstmt.executeQuery();
// 只試作 10筆
while (rs.next()) {

String address1 = rs.getString("ct_addr1");
MLocation location = new MLocation(A_Ctx, 0, A_TrxName);
location.setAD_Client_ID (1000000);
location.setC_Country_ID (316);
location.setAddress1 (address1);
location.save();
}
rs.close();
pstmt.close();

result="""

groovy_12

"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.MBPartner
int v_count = 0;
String sql = "SELECT a.ct_no, a.ct_name, b.ct_sname, b.ct_unino from (SELECT max(ct_no) as ct_no, ct_name FROM adempiere.z1_pcust where ct_class = '1' group by ct_class,ct_name order by ct_no, ct_name) a inner join adempiere.z1_pcust b on a.ct_no = b.ct_no order by b.ct_no";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, A_AD_Client_ID);
ResultSet rs = pstmt.executeQuery();
// 只試作 10筆
while (rs.next()) {
String value = rs.getString("ct_no");
String name = rs.getString("ct_name");
String name2 = rs.getString("ct_sname");
String duns = rs.getString("ct_unino");
MBPartner bp = new MBPartner(A_Ctx, 0, A_TrxName);
bp.setAD_Client_ID (1000000);
bp.setC_BP_Group_ID (1000000);
bp.setValue (value);
bp.setName (name);
bp.setName2 (name2);
bp.setDUNS (duns);
bp.setIsCustomer (true);
bp.setIsVendor (false);
bp.setIsEmployee (false);
bp.save();
}
rs.close();
pstmt.close();

result="""

groovy_11

"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.MBPartnerLocation

String sql = "select bp.c_bpartner_id, l.c_location_id, l.address1, p.ct_tel, p.ct_fax from adempiere.c_bpartner bp inner join adempiere.z1_pcust p on p.ct_no = bp.value inner join c_location l on p.ct_addr1 = l.address1 order by p.ct_no";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, A_AD_Client_ID);
ResultSet rs = pstmt.executeQuery();
// 只試作 10筆
while (rs.next()) {

int c_bpartner_id= rs.getInt("c_bpartner_id");
int c_location_id = rs.getInt("c_location_id");
String address1 = rs.getString("address1");
String tel = rs.getString("ct_tel")
String fax = rs.getString("ct_fax")

MBPartnerLocation MBlocation = new MBPartnerLocation(A_Ctx, 0, A_TrxName);
MBlocation.setAD_Client_ID (1000000);
MBlocation.setC_BPartner_ID (c_bpartner_id);
MBlocation.setC_Location_ID (c_location_id);
MBlocation.setName (address1);
MBlocation.setPhone (tel);
MBlocation.setFax (fax);
MBlocation.save();
}
rs.close();
pstmt.close();

result="""

groovy_10

"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="""

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="" "

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="" "

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="" "

groovy_6

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)"

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 )

"

groovy_3

"// A_Tab.setValue("Help", 'abcdefg'.getAt( 3 ))
import org.compiere.util.DB
import groovy.sql.Sql
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')

Date date1 = Date.parse("dd-MM-yyyy","31-12-2010")
String out1 = date.format("MM-dd-yyyy")

Calendar calendar = GregorianCalendar.instance
calendar.set(2010,3,1) // 1st April 2010

def lastDay = calendar.getActualMaximum(GregorianCalendar.DAY_OF_MONTH)
// assert lastDay == 30

// sql=Sql.newInstance('jdbc:sqlserver://127.0.0.1:1433;databaseName=compiere','compiere','compiere',
// 'com.microsoft.sqlserver.jdbc.SQLServerDriver' )

sql=Sql.newInstance('jdbc:postgresql://127.0.0.1:5432/idempiere','adempiere', 'adempiere','org.postgresql.Driver' )

sql.eachRow( 'select * from AD_Table' ) { println "$it.AD_Table_id-- ${it.TableName} --" }

A_Tab.setValue("Help", s.first()+"\n , "+s.last() + "\n , " + formattedDate )
"

groovy_2

"// A_Tab.setValue("Help", 'abcdefg'.getAt( 3 ))
import org.compiere.util.DB
import groovy.sql.Sql
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')

Date date1 = Date.parse("dd-MM-yyyy","31-12-2010")
String out1 = date.format("MM-dd-yyyy")

Calendar calendar = GregorianCalendar.instance
calendar.set(2010,3,1) // 1st April 2010

def lastDay = calendar.getActualMaximum(GregorianCalendar.DAY_OF_MONTH)
// assert lastDay == 30

sql=Sql.newInstance('jdbc:sqlserver://127.0.0.1:1433;databaseName=compiere','compiere','compiere', 'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
// sql=Sql.newInstance("jdbc:postgresql://127.0.0.1:5432/tg1004","adempiere", "adempiere","org.postgresql.Driver" )

sql.eachRow( 'select * from compiere.AD_Table' ) { println "$it.AD_Table_id-- ${it.TableName} --" }

A_Tab.setValue("Help", s.first()+" , "+s.last() + " , " + formattedDate )

"

groovy1

" import org.compiere.util.DB
import groovy.sql.Sql
import org.compiere.model.GridTab

int id = ((Integer)A_Tab.getValue("M_Requisition_ID")).intValue();

// def sql = " select name from m_product where m_product_id=? ";
// String r = DB.getSQLValueString(null, sql, [50002] );
// sql=Sql.newInstance('jdbc:sqlserver://127.0.0.1:1433;databaseName=compiere','compiere','compiere',
// 'com.microsoft.sqlserver.jdbc.SQLServerDriver' )
def str = "xxxxx---xxxxx";
sql=Sql.newInstance('jdbc:postgresql://127.0.0.1:5432/tg0924','adempiere', 'adempiere','org.postgresql.Driver' )
//sql.eachRow(
// 'select p.name from adempiere.m_requisitionline l inner join adempiere.m_product p on p.m_product _id=l.m_product _id where _requisition_id=' + id )
//{ str = str +"$it.name "+"\n" }

GridTab m_tabParent = A_Tab.getParentTab();
m_tabParent .setValue("Help", str )
m_tabParent.dataRefreshAll();

return """

訂閱：文章 (Atom)

2013年10月11日 星期五

2013年10月11日星期五