Skip to content
Snippets Groups Projects
searchRefactor.qfqr 26.7 KiB
Newer Older
#
# Search Page
#
# a) List of forms: {{form:S}}='', {{formIdHistory:S}}=''
# b) Edit Form: {{form:S}} - Open form {{form:S}} with record {{r:S}} - typically the FormEditor
#
# {{form:S}}

# Search value in FormElement, Form, tt_content and pages
dbIndex={{indexQfq:Y}}
# Check if enableSwitch is given from QFQ. Only created if file is given over variable.
    sql = SELECT 'p:{{pageSlug:T}}&file=_formEditor|G:glyphicon-arrow-right|s|b|t:FormEditor' AS '_link|switchButton|_hide'
            FROM (SELECT '') AS fake
            WHERE '{{enableSwitch:XE}}' = 'yes'
}

# Check for first page load, define defaults and tooltip
    sql = SELECT IF('{{firstPageLoad:CE:alnumx}}' = '', 'yes', 'no') AS _firstPageLoad
                 , IF('{{file:SE}}' != '', '{{file:S::w}}', '{{file:UE}}') AS '_=file'
                 , 'Search in following columns:\n\nFormElement:\tlabel, note, value, sql1, parameter, modeSql\nForm:\t\ttitle, forwardPage, parameter, tableName\nT3 tt_content:\tbodytext, header, subheader\nT3 pages:\ttitle, subtitle, alias' AS _tooltipTable
    sql = SELECT REPLACE(REPLACE(TRIM('{{search:CE:allbut}}'), '"', '"'), "'", ''') AS _displaySearch
                , TRIM('{{search:CE:allbut}}') AS _search
                , TRIM('{{excludedContent:CE:allbut}}') AS _excludedContent
                , IF('{{checkExactMatch:CE:allbut}}' = 'yes', 'checked', '') AS _checkExactMatchState
                , IF('{{checkLimit:CE:allbut}}' = 'yes', 'checked', '') AS _checkLimitState
                , IF('{{checkLimit:CE:allbut}}' = 'yes', 'LIMIT 100', '') AS _queryLimit
                , IF('{{checkFormElement:CE:allbut}}' = 'yes' OR '{{firstPageLoad:RE:alnumx}}' = 'yes', 'checked', '') AS _checkFormElementState
                , IF('{{checkForm:CE:allbut}}' = 'yes' OR '{{firstPageLoad:RE:alnumx}}' = 'yes', 'checked', '') AS _checkFormState
                , IF('{{checkTtContent:CE:allbut}}' = 'yes' OR '{{firstPageLoad:RE:alnumx}}' = 'yes', 'checked', '') AS _checkTtContentState
                , IF('{{checkPages:CE:allbut}}' = 'yes' OR '{{firstPageLoad:RE:alnumx}}' = 'yes', 'checked', '') AS _checkPagesState

    # Creates a small form that redirects back to this page
        head =  <div style="clear:both;"></div><div class="panel panel-default" style="width: min-content;min-width: fit-content;"><div class="panel-heading"><h2 style="margin:0;">SearchRefactor &emsp;<span class="glyphicon glyphicon-info-sign" title='{{tooltipTable:RE::-}}'></span><span class="pull-right" style="margin-top:-4px;">{{&switchButton:RE::-}}</span></h2></div><div style="clear:both;"></div><div class="panel-body">
                    <form action='index.php' class='form-inline'>
                        <div class='form-group'>
                            <table>
                                <tr>
                                    <td>
                                        <label for='search'>Search for:</label>
                                    </td>
                                    <td>
                                        <label for='excludedContent'>Ignore:</label>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <input class='form-control' type='text' id='search' name='search' placeholder='String' value="{{displaySearch:RE:all}}" autofocus='autofocus' style='width:90%;'>
                                    </td>
                                    <td>
                                        <input class='form-control' type='text' id='excludedContent' name='excludedContent' placeholder='Optional' value='{{excludedContent:RE:alnumx}}' style='width:80%;'>
                                        <input class='btn btn-default form-control' type='submit' value='Search' style='width:15%;'>
                                    </td>
                                </tr>
                            </table>
                            <br>
                            <label for="table">Table:</label>&emsp;&emsp;&nbsp;
                            <input name='checkFormElement' type='checkbox' id='checkFormElement' value='yes' {{checkFormElementState:RE:all}}><label for="checkFormElement"> FormElement</label>&emsp;
                            <input name='checkForm' type='checkbox' id='checkForm' value='yes' {{checkFormState:RE:all}}><label for="checkForm"> Form</label>&emsp;
                            <input name='checkTtContent' type='checkbox' id='checkTtContent' value='yes' {{checkTtContentState:RE:all}}><label for="checkTtContent"> tt_content (Report)</label>&emsp;
                            <input name='checkPages' type='checkbox' id='checkPages' value='yes' {{checkPagesState:RE:all}}><label for="checkPages"> pages</label><br>
                            <label for="table">Options:</label>&emsp;
                            <input name='checkExactMatch' type='checkbox' id='checkExactMatch' value='yes' {{checkExactMatchState:RE:all}}><label for="checkExactMatch"> Exact match</label>&emsp;&ensp;&nbsp;
                            <input name='checkLimit' type='checkbox' id='checkLimit' value='yes' {{checkLimitState:RE:all}}><label for="checkLimit"> Limit 100 records</label><br><br>
                            <input name='id' value='{{pageId:T}}' type='hidden'>
                            <input name='firstPageLoad' value='loaded' type='hidden'>
                        </div>
                    </form>
                </div>
                </div>
        sql = SELECT IF('{{checkExactMatchState:RE:alnumx}}' = 'checked', '{{search:RE:allbut}}', CONCAT('%', '{{search:RE:allbut}}', '%')) AS _finalSearch
    }

    # Check for empty search and first page load
        sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{firstPageLoad:RE:alnumx}}' = 'no' AND '{{search:RE:allbut}}' != ''

        # Search from checked tables
            sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{checkFormElementState:RE:alnumx}}' = 'checked'

            # Table Header
                sql = SELECT '<th class="filter-false sorter-false"></th>' AS _noWrap
                           , '<th class="filter-false sorter-false"></th>' AS _noWrap
                           , 'ID', 'FormId', 'Name', 'Enabled', 'Class', 'Found'
                        FROM FormElement AS fe

                        WHERE '{{finalSearch:RE:all}}' <> ''
                          AND (fe.label LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.label NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.note LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.note NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.value LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.value NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.sql1 LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.sql1 NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.parameter LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.modeSql LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.modeSql NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))
                head = <div class="panel panel-info" style="width: min-content;min-width: fit-content;"><div class="panel-heading"><h2 style="margin:0;">FormElement</h2><span class="result-box-counter" data-box-id="fe-box-counter"></span></div><div class="panel-body"><table class="table table-hover qfq-table-50 tablesorter tablesorter-filter tablesorter-pager" id="{{pageSlug:TE}}-fe"><thead><tr>
                fbeg = <th>
                fend = </th>
            }


            # SQL statement will find and list all the relevant persons (only executed if a search term is entered)
                sql = SELECT CONCAT('p:{{pageSlug:T}}&form=formElement&r=', fe.id,'|s') AS '_pagee'
                           , CONCAT('p:{{pageSlug:T}}&form=form&r=', fe.formId,'|s|b|t:Edit Form') AS '_link'
                           , fe.id
                           , fe.formId
                           , fe.name
                           , fe.enabled
                           , fe.class
                           , CONCAT(
                               IF(fe.label LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.label NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>Label:</strong><br>', QMARK(fe.label, '{{search:RE:allbut}}')), '')
                               , IF(fe.note LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.note NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>Note:</strong><br>', QMARK(fe.note, '{{search:RE:allbut}}')), '')
                               , IF(fe.value LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.value NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>Value:</strong><br>', QMARK(fe.value, '{{search:RE:allbut}}')), '')
                               , IF(fe.sql1 LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.sql1 NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>SQL1:</strong><br>', QMARK(fe.sql1, '{{search:RE:allbut}}')), '')
                               , IF(fe.parameter LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>Parameter:</strong><br>', QMARK(fe.parameter, '{{search:RE:allbut}}')), '')
                               , IF(fe.modeSql LIKE '{{finalSearch:RE:all}}'
                                    AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.modeSql NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                                    , CONCAT('<strong>ModeSql:</strong><br>', QMARK(fe.modeSql, '{{search:RE:allbut}}')), ''))
                        FROM FormElement AS fe

                        WHERE '{{finalSearch:RE:all}}' <> ''
                          AND (fe.label LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.label NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.note LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.note NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.value LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.value NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.sql1 LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.sql1 NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.parameter LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                          OR fe.modeSql LIKE '{{finalSearch:RE:all}}'
                          AND IF('{{excludedContent:RE:alnumx}}' <> '', fe.modeSql NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))

                        {{queryLimit:RE:alnumx}}



                head = <tbody>
                tail = </tbody></table></div></div><input id='fe-box-counter' value='{{20.50.60.80.line.count}}' type='hidden'>
                rbeg = <tr>
                rend = </tr>
                fbeg = <td>
                fend = </td>
            }
        }

        100 {
            sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{checkFormState:RE:alnumx}}' = 'checked'

            # Table Header
            110 {
                sql = SELECT '<th class="filter-false sorter-false"></th>' AS _noWrap
                   , 'ID', 'Name', 'TableName', 'Found'
                FROM Form AS f

                WHERE '{{finalSearch:RE:all}}' <> ''
                  AND (f.title LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.forwardPage LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.forwardPage NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.tableName LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.tableName NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.parameter LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))
                head = <div class="panel panel-info" style="width: min-content;min-width: fit-content;"><div class="panel-heading"><h2 style="margin:0;">Form</h2><span class="result-box-counter" data-box-id="f-box-counter"></span></div><div class="panel-body"><table class="table table-hover qfq-table-50 tablesorter tablesorter-filter tablesorter-pager" id="{{pageSlug:TE}}-form"><thead><tr>
                fbeg = <th>
                fend = </th>
            }

            # SQL statement will find and list all the relevant persons (only executed if a search term is entered)
            120 {
                sql = SELECT CONCAT('p:{{pageSlug:T}}&form=form&r=', f.id,'|s') AS '_pagee'
                   , CONCAT(IF(f.title LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', f.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Title:</strong><br>', QMARK(f.title, '{{search:RE:allbut}}')),'')
                            , IF(f.forwardPage LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', f.forwardPage NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Forward Page:</strong><br>', QMARK(f.forwardPage, '{{search:RE:allbut}}')),'')
                            , IF(f.parameter LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', f.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Parameter:</strong><br>', QMARK(f.parameter, '{{search:RE:allbut}}')),'')
                            , IF(f.tableName LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', f.tableName NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>TableName:</strong><br>', QMARK(f.tableName, '{{search:RE:allbut}}')),''))
                FROM Form AS f

                WHERE '{{finalSearch:RE:all}}' <> ''
                  AND (f.title LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.forwardPage LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.forwardPage NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.tableName LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.tableName NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR f.parameter LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' <> '', f.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))

                {{queryLimit:RE:alnumx}}


                head = <tbody>
                tail = </tbody></table></div></div><input id='f-box-counter' value='{{20.50.100.120.line.count}}' type='hidden'>
                rbeg = <tr>
                rend = </tr>
                fbeg = <td>
                fend = </td>
            }
        }

        # Search from used table
        130 {
            sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{checkTtContentState:RE:alnumx}}' = 'checked'

            # Table Header
            140 {
                sql = SELECT '<th class="filter-false sorter-false"></th>' AS _noWrap
                             ,'ID', 'PageId', 'Header', 'Subheader', 'Disabled', 'Found'

                FROM {{dbNameT3:Y}}.tt_content AS r

                WHERE '{{finalSearch:RE:all}}' != ''
                  AND (r.bodytext LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.bodytext NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR r.header LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.header NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR r.subheader LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.subheader NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))

                LIMIT 1
                head = <div class="panel panel-info" style="width: min-content;min-width: fit-content;"><div class="panel-heading"><h2 style="margin:0;">T3 tt_content (Report)</h2><span class="result-box-counter" data-box-id="r-box-counter"></span></div><div class="panel-body"><table class="table table-hover qfq-table-50 tablesorter tablesorter-filter tablesorter-pager" id="{{pageSlug:TE}}-report"><thead><tr>
                fbeg = <th>
                fend = </th>
            }

            # SQL statement will find and list all the relevant persons (only executed if a search term is entered)
            150 {
                sql = SELECT CONCAT('i:uid=', r.uid, '|b:btn btn-default') AS _link
                   , r.pid
                   , r.header
                   , r.subheader
                   , IF(r.hidden = 0, 'No', 'Yes')
                   , CONCAT(IF(r.bodytext LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', r.bodytext NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Bodytext:</strong><br>', QMARK(r.bodytext, '{{search:RE:allbut}}')),'')
                            , IF(r.header LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', r.header NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Header:</strong><br>', QMARK(r.header, '{{search:RE:allbut}}')),'')
                            , IF(r.subheader LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', r.subheader NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Subheader:</strong><br>', QMARK(r.subheader, '{{search:RE:allbut}}')),''))

                FROM {{dbNameT3:Y}}.tt_content AS r

                WHERE '{{finalSearch:RE:all}}' != ''
                  AND (r.bodytext LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.bodytext NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR r.header LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.header NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR r.subheader LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', r.subheader NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))
                  AND r.deleted = 0

                {{queryLimit:RE:alnumx}}


                head = <tbody>
                tail = </tbody></table></div></div><input id='r-box-counter' value='{{20.50.130.150.line.count}}' type='hidden'>
                rbeg = <tr>
                rend = </tr>
                fbeg = <td>
                fend = </td>
            }
        }

        # Search from used table
        160 {
            sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{checkPagesState:RE:alnumx}}' = 'checked'

            # Table Header
            170 {
                sql = SELECT 'ID', 'Parent<br>PageId', 'Title', 'Subtitle', 'Alias/Slug', 'Disabled', 'Found', 'TT-Contents'
                FROM {{dbNameT3:Y}}.pages AS p

                WHERE '{{finalSearch:RE:all}}' != ''
                  AND (p.title LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR p.subtitle LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))
                head = <div class="panel panel-info" style="width: min-content;min-width: fit-content;"><div class="panel-heading"><h2 style="margin:0;">T3 pages</h2><span class="result-box-counter" data-box-id="p-box-counter"></span></div><div class="panel-body"><table class="table table-hover qfq-table-50 tablesorter tablesorter-filter tablesorter-pager" id="{{pageSlug:TE}}-report"><thead><tr>
                fbeg = <th>
                fend = </th>
            }

            # SQL statement will find and list all the relevant pages (only executed if a search term is entered)
            180 {
                sql = SELECT p.uid
                   , p.pid
                   , p.title
                   , p.subtitle
                   , p.{{columnNameAliasSlug:XE}}
                   , CONCAT(IF(p.title LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Title:</strong><br>', QMARK(p.title, '{{search:RE:allbut}}')),'')
                            , IF(p.subtitle LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Subtitle:</strong><br>', QMARK(p.subtitle, '{{search:RE:allbut}}')),'')
                            , IF(p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all}}'
                                AND IF('{{excludedContent:RE:alnumx}}' <> '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                            , CONCAT('<strong>Alias:</strong><br>', QMARK(p.{{columnNameAliasSlug:XE}}, '{{search:RE:allbut}}')),''))

                FROM {{dbNameT3:Y}}.pages AS p

                WHERE '{{finalSearch:RE:all}}' != ''
                  AND (p.title LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR p.subtitle LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1)
                  OR p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all}}'
                  AND IF('{{excludedContent:RE:alnumx}}' != '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:alnumx}}', '%'), 1=1))
                head = <tbody>
                tail = </tbody></table><input id='p-box-counter' value='{{20.50.160.180.line.count}}' type='hidden'>

                # Iterate over all tt_contents from each page and build edit button
                190 {
                    sql = SELECT CONCAT('i:uid=', r.uid, '|t:', r.uid, '|b:btn btn-default') AS _link
                                 , ' '
                            FROM {{dbNameT3:Y}}.pages AS p, {{dbNameT3:Y}}.tt_content AS r
                            WHERE p.uid = {{pageId:R0}}
                              AND p.uid = r.pid
                              AND r.deleted = 0
                    shead = <td>
                    stail = </td>
                }
        sql = SELECT '<script> $(document).ready(function() {
                                    var resultBoxes = document.getElementsByClassName("result-box-counter");
                                    Array.from(resultBoxes).forEach(setCounter);
                                    function setCounter(element) {
                                        if (element.dataset !== undefined) {
                                            var value = document.getElementById(element.dataset.boxId).value;
                                            if (value >= 100) {
                                                element.innerText = "100 or more rows reached!";
                                            } } } }); </script>'
    }
}