Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package org.aoi.enh.allocation.process;
- import java.math.BigDecimal;
- import java.math.RoundingMode;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.Timestamp;
- import java.util.logging.Level;
- import org.aoi.enh.model.MkstFSAllocation;
- import org.aoi.enh.model.MkstFSAllocationDetail;
- import org.aoi.mrp.model.X_kst_ImportSetting;
- import org.aoi.mrp.model.X_kst_PO_Allocation;
- import org.aoi.mrp.model.X_kst_PO_AllocationLine;
- import org.compiere.model.MOrderLine;
- import org.compiere.model.MProduct;
- import org.compiere.model.MUOM;
- import org.compiere.model.Query;
- import org.compiere.process.ProcessInfoParameter;
- import org.compiere.process.SvrProcess;
- import org.compiere.util.DB;
- import org.compiere.util.Env;
- public class WMSFreeStockAllocateIntegration extends SvrProcess {
- /** Client to be imported to */
- private int m_AD_Client_ID = 0;
- /** Organization to be imported to */
- private int m_AD_Org_ID = 0;
- /** RecevingDate */
- private Timestamp p_DateFrom;
- private Timestamp p_DateTo;
- /** Document Type */
- private int p_C_DocType_ID = 0;
- /** Document Type Return */
- private int p_C_DocType_ID_Return = 0;
- @Override
- protected void prepare() {
- ProcessInfoParameter[] para = getParameter();
- for (int i = 0; i < para.length; i++)
- {
- String name = para[i].getParameterName();
- if (name.equals("AD_Client_ID"))
- m_AD_Client_ID = para[i].getParameterAsInt();
- else if (name.equals("AD_Org_ID"))
- m_AD_Org_ID = para[i].getParameterAsInt();
- else
- log.log(Level.SEVERE, "Unknown Parameter: " + name);
- }
- }
- @Override
- protected String doIt() throws Exception {
- String msg ="";
- String msg2 ="";
- String sqlSetting = " Name = 'WMS'";
- X_kst_ImportSetting impSett = new Query(Env.getCtx(),X_kst_ImportSetting.Table_Name,sqlSetting,null)
- .setOrderBy("kst_importsetting_id desc")
- .setOnlyActiveRecords(true)
- .first();
- String ip = impSett.getkst_IP();
- String dbname = impSett.getkst_DatabaseName();
- String port = impSett.getkst_Port();
- String user = impSett.getkst_UserName();
- String password = impSett.getkst_Password();
- int UpdateFlag = 0;
- //MUTASI
- // String sql = "SELECT * FROM("
- // + " SELECT A.*,"
- // + " CASE WHEN B.kst_FS_AllocationDetail_UU IS NULL AND C.kst_PO_AllocationLine_UU IS NULL THEN 0 ELSE 1 END as result FROM wms_move_mutasi A "
- // + " LEFT JOIN kst_FS_AllocationDetail B ON A.id = B.kst_idwms"
- // + " LEFT JOIN kst_PO_AllocationLine C ON A.id = C.kst_idwms"
- // + " ) Z"
- // + " WHERE result = 0 ORDER BY created_at FETCH FIRST 500 ROWS ONLY";
- String sql = "SELECT * FROM wms_move_mutasi2 ORDER BY created_at FETCH FIRST 500 ROWS ONLY";
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try{
- pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
- rs = pstmt.executeQuery();
- while (rs.next()){
- MProduct prd = new Query(getCtx(), MProduct.Table_Name, " value = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(rs.getString("item_code"))
- .first();
- MUOM uom = new Query(getCtx(), MUOM.Table_Name, " uomsymbol = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(rs.getString("uom"))
- .first();
- int State = 0;
- if(prd == null && uom == null ){
- msg = "Product or UOM is not Found";
- }else{
- if(rs.getString("is_stock_allocation").equals("f")){ // Free Stock
- MkstFSAllocation fs = null;
- String ordID = rs.getString("c_order_id");
- String prtId = rs.getNString("c_bpartner_id");
- if(ordID.equals("FREE STOCK") && prtId.equals(("FREE STOCK"))){
- fs = new Query(getCtx(), MkstFSAllocation.Table_Name, " m_product_id = ? AND m_warehouse_id = ? AND type_stock = ? AND c_bpartner_id IS NULL", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(prd.get_ID(),rs.getInt("warehouse_id"),rs.getString("type_stock_erp_code"))
- .first();
- }else if(ordID.equals("FREE STOCK") ){
- fs = new Query(getCtx(), MkstFSAllocation.Table_Name, " m_product_id = ? AND m_warehouse_id = ? AND type_stock = ? AND c_bpartner_ID = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(prd.get_ID(),rs.getInt("warehouse_id"),rs.getString("type_stock_erp_code"),Integer.parseInt(rs.getString("c_bpartner_id")))
- .first();
- }else{
- fs = new Query(getCtx(), MkstFSAllocation.Table_Name, " m_product_id = ? AND m_warehouse_id = ? AND type_stock = ? AND c_bpartner_ID = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(prd.get_ID(),rs.getInt("warehouse_id"),rs.getString("type_stock_erp_code"),Integer.parseInt(rs.getString("c_bpartner_id")))
- .first();
- }
- if(fs != null){ // Free Stock Exist
- if(rs.getString("po_buyer_allocation") != null){
- MkstFSAllocationDetail line = null;
- line = new MkstFSAllocationDetail(getCtx(), 0, get_TrxName());
- line.setAD_Org_ID(fs.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", fs.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source")+rs.getString("style_po_buyer_allocation")+" "+rs.getString("article_po_buyer_allocation"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date_po_buyer_allocation"));
- line.setkst_FS_Allocation_ID(fs.get_ID());
- line.setPOReference(rs.getString("po_buyer_allocation"));
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line.setM_Product_ID(prd.get_ID());
- line.setqtyallocated(rs.getBigDecimal("qty_allocation"));
- if(!line.save()){
- return "gagal save Existing FreeStock Line";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }else{
- MkstFSAllocationDetail line = null;
- if(rs.getBigDecimal("stock_new").compareTo(Env.ZERO) > 0){
- line = new MkstFSAllocationDetail(getCtx(), 0, get_TrxName());
- line.setM_Product_ID(prd.get_ID());
- line.setAD_Org_ID(fs.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", fs.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- //line_old.set_ValueOfColumn("C_UOM_ID", fsdata.get_ValueAsInt("C_UOM_ID"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line.setkst_FS_Allocation_ID(fs.get_ID());
- line.setPOReference(rs.getString("po_buyer"));
- line.setqtyallocated(rs.getBigDecimal("stock_new").negate());
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line.setM_Product_ID(prd.get_ID());
- if(!line.save()){
- return "gagal save";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- MkstFSAllocationDetail line_old = null;
- if(rs.getBigDecimal("stock_old").compareTo(Env.ZERO) > 0){
- line_old = new MkstFSAllocationDetail(getCtx(), 0, get_TrxName());
- line_old.setM_Product_ID(prd.get_ID());
- line_old.setAD_Org_ID(fs.getAD_Org_ID());
- line_old.set_ValueOfColumn("AD_Client_ID", fs.getAD_Client_ID());
- line_old.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- //line_old.set_ValueOfColumn("C_UOM_ID", fsdata.get_ValueAsInt("C_UOM_ID"));
- line_old.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line_old.setkst_FS_Allocation_ID(fs.get_ID());
- line_old.setPOReference(rs.getString("po_buyer"));
- line_old.setqtyallocated(rs.getBigDecimal("stock_old"));
- line_old.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line_old.setM_Product_ID(prd.get_ID());
- //line_old.save();
- if(!line_old.save()){
- line.delete(true);
- State = 0;
- return "gagal save";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- }
- }else{ // New Free Stock
- fs = new MkstFSAllocation(getCtx(), 0, get_TrxName());
- fs.set_ValueOfColumn("AD_Client_ID", m_AD_Client_ID);
- fs.setAD_Org_ID(m_AD_Org_ID);
- fs.setM_Product_ID(prd.get_ID());
- fs.set_ValueOfColumn("M_Warehouse_ID", rs.getInt("warehouse_id"));
- fs.set_ValueOfColumn("M_Product_Category_ID", prd.getM_Product_Category_ID());
- BigDecimal NewQty = rs.getBigDecimal("stock_new").setScale(4, RoundingMode.UP);
- String sqlavail = "select kst_convertedqty("+NewQty+", "+prd.getM_Product_ID()+", "+uom.get_ID()+", "+prd.getC_UOM_ID()+" ) ";
- BigDecimal qtyAvail = DB.getSQLValueBD(get_TrxName(), sqlavail);
- fs.setQty(NewQty);
- fs.setQtyAvailable(qtyAvail);
- fs.setqtyallocated(Env.ZERO);
- if(!prtID.equals("FREE STOCK")){
- fs.set_ValueOfColumn("C_BPartner_ID", Integer.parseInt(rs.getString("c_bpartner_id")));
- }
- fs.set_ValueOfColumn("description", "Opening Balance New Integrate FreeStock ID "+rs.getString("id"));
- fs.set_ValueOfColumn("C_UOM_ID", uom.get_ID());
- fs.set_ValueOfColumn("type_stock", rs.getString("type_stock_erp_code"));
- if(!fs.save()){
- fs.delete(true);
- return "gagal Save";
- }else{
- MkstFSAllocationDetail line = null;
- line = new MkstFSAllocationDetail(getCtx(), 0, get_TrxName());
- line.setM_Product_ID(prd.get_ID());
- line.setAD_Org_ID(fs.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", fs.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "Opening Balance ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- //line_old.set_ValueOfColumn("C_UOM_ID", fsdata.get_ValueAsInt("C_UOM_ID"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line.setkst_FS_Allocation_ID(fs.get_ID());
- line.setPOReference(rs.getString("po_buyer"));
- line.setqtyallocated(Env.ZERO);
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- if(!line.save()){
- return "gagal save";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- }
- }else{ // PO Allocation
- X_kst_PO_Allocation poal = new Query(getCtx(), X_kst_PO_Allocation.Table_Name, " m_product_id = ? AND c_order_id = ? AND m_warehouse_id = ? AND type_stock = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(prd.get_ID(),rs.getInt("c_order_id"),rs.getInt("warehouse_id"),rs.getString("type_stock_erp_code"))
- .first();
- if(poal != null){ // PO Allocation Exist
- if(rs.getString("po_buyer_allocation") != null){
- X_kst_PO_AllocationLine line = null;
- line = new X_kst_PO_AllocationLine(getCtx(), 0, get_TrxName());
- line.setAD_Org_ID(poal.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", poal.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source")+rs.getString("style_po_buyer_allocation")+" "+rs.getString("article_po_buyer_allocation"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date_po_buyer_allocation"));
- line.setkst_po_allocation_ID(poal.get_ID());
- line.setPOReference(rs.getString("po_buyer_allocation"));
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line.setqtyallocated(rs.getBigDecimal("qty_allocation"));
- if(!line.save()){
- return "gagal save Existing PO Alloc Line";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }else{
- X_kst_PO_AllocationLine line = null;
- if(rs.getBigDecimal("stock_new").compareTo(Env.ZERO) > 0){
- line = new X_kst_PO_AllocationLine(getCtx(), 0, get_TrxName());
- line.setAD_Org_ID(poal.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", poal.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line.setkst_po_allocation_ID(poal.get_ID());
- line.setPOReference(rs.getString("po_buyer"));
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line.setqtyallocated(rs.getBigDecimal("stock_new").negate());
- if(!line.save()){
- return "gagal save Existing PO Alloc Line";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- X_kst_PO_AllocationLine line_old = null;
- if(rs.getBigDecimal("stock_old").compareTo(Env.ZERO) > 0){
- line_old = new X_kst_PO_AllocationLine(getCtx(), 0, get_TrxName());
- line_old.setAD_Org_ID(poal.getAD_Org_ID());
- line_old.set_ValueOfColumn("AD_Client_ID", poal.getAD_Client_ID());
- line_old.set_ValueOfColumn("Description", "ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- //line_old.set_ValueOfColumn("C_UOM_ID", fsdata.get_ValueAsInt("C_UOM_ID"));
- line_old.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line_old.setkst_po_allocation_ID(poal.get_ID());
- line_old.setPOReference(rs.getString("po_buyer"));
- line_old.setqtyallocated(rs.getBigDecimal("stock_old"));
- line_old.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- if(!line_old.save()){
- line.delete(true);
- State = 0;
- return "gagal save";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- }
- }else{ // New PO Allocation
- MOrderLine po_supp_line = new Query(getCtx(), MOrderLine.Table_Name, " C_Order_ID = ? AND M_Product_ID = ? ", get_TrxName())
- .setClient_ID()
- .setOnlyActiveRecords(true)
- .setParameters(rs.getInt("c_order_id"),prd.get_ID())
- .first();
- if(po_supp_line == null) {
- msg = "Some datas is not found";
- }else{
- BigDecimal NewQty = rs.getBigDecimal("stock_new").setScale(4, RoundingMode.UP);
- String sqlavail = "select kst_convertedqty("+NewQty+", "+prd.getM_Product_ID()+", "+uom.get_ID()+", "+prd.getC_UOM_ID()+" ) ";
- BigDecimal qtyAvail = DB.getSQLValueBD(get_TrxName(), sqlavail);
- poal = new X_kst_PO_Allocation(getCtx(), 0, get_TrxName());
- poal.set_ValueOfColumn("AD_Client_ID", m_AD_Client_ID);
- poal.setAD_Org_ID(m_AD_Org_ID);
- poal.setC_Order_ID(rs.getInt("c_order_id"));
- poal.setC_OrderLine_ID(po_supp_line.get_ID());
- poal.setM_Product_ID(prd.getM_Product_ID());
- poal.setQty(NewQty);
- poal.setQtyAvailable(qtyAvail);
- poal.set_ValueOfColumn("C_UOM_ID", uom.get_ID());
- poal.set_ValueOfColumn("M_Product_Category_ID", prd.getM_Product_Category_ID());
- poal.setqtyallocated(Env.ZERO);
- poal.set_ValueOfColumn("Description", "Opening Balance ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- poal.set_ValueOfColumn("M_Warehouse_ID", rs.getInt("warehouse_id"));
- poal.set_ValueOfColumn("type_stock", rs.getString("type_stock_erp_code"));
- if(!poal.save()){
- poal.delete(true);
- return "gagal Save PO Alloc Header";
- }else{
- X_kst_PO_AllocationLine line = null;
- line = new X_kst_PO_AllocationLine(getCtx(), 0, get_TrxName());
- line.setAD_Org_ID(poal.getAD_Org_ID());
- line.set_ValueOfColumn("AD_Client_ID", poal.getAD_Client_ID());
- line.set_ValueOfColumn("Description", "Opening Balance ID "+rs.getString("id")+" | "+rs.getString("note")+" | "+rs.getString("source"));
- line.set_ValueOfColumn("kst_lcdate", rs.getTimestamp("lc_date"));
- line.setkst_po_allocation_ID(poal.get_ID());
- line.setPOReference(rs.getString("po_buyer"));
- line.set_ValueOfColumn("kst_idwms", rs.getString("id"));
- line.setqtyallocated(Env.ZERO);
- if(!line.save()){
- return "gagal save Existing PO Alloc Line";
- }else{
- msg += rs.getString("id")+" , ";
- State = 1;
- }
- }
- }
- }
- }
- }
- if(State >= 1){
- String wms_id = rs.getString("id");
- DB.executeUpdate(" select * "
- + " FROM dblink('"
- + "dbname=" + dbname +" port=" + port +" host=" + ip +" user=" + user +" password=" + password +"' "
- + ",'Update history_material_stocks set is_integrate = true, integration_date = now() "
- + "WHERE id =''" + wms_id +"''' ) tt(updated text);");
- System.out.println("WMS #"+wms_id+" Updated IsIntegration...");
- }
- }
- }catch (Exception e){
- msg = e.toString();
- return msg;
- }finally{
- DB.close(rs, pstmt);
- pstmt = null;
- rs = null;
- }
- //msg = " \n " + msg2;
- return msg;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement