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