ProcessApprovingSalesOrder

package org.infinite.retail.process;

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.hibernate.criterion.Restrictions;
import org.openbravo.base.exception.OBException;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBCriteria;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.ConnectionProvider;
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.erpCommon.utility.OBMessageUtils;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.ad.domain.Preference;
import org.openbravo.model.ad.process.ProcessInstance;
import org.openbravo.model.ad.system.Client;
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.scheduling.ProcessLogger;
import org.openbravo.service.db.CallProcess;
import org.openbravo.service.db.DalBaseProcess;
import org.openbravo.service.db.DalConnectionProvider;

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

    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 Sales Order \n");

        //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 WhereClause = "POS_order_pending_complete_whereclause";
        Client client = orgId.getClient();

        OBCriteria<Preference> preferenceCriteria = OBDal.getInstance().createCriteria(Preference.class);
        preferenceCriteria.add(Restrictions.eq(Preference.PROPERTY_ATTRIBUTE, WhereClause));
        preferenceCriteria.add(Restrictions.eq(Preference.PROPERTY_CLIENT, client));
        Preference posOrderWhereClausea = preferenceCriteria.list().get(0);
        String posOrderWhereClause = posOrderWhereClausea.getSearchKey();

        List<String> orderList = getOrderList(posOrderWhereClause, clientId);

        for (String order : orderList) {
            Order orderId  = OBDal.getInstance().get(Order.class, order);

            String docstatus=orderId.getDocumentStatus();
            String docaction=orderId.getDocumentAction();

            //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
            OBError oberror = doExecuteProcedureCall(orderId.getId(), orderProcessID);
            String pesan = oberror.getMessage();
            if (pesan == null || pesan.isEmpty()) {
                pesan = "aproved to completed";
            }
            logger.log("Sales Order Document No:" + orderId.getDocumentNo() + " - " + pesan + "\n");

            if (oberror.getType().equalsIgnoreCase("Error")){
                OBDal.getInstance().refresh(orderId);
                orderId.setDocumentStatus(docstatus);
                orderId.setDocumentAction(docaction);
                OBDal.getInstance().save(orderId);
                OBDal.getInstance().flush();
                oberror.setType("Error");
                oberror.setTitle("Error");
                oberror.setMessage(pesan);
                bundle.setResult(oberror);
            }
        }
    }

    private List<String> getOrderList(String posOrderWhereClause, String clientId) {
        List<String> output = new ArrayList<>();
        if(posOrderWhereClause == null) {
            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 "+posOrderWhereClause;

        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 orderId = rs.getString("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;
    }

}