ProcessApprovingTransferRequest.java

package org.infinite.retail.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.openbravo.base.exception.OBException;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.ConnectionProvider;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.model.ad.process.ProcessInstance;
import org.openbravo.model.ad.ui.Process;
import org.openbravo.model.common.enterprise.Locator;
import org.openbravo.model.common.enterprise.Organization;
import org.openbravo.model.common.plm.Product;
import org.openbravo.model.materialmgmt.onhandquantity.StorageDetail;
import org.openbravo.model.materialmgmt.transaction.InternalMovement;
import org.openbravo.model.materialmgmt.transaction.InternalMovementLine;
import org.openbravo.scheduling.ProcessBundle;
import org.openbravo.scheduling.ProcessLogger;
import org.openbravo.service.db.CallProcess;
import org.openbravo.service.db.DalBaseProcess;
import org.openbravo.service.db.DalConnectionProvider;

public class ProcessApprovingTransferRequest extends DalBaseProcess {

    private final String inoutProcessID="122";
    private final String closedStatus="CL";
    private final String voidStatus="VO";
    private final String draftStatus="DR";
    private final String doc_status_to = "CO";

    final ConnectionProvider conn = new DalConnectionProvider();
    protected Logger log4j = Logger.getLogger(this.getClass());
    private ProcessLogger logger;

    @Override
    protected void doExecute(ProcessBundle bundle) throws Exception {
        // get logger
        logger = bundle.getLogger();
        logger.log("Starting background Process Approving Transfer Request\n");
        logger.log("------------------------------------------------------\n");

        String org = bundle.getContext().getOrganization();
        Organization orgId = OBDal.getInstance().get(Organization.class, org);
        String clientId = orgId.getClient().getId();

        List<String> TransferRequestIdList = getMovementList(clientId);

        for(String transferRequest : TransferRequestIdList) {
            InternalMovement transferRequestId = OBDal.getInstance().get(InternalMovement.class, transferRequest);

            String docstatus=transferRequestId.getOezDocstatus();
            String docaction=transferRequestId.getOezDocaction();
            String stringErorr = null;
            int erorrMovement = 0;

            //use for verifed that quantity on hand not null
            for (InternalMovementLine transferRequestLineId : transferRequestId.getMaterialMgmtInternalMovementLineList())
            {
                Locator bin = transferRequestLineId.getStorageBin();
                Product productId = transferRequestLineId.getProduct();
                BigDecimal quantityOnHand = BigDecimal.valueOf(0);

                for (StorageDetail storageDetail : bin.getMaterialMgmtStorageDetailList()) {
                    if (storageDetail.getProduct().equals(productId))
                        quantityOnHand=storageDetail.getQuantityOnHand();
                }

                String movementDocumentNo = transferRequestId.getDocumentNo();
                String productName = transferRequestLineId.getProduct().getName();
                int qntyMovementLines = transferRequestLineId.getMovementQuantity().intValue();
                String uom = transferRequestLineId.getUOM().getName();
                String originalWarehouse = transferRequestLineId.getStorageBin().getSearchKey();
                if(transferRequestLineId.getMovementQuantity().compareTo(quantityOnHand) > 0 )
                {
                    stringErorr =  String.format("Insufficient stock for Document no %s Product %s with %2d UOM %s and Storage Bin %s ", movementDocumentNo, productName, qntyMovementLines, uom, originalWarehouse);
                    logger.log(stringErorr +"\n"); // < agar tidak exception

                    //write erorr to description
                    transferRequestId.setDescription(stringErorr);
                    OBDal.getInstance().save(transferRequestId);
                    OBDal.getInstance().flush();
                    erorrMovement++;
                    continue;
                }
            }

            if(erorrMovement > 0)
                continue;

            //cek apakah dari CL atau VO, jika ya, maka exception
            if (transferRequestId.getOezDocstatus().equalsIgnoreCase(closedStatus)||
                    transferRequestId.getOezDocstatus().equalsIgnoreCase(voidStatus))
                continue;

            // call store procedure
            OBError oberror = doExecuteProcedureCall(transferRequestId.getId(), inoutProcessID);
            String pesan = oberror.getMessage();

            if (pesan == null || pesan.isEmpty()) {
                transferRequestId.setOezDocstatus(doc_status_to);
                transferRequestId.setOezDocaction(doc_status_to);
                transferRequestId.setDescription("");
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                pesan = "aproved to completed";
                logger.log("Transfer Request Document No:" + transferRequestId.getDocumentNo() + " - " + pesan + "\n");
            }

            if (oberror.getType().equalsIgnoreCase("Error")){
                OBDal.getInstance().refresh(transferRequestId);
                transferRequestId.setOezDocstatus(docstatus);
                transferRequestId.setOezDocaction(docaction);
                transferRequestId.setDescription(stringErorr);
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                oberror.setType("Error");
                oberror.setTitle("Error");
                oberror.setMessage(pesan);
                bundle.setResult(oberror);
            }
            logger.log("--------------------------------------------------\n");
        }

        logger.log("--------------------------------------------------\n");
        logger.log("background Process Approving Transfer Request [DONE]");
    }

