IdolMartGenerateProductSearchKey4September

package org.wirabumi.gen.oez.callout;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;

import org.hibernate.criterion.Restrictions;
import org.openbravo.base.exception.OBException;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBCriteria;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.ad_callouts.SimpleCallout.CalloutInfo;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.ad.system.ClientInformation;
import org.openbravo.model.ad.utility.Tree;
import org.openbravo.model.ad.utility.TreeNode;
import org.openbravo.model.common.plm.ProductCategory;

public class IdolMartGenerateProductSearchKey implements GenerateProductSearchKey{

    public String getProductSearchKey(CalloutInfo info) {
        //get key from sub category
        String strProductCategoryId = info.getStringParameter("inpmProductCategoryId", null);

        // get preference
        String myPreference = Utility.getPreference(info.vars, "IdolMartPreference", null);

        ClientInformation ClientInfo = OBContext.getOBContext().getCurrentClient().getClientInformationList().get(0);
        Tree adTree = ClientInfo.getPrimaryTreeProductCategory();

        ProductCategory pcSubId = OBDal.getInstance().get(ProductCategory.class, strProductCategoryId);
        String subKey = pcSubId.getSearchKey();
        String[] subKeyParts = subKey.split("-");
        String SK = subKeyParts[0].trim();

        //get key from main category
        OBCriteria<TreeNode> treeNodeCriteria = OBDal.getInstance().createCriteria(TreeNode.class);
        treeNodeCriteria.add(Restrictions.eq(TreeNode.PROPERTY_TREE, adTree));
        treeNodeCriteria.add(Restrictions.eq(TreeNode.PROPERTY_NODE, strProductCategoryId));

        TreeNode treeNode = treeNodeCriteria.list().get(0);
        String pcMainId = treeNode.getReportSet();
        if (pcMainId.equalsIgnoreCase("0")) {
            pcMainId = subKey;
        }
        ProductCategory pcMain = OBDal.getInstance().get(ProductCategory.class, pcMainId);
        String mainKey = pcMain.getSearchKey();
        String[] mainKeyParts = mainKey.split("-");
        String MK = mainKeyParts[0].trim();

        if (pcMain.getOezProductsequence() == null) {
            //get sequence number
            String sql = "select count(*) as jumlahproduct" +
                    " from ad_treenode a" +
                    " inner join m_product b on b.m_product_category_id=a.node_id" +
                    " where a.parent_id=? " +
                    " and a.ad_tree_id=? "  +
                    " and b.m_product_category_id=? ";
            java.sql.Connection conn = OBDal.getInstance().getConnection();
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, pcMainId);
                ps.setString(2, adTree.getId());
                ps.setString(3, strProductCategoryId);
                ResultSet rs = ps.executeQuery();
                int jumlahProduct = 0;
                while (rs.next()) {
                    jumlahProduct = rs.getInt("jumlahProduct");
                }
                jumlahProduct++;

                DecimalFormat myFormatter = new DecimalFormat("00000000");
                String strJumlahProduct = myFormatter.format(jumlahProduct);

                //set value product sequence on tabel
                Long JumlahProductLong = Long.valueOf(jumlahProduct);
                pcMain.setOezProductsequence(JumlahProductLong);
                OBDal.getInstance().save(pcMain);

                //put to field search key window product
                String searchKey = myPreference+MK+SK+strJumlahProduct;
                return searchKey;
            }
            catch (Exception e) {
                e.printStackTrace();
                throw new OBException(e);
            }
        }
        else
        {
            long productSequence = pcMain.getOezProductsequence();
            long addProductSequence = productSequence + 1;
            pcMain.setOezProductsequence(addProductSequence);
            OBDal.getInstance().save(pcMain);

            DecimalFormat myFormatter = new DecimalFormat("00000000");
            String productSequenceString = myFormatter.format(addProductSequence);
            String searchKey = myPreference+MK+SK+productSequenceString;
            return searchKey;
        }
    }
}