A Search for a Category

[bhsmither]

Feature Request (in admin):

Similar to the feature of searching language packs and displaying the results having that word, request that:

A means to supply a category name (or part of a name), to have the database tables CubeCart_category, CubeCart_category_language, and CubeCart_seo_urls searched for the term(s), displaying the curated list as (at least) links to the edit page, and the directory path of its parentage for each result.

Probably not so helpful having a small number of categories. But having dozens of categories several levels deep, the drill-down might be onerous with many backtracks from dead-ends trying to find it.

[bhsmither]

The searched locations to include the cat_name and cat_desc - possibly using a fulltext index.

[bhsmither]

This is what I have so far:

categories.index.php
Find:
    {else}
    <h3>{$LANG.settings.title_category}</h3>
    {/if}

Add after:
{if $SHOW_SEARCH}
         <fieldset>
            <legend>Search category names and descriptions that contains...</legend>
            <div>
               <input type="text" class="textbox" name="cat_search_phrase" value="{$SEARCH_PHRASE}" />
               <input type="submit" value="{$LANG.common.go}" name="go" class="update tiny">
 {if !empty($SEARCH_PHRASE)}
               <a href="?_g=categories&amp;node=index">{$LANG.common.reset}</a>
 {/if}
            </div>
         </fieldset>
 {if isset($SEARCH_HITS) && count($SEARCH_HITS)>0}
         <p>Found these categories...</p>
         <table class="collapsed">
            <tr>
               <th class="thead text-left" nowrap="nowrap">Category: ID</th>
               <th class="thead text-left">Location</th>
               <th class="thead text-left">Context</th>
            </tr>
  {foreach $SEARCH_HITS as $hit}
            <tr>
               <td nowrap="nowrap">&nbsp;<a href="index.php?_a=category&cat_id={$hit.id}" title="View" target="_blank"><i class="fa fa-search" title="View"></i></a><a href="?_g=categories&amp;node=index&amp;action=edit&amp;cat_id={$hit.id} title="Edit" target="_blank"><i class="fa fa-pencil-square-o" title="Edit"></i></a>{$hit.name}:&nbsp;{$hit.id}</td>
               <td width="75px">{$hit.location}</td>
               <td>{$hit.context}</td>
            </tr>
  {/foreach}
         </table>
 {elseif isset($SEARCH_HITS)}
         <p>{sprintf($LANG.translate.no_phrases_found,$SEARCH_PHRASE)}</p>
 {/if}
{/if}
<hr>

categories.index.inc.php
Line 252, from:
    httpredir(currentPage());
To:
if (!isset($_POST['go'])) httpredir(currentPage());

Line 426, find:
    $GLOBALS['smarty']->assign('PLUGIN_TABS', ($smarty_data['plugin_tabs'] ?? false));
    $GLOBALS['main']->addTabControl($lang['settings']['title_category'], 'categories');
    $GLOBALS['main']->addTabControl($lang['settings']['title_category_add'], null, currentPage(null, array('action' => 'add')));
Add after:
    $GLOBALS['smarty']->assign('SHOW_SEARCH', true);
    if (isset($GLOBALS['RAW']['POST']['cat_search_phrase']) && !empty($GLOBALS['RAW']['POST']['cat_search_phrase'])) { // We have a category to search for.
        $search_hits = array();
        $search_targets = array(
          'CubeCart_category'=>array(
            'searchCols'=>array('cat_name','cat_desc'),
            'whereCols'=>array(),
            'returnCols'=>'cat_id'
          ),
          'CubeCart_category_language'=>array(
            'searchCols'=>array('cat_name','cat_desc'),
            'whereCols'=>array(),
            'returnCols'=>'cat_id'
          ),
          'CubeCart_seo_urls'=>array(
            'searchCols'=>array('path'),
            'whereCols'=>array('type'=>'cat'),
            'returnCols'=>'item_id'
          )
        );
        $search_target_ident_cols = array('cat_id','item_id');
        foreach($search_targets as $searchTable => $arrSearchParts) {
            $search_hits[$searchTable] = array();
            foreach($arrSearchParts['searchCols'] as $searchCol) {
                if( ($searchedCol = $GLOBALS['db']->select($searchTable // table
                     , $arrSearchParts['returnCols'].','.$searchCol // cols
                     , [...array($searchCol => '~'.$GLOBALS['RAW']['POST']['cat_search_phrase']), ...$arrSearchParts['whereCols']] // where
                       )
                    ) !== false )
                {
                    $search_hits[$searchTable] = array_merge($search_hits[$searchTable], $searchedCol);
                }
            }
        }
        if(!empty($search_hits)) {
            $search_regex = '(?:^|(\S+|\s+){1,11})('.$GLOBALS['RAW']['POST']['cat_search_phrase'].')(?:(\s+|\S+){1,11}|$)';
            $search_regex_capture_group_number = 3;
            $mark = $phrase_group_name = $phrase_group_title = array();
            foreach($search_hits as $searchedTable => $searchedTableRecords) {
                foreach($searchedTableRecords as $arrsearchedRowKey => $arrsearchedRow) {
                    foreach($arrsearchedRow as $arrsearchedColKey => $arrsearchedColVal) {
                        $arrsearchedColVal = html_entity_decode(strip_tags(str_replace("#","",$arrsearchedColVal)));
                        if(strlen($arrsearchedColVal) > 500) { // The context is longer than 500 characters. So chop it.
                            $arrsearchedColValPos = stripos($arrsearchedColVal,$GLOBALS['RAW']['POST']['cat_search_phrase']);
                            $arrsearchedColVal = substr($arrsearchedColVal, ($arrsearchedColValPos >= 100)? $arrsearchedColValPos - 100: 0, 200);
                        }
                        preg_match('#'.$search_regex.'#i', $arrsearchedColVal, $matches);
                        if ($matches && !in_array($arrsearchedColKey, $search_target_ident_cols)) {
                            $found_context[$arrsearchedColKey] = preg_replace("#[\s]+#",' ',$matches[0]);
                            $mark[$searchedTable][$arrsearchedRowKey][$arrsearchedColKey] = str_ireplace($matches[2],"<mark>".$matches[2]."</mark>",$found_context[$arrsearchedColKey]);
                            $found_context = array();
                        } else {
                           $mark[$searchedTable][$arrsearchedRowKey][$arrsearchedColKey] = $arrsearchedColVal;
                        }
                    }
                }
            }
            $i = 0;
            foreach($mark as $table => $hits) {
                foreach($hits as $row) {
                    $searched_mark[$i]['table'] = $table;
                    foreach($row as $k => $v) {
                        if(in_array($k,$search_target_ident_cols)) {
                            $searched_mark[$i]['id'] = $v;
                            $searched_mark[$i]['name'] = $GLOBALS['db']->select("CubeCart_category","cat_name","cat_id=$v")[0]["cat_name"];
                        } else {
                            $searched_mark[$i]['location'] = $k;
                            $searched_mark[$i]['context'] = $v;
                        }
                    }
                    ++$i;
                }
            }

            $search_hits = $searched_mark;
            unset($mark);
        }
        $GLOBALS['smarty']->assign("SEARCH_PHRASE", $GLOBALS['RAW']['POST']['cat_search_phrase']);
        $GLOBALS['smarty']->assign("SEARCH_HITS", isset($search_hits) ? $search_hits : array());
    }