    private List<String> getMovementList (String clientId) {
        List<String> output = new ArrayList<>();

        String sql= "select m_movement_id as movementId" +
                " from m_movement" +
                " where ad_client_id= ?" +
                " and em_oez_is_tranfer_request='Y'"+
                " and em_oez_docstatus='OEZ_RELEASE'"+
                " and processed='N' ";

        java.sql.Connection conn = OBDal.getInstance().getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, clientId );
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                String movementId = rs.getString("movementId");
                output.add(movementId);
            }
            return output;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new OBException(e);
        }

    }

    private OBError doExecutePrpackage org.infinite.retail.process;

import java.math.BigDecimal;

public class ProcessApprovingTransferRequest extends DalBaseProcess {

    private final String inoutProcessID="122";
    private final String closedStatus="CL";
    private final String voidStatus="VO";
    private final String draftStatus="DR";
    private final String doc_status_to = "CO";

    final ConnectionProvider conn = new DalConnectionProvider();
    protected Logger log4j = Logger.getLogger(this.getClass());
    private ProcessLogger logger;

    @Override
    protected void doExecute(ProcessBundle bundle) throws Exception {
        // get logger
        logger = bundle.getLogger();
        logger.log("Starting background Process Approving Transfer Request\n");
        logger.log("------------------------------------------------------\n");

        String org = bundle.getContext().getOrganization();
        Organization orgId = OBDal.getInstance().get(Organization.class, org);
        String clientId = orgId.getClient().getId();

        List<String> TransferRequestIdList = getMovementList(clientId);

        for(String transferRequest : TransferRequestIdList) {
            InternalMovement transferRequestId = OBDal.getInstance().get(InternalMovement.class, transferRequest);

            String docstatus=transferRequestId.getOezDocstatus();
            String docaction=transferRequestId.getOezDocaction();
            String stringErorr = null;
            int erorrMovement = 0;

            //use for verifed that quantity on hand not null
            for (InternalMovementLine transferRequestLineId : transferRequestId.getMaterialMgmtInternalMovementLineList())
            {
                Locator bin = transferRequestLineId.getStorageBin();
                Product productId = transferRequestLineId.getProduct();
                BigDecimal quantityOnHand = BigDecimal.valueOf(0);

                for (StorageDetail storageDetail : bin.getMaterialMgmtStorageDetailList()) {
                    if (storageDetail.getProduct().equals(productId))
                        quantityOnHand=storageDetail.getQuantityOnHand();
                }

                String movementDocumentNo = transferRequestId.getDocumentNo();
                String productName = transferRequestLineId.getProduct().getName();
                int qntyMovementLines = transferRequestLineId.getMovementQuantity().intValue();
                String uom = transferRequestLineId.getUOM().getName();
                String originalWarehouse = transferRequestLineId.getStorageBin().getSearchKey();
                if(transferRequestLineId.getMovementQuantity().compareTo(quantityOnHand) > 0 )
                {
                    stringErorr =  String.format("Insufficient stock for Document no %s Product %s with %2d UOM %s and Storage Bin %s ", movementDocumentNo, productName, qntyMovementLines, uom, originalWarehouse);
                    logger.log(stringErorr +"\n"); // < agar tidak exception

                    //write erorr to description
                    transferRequestId.setDescription(stringErorr);
                    OBDal.getInstance().save(transferRequestId);
                    OBDal.getInstance().flush();
                    erorrMovement++;
                    continue;
                }
            }

            if(erorrMovement > 0)
                continue;

            //cek apakah dari CL atau VO, jika ya, maka exception
            if (transferRequestId.getOezDocstatus().equalsIgnoreCase(closedStatus)||
                    transferRequestId.getOezDocstatus().equalsIgnoreCase(voidStatus))
                continue;

            // call store procedure
            OBError oberror = doExecuteProcedureCall(transferRequestId.getId(), inoutProcessID);
            String pesan = oberror.getMessage();

            if (pesan == null || pesan.isEmpty()) {
                transferRequestId.setOezDocstatus(doc_status_to);
                transferRequestId.setOezDocaction(doc_status_to);
                transferRequestId.setDescription("");
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                pesan = "aproved to completed";
                logger.log("Transfer Request Document No:" + transferRequestId.getDocumentNo() + " - " + pesan + "\n");
            }

            if (oberror.getType().equalsIgnoreCase("Error")){
                OBDal.getInstance().refresh(transferRequestId);
                transferRequestId.setOezDocstatus(docstatus);
                transferRequestId.setOezDocaction(docaction);
                transferRequestId.setDescription(stringErorr);
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                oberror.setType("Error");
                oberror.setTitle("Error");
                oberror.setMessage(pesan);
                bundle.setResult(oberror);
            }
            logger.log("--------------------------------------------------\n");
        }

        logger.log("--------------------------------------------------\n");
        logger.log("background Process Approving Transfer Request [DONE]");
    }

