keywordsearch.java

国外的一套开源CRM

     *@return ArrayList of productId Strings
     */
    /* TODO: DEJ 20031025 delete this if not used in the near future
    public static ArrayList productsByKeywords(String keywordsString, GenericDelegator delegator, String categoryId, String visitId, boolean anyPrefix, boolean anySuffix, boolean isAnd) {
        if (delegator == null) {
            return null;
        }
        String helperName = null;

        helperName = delegator.getEntityHelperName("ProductKeyword");
        boolean useCategory = (categoryId != null && categoryId.length() > 0) ? true : false;
        boolean removeStems = UtilProperties.propertyValueEquals("prodsearch", "remove.stems", "true");

        ArrayList pbkList = new ArrayList(100);

        List keywordFirstPass = makeKeywordList(keywordsString);
        List keywordList = fixKeywords(keywordFirstPass, anyPrefix, anySuffix, removeStems, isAnd);

        if (keywordList.size() == 0) {
            return null;
        }

        List params = new ArrayList();
        String sql = getSearchSQL(keywordList, params, useCategory, isAnd);

        if (sql == null) {
            return null;
        }
        if (useCategory) {
            params.add(categoryId);
            params.add(UtilDateTime.nowTimestamp());
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = ConnectionFactory.getConnection(helperName);
            statement = connection.prepareStatement(sql);

            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof String) {
                    statement.setString(i + 1, (String) param);
                } else if (param instanceof Timestamp) {
                    statement.setTimestamp(i + 1, (Timestamp) param);
                } else {
                    //in this class we only put Strings and Timestamps in there, but warn anyway...
                    Debug.logWarning("Found a keyword search query parameter with an unknown type: " + param.getClass().getName(), module);
                }
                if (Debug.verboseOn()) Debug.logVerbose("[KeywordSearch] Params: " + (String) params.get(i), module);
            }
            resultSet = statement.executeQuery();
            Set idSet = new HashSet();
            while (resultSet.next()) {
                //since there is a chance of duplicate IDs, check to see if the ID is already in the list to eliminate all but the first
                String productId = resultSet.getString("PRODUCT_ID");
                if (productId != null && !idSet.contains(productId)) {
                    pbkList.add(productId);
                    idSet.add(productId);
                    // Debug.logInfo("PRODUCT_ID=" + productId + " TOTAL_WEIGHT=" + resultSet.getInt("TOTAL_WEIGHT"), module);
                }
            }
            if (Debug.infoOn()) Debug.logInfo("[KeywordSearch] got " + pbkList.size() + " results found for search string: [" + keywordsString + "], keyword combine operator is AND? [" + isAnd + "], categoryId=" + categoryId + ", anyPrefix=" + anyPrefix + ", anySuffix=" + anySuffix + ", removeStems=" + removeStems, module);
            //if (Debug.infoOn()) Debug.logInfo("pbkList=" + pbkList, module);

            try {
                GenericValue productKeywordResult = delegator.makeValue("ProductKeywordResult", null);
                Long nextPkrSeqId = delegator.getNextSeqId("ProductKeywordResult");

                productKeywordResult.set("productKeywordResultId", nextPkrSeqId.toString());
                productKeywordResult.set("visitId", visitId);
                if (useCategory) productKeywordResult.set("productCategoryId", categoryId);
                productKeywordResult.set("searchString", keywordsString);
                productKeywordResult.set("intraKeywordOperator", (isAnd ? "AND" : "OR"));
                productKeywordResult.set("anyPrefix", new Boolean(anyPrefix));
                productKeywordResult.set("anySuffix", new Boolean(anySuffix));
                productKeywordResult.set("removeStems", new Boolean(removeStems));
                productKeywordResult.set("numResults", new Long(pbkList.size()));
                productKeywordResult.create();
            } catch (Exception e) {
                Debug.logError(e, "Error saving keyword result stats", module);
                Debug.logError("[KeywordSearch] Stats are: got " + pbkList.size() + " results found for search string: [" + keywordsString + "], keyword combine operator is " + (isAnd ? "AND" : "OR") + ", categoryId=" + categoryId + ", anyPrefix=" + anyPrefix + ", anySuffix=" + anySuffix + ", removeStems=" + removeStems, module);
            }

            if (pbkList.size() == 0) {
                return null;
            } else {
                return pbkList;
            }
        } catch (java.sql.SQLException sqle) {
            Debug.logError(sqle, module);
        } catch (GenericEntityException e) {
            Debug.logError(e, module);
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException sqle) {}
            try {
                if (statement != null)
                    statement.close();
            } catch (SQLException sqle) {}
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException sqle) {}
        }
        return null;
    }
     */

    /* TODO: DEJ 20031025 delete this if not used in the near future
    protected static String getSearchSQL(List keywords, List params, boolean useCategory, boolean isAnd) {
        if (keywords == null || keywords.size() <= 0)
            return null;
        StringBuffer sql = new StringBuffer();
        Iterator keywordIter = keywords.iterator();

        // AND EXAMPLE:
        // SELECT DISTINCT P1.PRODUCT_ID, (P1.RELEVANCY_WEIGHT + P2.RELEVANCY_WEIGHT + P3.RELEVANCY_WEIGHT) AS TOTAL_WEIGHT FROM PRODUCT_KEYWORD P1, PRODUCT_KEYWORD P2, PRODUCT_KEYWORD P3
        // WHERE P1.PRODUCT_ID=P2.PRODUCT_ID AND P1.PRODUCT_ID=P3.PRODUCT_ID AND P1.KEYWORD LIKE 'TI%' AND P2.KEYWORD LIKE 'HOUS%' AND P3.KEYWORD = '1003027' ORDER BY TOTAL_WEIGHT DESC
        // AND EXAMPLE WITH CATEGORY CONSTRAINT:
        // SELECT DISTINCT P1.PRODUCT_ID, PCM.SEQUENCE_NUM AS CAT_SEQ_NUM, TOTAL_WEIGHT = P1.RELEVANCY_WEIGHT + P2.RELEVANCY_WEIGHT + P3.RELEVANCY_WEIGHT FROM PRODUCT_KEYWORD P1, PRODUCT_KEYWORD P2, PRODUCT_KEYWORD P3, PRODUCT_CATEGORY_MEMBER PCM
        // WHERE P1.PRODUCT_ID=P2.PRODUCT_ID AND P1.PRODUCT_ID=P3.PRODUCT_ID AND P1.KEYWORD LIKE 'TI%' AND P2.KEYWORD LIKE 'HOUS%' AND P3.KEYWORD = '1003027' AND P1.PRODUCT_ID=PCM.PRODUCT_ID AND PCM.PRODUCT_CATEGORY_ID='foo' AND (PCM.THRU_DATE IS NULL OR PCM.THRU_DATE > ?) ORDER BY CAT_SEQ_NUM, TOTAL_WEIGHT DESC

        // ORs are a little more complicated, so get individual results group them by PRODUCT_ID and sum the RELEVANCY_WEIGHT
        // OR EXAMPLE:
        // SELECT DISTINCT P1.PRODUCT_ID, SUM(P1.RELEVANCY_WEIGHT) AS TOTAL_WEIGHT FROM PRODUCT_KEYWORD P1
        // WHERE (P1.KEYWORD LIKE 'TI%' OR P1.KEYWORD LIKE 'HOUS%' OR P1.KEYWORD = '1003027') GROUP BY P1.PRODUCT_ID ORDER BY TOTAL_WEIGHT DESC
        // OR EXAMPLE WITH CATEGORY CONSTRAINT:
        // SELECT DISTINCT P1.PRODUCT_ID, MIN(PCM.SEQUENCE_NUM) AS CAT_SEQ_NUM, TOTAL_WEIGHT = SUM(P1.RELEVANCY_WEIGHT) FROM PRODUCT_KEYWORD P1, PRODUCT_CATEGORY_MEMBER PCM
        // WHERE (P1.KEYWORD LIKE 'TI%' OR P1.KEYWORD LIKE 'HOUS%' OR P1.KEYWORD = '1003027') AND P1.PRODUCT_ID=PCM.PRODUCT_ID AND PCM.PRODUCT_CATEGORY_ID='foo' AND (PCM.THRU_DATE IS NULL OR PCM.THRU_DATE > ?) GROUP BY P1.PRODUCT_ID ORDER BY CAT_SEQ_NUM, TOTAL_WEIGHT DESC

        StringBuffer from = new StringBuffer(" FROM ");
        StringBuffer join = new StringBuffer(" WHERE ");
        StringBuffer where = new StringBuffer(" (");
        StringBuffer selectWeightTotal = new StringBuffer();
        StringBuffer groupBy = new StringBuffer();

        if (isAnd) {
            selectWeightTotal.append(", (P1.RELEVANCY_WEIGHT");
            int i = 1;

            while (keywordIter.hasNext()) {
                String keyword = (String) keywordIter.next();
                String comparator = "=";

                if (keyword.indexOf('%') >= 0 || keyword.indexOf('_') >= 0) {
                    comparator = " LIKE ";
                }
                params.add(keyword);
                if (i == 1) {
                    from.append("PRODUCT_KEYWORD P");
                    from.append(i);

                    where.append(" P");
                    where.append(i);
                    where.append(".KEYWORD");
                    where.append(comparator);
                    where.append("? ");
                } else {
                    from.append(", PRODUCT_KEYWORD P");
                    from.append(i);
                    from.append(" ");

                    selectWeightTotal.append(" + P");
                    selectWeightTotal.append(i);
                    selectWeightTotal.append(".RELEVANCY_WEIGHT");

                    join.append("P");
                    join.append(i - 1);
                    join.append(".PRODUCT_ID=P");
                    join.append(i);
                    join.append(".PRODUCT_ID AND ");

                    where.append("AND P");
                    where.append(i);
                    where.append(".KEYWORD");
                    where.append(comparator);
                    where.append("? ");
                }
                i++;
            }
            selectWeightTotal.append(") AS TOTAL_WEIGHT");
            where.append(") ");
        } else {
            selectWeightTotal.append(", SUM(P1.RELEVANCY_WEIGHT) AS TOTAL_WEIGHT");
            from.append("PRODUCT_KEYWORD P1");
            groupBy.append(" GROUP BY P1.PRODUCT_ID ");
            int i = 1;

            while (keywordIter.hasNext()) {
                String keyword = (String) keywordIter.next();
                String comparator = "=";

                if (keyword.indexOf('%') >= 0 || keyword.indexOf('_') >= 0) {
                    comparator = " LIKE ";
                }
                params.add(keyword);
                if (i == 1) {
                    where.append(" P1.KEYWORD");
                    where.append(comparator);
                    where.append("? ");
                } else {
                    where.append("OR P1.KEYWORD");
                    where.append(comparator);
                    where.append("? ");
                }
                i++;
            }
            where.append(") ");
        }

        if (useCategory) {
            from.append(", PRODUCT_CATEGORY_MEMBER PCM");
            where.append(" AND P1.PRODUCT_ID=PCM.PRODUCT_ID AND PCM.PRODUCT_CATEGORY_ID=? AND (PCM.THRU_DATE IS NULL OR PCM.THRU_DATE > ?)");
        }

        StringBuffer select = null;

        if (useCategory) {
            if (isAnd) {
                select = new StringBuffer("SELECT DISTINCT P1.PRODUCT_ID, PCM.SEQUENCE_NUM AS CAT_SEQ_NUM");
            } else {
                select = new StringBuffer("SELECT DISTINCT P1.PRODUCT_ID, MIN(PCM.SEQUENCE_NUM) AS CAT_SEQ_NUM");
            }
        } else {
            select = new StringBuffer("SELECT DISTINCT P1.PRODUCT_ID");
        }
        sql.append(select.toString());
        sql.append(selectWeightTotal.toString());
        sql.append(from.toString());
        sql.append(join.toString());
        sql.append(where.toString());
        sql.append(groupBy.toString());
        // for order by: do by SEQUENCE_NUM first then by RELEVANCY_WEIGHT
        // this basicly allows a default ordering with the RELEVANCY_WEIGHT and a manual override with SEQUENCE_NUM
        sql.append(" ORDER BY ");
        if (useCategory) {
            sql.append("CAT_SEQ_NUM, ");
        }
        sql.append("TOTAL_WEIGHT DESC");

        if (Debug.verboseOn()) Debug.logVerbose("[KeywordSearch] sql=" + sql.toString(), module);
        return sql.toString();
    }
     */

    public static void induceKeywords(GenericValue product) throws GenericEntityException {
        if (product == null) return;
        KeywordIndex.indexKeywords(product, false);
    }
    
    public static void induceKeywords(GenericValue product, boolean doAll) throws GenericEntityException {
        if (product == null) return;
        KeywordIndex.indexKeywords(product, doAll);
    }
}