ProcessApprovingSalesOrder

package org.infinite.retail.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.openbravo.base.exception.OBException;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.ad.process.ProcessInstance;
import org.openbravo.model.ad.ui.Process;
import org.openbravo.model.common.enterprise.Organization;
import org.openbravo.model.common.order.Order;
import org.openbravo.scheduling.ProcessBundle;
import org.openbravo.service.db.CallProcess;
import org.openbravo.service.db.DalBaseProcess;

public class ProcessApprovingSalesOrder extends DalBaseProcess {
    private final String doc_status_to = "CO";
    private final String draftStatus="DR";
    private final String orderProcessID="104";
    private final String closedStatus="CL";
    private final String voidStatus="VO";

    protected Logger log4j = Logger.getLogger(this.getClass());
    @Override
    protected void doExecute(ProcessBundle bundle) throws Exception {
        // TODO Auto-generated method stub

        //load unprocessed list Order
        VariablesSecureApp varsAux = bundle.getContext().toVars();
        String org = bundle.getContext().getOrganization();
        Organization orgId = OBDal.getInstance().get(Organization.class, org);
        String clientId = orgId.getClient().getId();

        String posOrderWhereClause = Utility.getPreference(varsAux, "POS_order_pending_complete_whereclause", null);
        List<String> orderList = getOrderList(posOrderWhereClause, clientId);

        for (String order : orderList) {
            Order orderId  = OBDal.getInstance().get(Order.class, order);
            //cek apakah dari CL atau VO, jika ya, maka exception
            if (orderId.getDocumentStatus().equalsIgnoreCase(closedStatus)||
                    orderId.getDocumentStatus().equalsIgnoreCase(voidStatus))
                continue;
            orderId.setDocumentStatus(draftStatus);
            orderId.setDocumentAction(doc_status_to);
            OBDal.getInstance().save(orderId);
            OBDal.getInstance().flush();
            // call store procedure
            doExecuteProcedureCall(orderId.getId(), orderProcessID);
        }
    }

    private List<String> getOrderList(String posOrderWhereClause, String clientId) {
        if(posOrderWhereClause.isEmpty()) {
            posOrderWhereClause = "(1=1)";
        }
        String sql = " select e.c_order_id as orderId" +
                " from c_order e" +
                " inner join c_doctype as b on b.c_doctype_id = e.c_doctypetarget_id" +
                " where e.issotrx ='Y'" +
                " and e.processed ='N'" +
                " and b.docbasetype ='SOO'" +
                " and b.docsubtypeso ='WR' " +
                " and e.ad_client_id = ?" +
                " and ?";

        java.sql.Connection conn = OBDal.getInstance().getConnection();
        ArrayList<String> output = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, clientId );
            ps.setString(2, posOrderWhereClause);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                String orderId = rs.getString("orderID");
                System.out.println(orderId);
                output.add(orderId);
            }
            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;
    }
}