    private List<String> getMovementList (String clientId) {
        List<String> output = new ArrayList<>();

        String sql= "select m_movement_id as movementId" +
                " from m_movement" +
                " where ad_client_id= ?" +
                " and em_oez_is_tranfer_request='Y'"+
                " and em_oez_docstatus='OEZ_RELEASE'"+
                " and processed='N' ";

        java.sql.Connection conn = OBDal.getInstance().getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, clientId );
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                String movementId = rs.getString("movementId");
                output.add(movepackage org.infinite.retail.process;

import java.math.BigDecimal;

public class ProcessApprovingTransferRequest extends DalBaseProcess {

    private final String inoutProcessID="122";
    private final String closedStatus="CL";
    private final String voidStatus="VO";
    private final String draftStatus="DR";
    private final String doc_status_to = "CO";

    final ConnectionProvider conn = new DalConnectionProvider();
    protected Logger log4j = Logger.getLogger(this.getClass());
    private ProcessLogger logger;

    @Override
    protected void doExecute(ProcessBundle bundle) throws Exception {
        // get logger
        logger = bundle.getLogger();
        logger.log("Starting background Process Approving Transfer Request\n");
        logger.log("------------------------------------------------------\n");

        String org = bundle.getContext().getOrganization();
        Organization orgId = OBDal.getInstance().get(Organization.class, org);
        String clientId = orgId.getClient().getId();

        List<String> TransferRequestIdList = getMovementList(clientId);

        for(String transferRequest : TransferRequestIdList) {
            InternalMovement transferRequestId = OBDal.getInstance().get(InternalMovement.class, transferRequest);

            String docstatus=transferRequestId.getOezDocstatus();
            String docaction=transferRequestId.getOezDocaction();
            String stringErorr = null;
            int erorrMovement = 0;

            //use for verifed that quantity on hand not null
            for (InternalMovementLine transferRequestLineId : transferRequestId.getMaterialMgmtInternalMovementLineList())
            {
                Locator bin = transferRequestLineId.getStorageBin();
                Product productId = transferRequestLineId.getProduct();
                BigDecimal quantityOnHand = BigDecimal.valueOf(0);

                for (StorageDetail storageDetail : bin.getMaterialMgmtStorageDetailList()) {
                    if (storageDetail.getProduct().equals(productId))
                        quantityOnHand=storageDetail.getQuantityOnHand();
                }

                String movementDocumentNo = transferRequestId.getDocumentNo();
                String productName = transferRequestLineId.getProduct().getName();
                int qntyMovementLines = transferRequestLineId.getMovementQuantity().intValue();
                String uom = transferRequestLineId.getUOM().getName();
                String originalWarehouse = transferRequestLineId.getStorageBin().getSearchKey();
                if(transferRequestLineId.getMovementQuantity().compareTo(quantityOnHand) > 0 )
                {
                    stringErorr =  String.format("Insufficient stock for Document no %s Product %s with %2d UOM %s and Storage Bin %s ", movementDocumentNo, productName, qntyMovementLines, uom, originalWarehouse);
                    logger.log(stringErorr +"\n"); // < agar tidak exception

                    //write erorr to description
                    transferRequestId.setDescription(stringErorr);
                    OBDal.getInstance().save(transferRequestId);
                    OBDal.getInstance().flush();
                    erorrMovement++;
                    continue;
                }
            }

            if(erorrMovement > 0)
                continue;

            //cek apakah dari CL atau VO, jika ya, maka exception
            if (transferRequestId.getOezDocstatus().equalsIgnoreCase(closedStatus)||
                    transferRequestId.getOezDocstatus().equalsIgnoreCase(voidStatus))
                continue;

            // call store procedure
            OBError oberror = doExecuteProcedureCall(transferRequestId.getId(), inoutProcessID);
            String pesan = oberror.getMessage();

            if (pesan == null || pesan.isEmpty()) {
                transferRequestId.setOezDocstatus(doc_status_to);
                transferRequestId.setOezDocaction(doc_status_to);
                transferRequestId.setDescription("");
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                pesan = "aproved to completed";
                logger.log("Transfer Request Document No:" + transferRequestId.getDocumentNo() + " - " + pesan + "\n");
            }

            if (oberror.getType().equalsIgnoreCase("Error")){
                OBDal.getInstance().refresh(transferRequestId);
                transferRequestId.setOezDocstatus(docstatus);
                transferRequestId.setOezDocaction(docaction);
                transferRequestId.setDescription(stringErorr);
                OBDal.getInstance().save(transferRequestId);
                OBDal.getInstance().flush();
                oberror.setType("Error");
                oberror.setTitle("Error");
                oberror.setMessage(pesan);
                bundle.setResult(oberror);
            }
            logger.log("--------------------------------------------------\n");
        }

        logger.log("--------------------------------------------------\n");
        logger.log("background Process Approving Transfer Request [DONE]");
    }

    private List<String> getMovementList (String clientId) {
        List<String> output = new ArrayList<>();

        String sql= "select m_movement_id as movementId" +
                " from m_movement" +
                " where ad_client_id= ?" +
                " and em_oez_is_tranfer_request='Y'"+
                " and em_oez_docstatus='OEZ_RELEASE'"+
                " and processed='N' ";

        java.sql.Connection conn = OBDal.getInstance().getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, clientId );
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                String movementId = rs.getString("movementId");
                output.add(movementId);
            }
            return output;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new OBException(e);
        }

    }

    private OBError doExecuteProcedureCall(String recordID, String processID){
        OBError oberror = new OBError();
        oberror.setType("Success");
        oberror.setTitle("Success");
        OBContext.setAdminMode();
        final Process process = OBDal.getInstance().get(Process.class, processID);
        log4j.debug("execute procedure call "+process.getName());
        final ProcessInstance pInstance = CallProcess.getInstance().call(process, recordID, null);
        long result = pInstance.getResult();
        if (result==0){
            String errormessage = pInstance.getErrorMsg();
            log4j.debug("error message "+errormessage);
            oberror.setType("Error");
            oberror.setTitle("Error");
            oberror.setMessage(errormessage);
        }
        OBContext.restorePreviousMode();
        return oberror;
    }

}
mentId);
            }
            return output;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new OBException(e);
        }

    }

    private OBError doExecuteProcedureCall(String recordID, String processID){
        OBError oberror = new OBError();
        oberror.setType("Success");
        oberror.setTitle("Success");
        OBContext.setAdminMode();
        final Process process = OBDal.getInstance().get(Process.class, processID);
        log4j.debug("execute procedure call "+process.getName());
        final ProcessInstance pInstance = CallProcess.getInstance().call(process, recordID, null);
        long result = pInstance.getResult();
        if (result==0){
            String errormessage = pInstance.getErrorMsg();
            log4j.debug("error message "+errormessage);
            oberror.setType("Error");
            oberror.setTitle("Error");
            oberror.setMessage(errormessage);
        }
        OBContext.restorePreviousMode();
        return oberror;
    }

}
ocedureCall(String recordID, String processID){
        OBError oberror = new OBError();
        oberror.setType("Success");
        oberror.setTitle("Success");
        OBContext.setAdminMode();
        final Process process = OBDal.getInstance().get(Process.class, processID);
        log4j.debug("execute procedure call "+process.getName());
        final ProcessInstance pInstance = CallProcess.getInstance().call(process, recordID, null);
        long result = pInstance.getResult();
        if (result==0){
            String errormessage = pInstance.getErrorMsg();
            log4j.debug("error message "+errormessage);
            oberror.setType("Error");
            oberror.setTitle("Error");
            oberror.setMessage(errormessage);
        }
        OBContext.restorePreviousMode();
        return oberror;
    }

}