#
# 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}}

form={{form:SE}}

# Check if enableSwitch is given from QFQ. Only created if file is given over variable.
10 {
    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
20 {
    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:\tid, label, note, value, sql1, parameter, modeSql\nForm:\t\tid, title, forwardPage, parameter, tableName\nT3 tt_content:\tuid, bodytext, header, subheader\nT3 pages:\tuid, title, subtitle, alias' AS _tooltipTable
}

30 {
    # GET parameter: {{search}}
    sql = SELECT REPLACE(REPLACE(TRIM('{{search:CE:all:Sm}}'), '"', '&quot;'), "'", '&#39;') AS _displaySearch
                , REPLACE(REPLACE(REPLACE(TRIM('{{search:CE:all:Sm}}'), '%', '\%'), '_', '\_'), '/', '\/') AS _prepareFinalSearch
                , TRIM('{{search:CE:all:Sm}}') AS _search
                , REPLACE(REPLACE(REPLACE(TRIM('{{excludedContent:CE:all:Sm}}'), '%', '\%'), '_', '\_'), '/', '\/') 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
                , IF('{{checkActive:CE:allbut}}' = 'yes' OR '{{firstPageLoad:RE:alnumx}}' = 'yes', 'checked', '') AS _checkActive

    # Creates a small form that redirects back to this page
    40 {
        sql = SELECT ''
        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="search-form-group">
                          <label for="search" class="search-label">Search for:</label>
                          <input type="text" id="search" name="search" placeholder="String" value="{{displaySearch:RE:all:S}}" autofocus style="flex: 1; padding: 8px; border: 1px solid #ccc; border-radius: 5px;">
                      </div>
                      <div class="search-form-group">
                          <label for="excludedContent" class="search-label">Ignore:</label>
                          <input type="text" id="excludedContent" name="excludedContent" placeholder="Optional" value="{{excludedContent:RE:all:Sm}}" style="flex: 1; padding: 8px; border: 1px solid #ccc; border-radius: 5px;">
                      </div>
                      <div class="search-form-group search-checkbox-group">
                          <span class="search-label">Table:</span>
                          <label for="checkFormElement" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkFormElement" name="checkFormElement" value="yes" {{checkFormElementState:RE:all}}> FormElement</label>
                          <label for="checkForm" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkForm" name="checkForm" value="yes" {{checkFormState:RE:all}}> Form</label>
                          <label for="checkTtContent" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkTtContent" name="checkTtContent" value="yes" {{checkTtContentState:RE:all}}> tt_content (Report)</label>
                          <label for="checkPages" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkPages" name="checkPages" value="yes" {{checkPagesState:RE:all}}> Pages</label>
                          <label for="checkActive" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkActive" name="checkActive" value="yes" {{checkActive:RE:all}}> Only Active</label>
                      </div>
                      <div class="search-form-group search-checkbox-group">
                          <span class="search-label">Options:</span>
                          <label for="checkExactMatch" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkExactMatch" name="checkExactMatch" value="yes" {{checkExactMatchState:RE:all}}> Exact Match</label>
                          <label for="checkLimit" style="margin-right: 15px; font-weight: normal;"><input type="checkbox" id="checkLimit" name="checkLimit" value="yes" {{checkLimitState:RE:all}}> Limit 100 records</label>
                      </div>
                      <div class="search-btn-container" style="text-align: center; margin-top: 20px;">
                          <input type="submit" value="Search" class="search-submit-btn">
                      </div>
                      <input type="hidden" name="id" value="{{pageId:T}}">
                      <input type="hidden" name="firstPageLoad" value="loaded">
                  </form>
                </div>
                </div>
    }


    # Prepare value with or without wildcard and escape underscore character
    50 {
        sql = SELECT IF('{{checkExactMatchState:RE:alnumx}}' = 'checked', '{{prepareFinalSearch:RE:all:Sm}}', CONCAT('%', '{{prepareFinalSearch:RE:all:Sm}}', '%')) AS _finalSearch
    }

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

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

            # Table Header
            80 {
                sql = SELECT '<th class="sorter-false">FE</th>' AS _noWrap
                           , '<th class="sorter-false">Form</th>' AS _noWrap
                           , 'Type', 'Enabled', 'Class', 'Found'
                        FROM FormElement AS fe

                        WHERE '{{finalSearch:RE:all:Sm}}' <> ''
                          AND (fe.id LIKE '{{finalSearch:RE:all:Sm}}'
                          OR fe.name LIKE '{{finalSearch:RE:all:Sm}}'
                          OR fe.type LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.id NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.label LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.label NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.note LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.note NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.value LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.value NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.sql1 LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.sql1 NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.parameter LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.modeSql LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.modeSql NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1))
                          AND IF('{{checkActive:RE:all:Sm}}' <> '', 1=1, fe.enabled = 'yes')

                        LIMIT 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>
                tail = </tr></thead>
                fbeg = <th>
                fend = </th>
            }


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

                        WHERE '{{finalSearch:RE:all:Sm}}' <> ''
                          AND (fe.id LIKE '{{finalSearch:RE:all:Sm}}'
                          OR fe.name LIKE '{{finalSearch:RE:all:Sm}}'
                          OR fe.type LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.id NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.label LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.label NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.note LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.note NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.value LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.value NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.sql1 LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.sql1 NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.parameter LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                          OR fe.modeSql LIKE '{{finalSearch:RE:all:Sm}}'
                          AND IF('{{excludedContent:RE:all:Sm}}' <> '', fe.modeSql NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1))
                          AND IF('{{checkActive:RE:all:Sm}}' <> '', 1=1, fe.enabled = 'yes')
                          AND f.id = fe.formId

                        {{queryLimit:RE:alnumx}}



                head = <tbody>
                tail = </tbody></table></div></div><input id='fe-box-counter' value='{{30.60.70.90.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="sorter-false">Form</th>' AS _noWrap
                , 'TableName', 'Found'
                FROM Form AS f

                WHERE '{{finalSearch:RE:all:Sm}}' <> ''
                  AND (f.id LIKE '{{finalSearch:RE:all:Sm}}'
                  OR f.name LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.id NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR f.title LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.title NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR f.forwardPage LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.forwardPage NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR f.tableName LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.tableName NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR f.parameter LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 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;">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>
                tail = </tr></thead>
                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|t:', f.id, '/', f.name) AS '_pagee'
                   , f.tableName
                   , CONCAT(IF(f.id LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.id NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>ID:</strong><br>', QMARK(f.id, '{{search:RE:all:Sm}}')),'')
                            , IF(f.title LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.title NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Title:</strong><br>', QMARK(f.title, '{{search:RE:all:Sm}}')),'')
                            , IF(f.forwardPage LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.forwardPage NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Forward Page:</strong><br>', QMARK(f.forwardPage, '{{search:RE:all:Sm}}')),'')
                            , IF(f.parameter LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.parameter NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Parameter:</strong><br>', QMARK(f.parameter, '{{search:RE:all:Sm}}')),'')
                            , IF(f.tableName LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', f.tableName NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>TableName:</strong><br>', QMARK(f.tableName, '{{search:RE:all:Sm}}')),''))
                FROM Form AS f

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

                {{queryLimit:RE:alnumx}}


                head = <tbody>
                tail = </tbody></table></div></div><input id='f-box-counter' value='{{30.60.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="sorter-false">Report</th>' AS _noWrap
                             , 'PageSlug', 'PageTitle', 'Header', 'Subheader', 'Disabled', 'Found'

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

                WHERE '{{finalSearch:RE:all:Sm}}' != ''
                  AND (r.uid LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', r.uid NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR r.bodytext LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', r.bodytext NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR r.header LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', r.header NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR r.subheader LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', r.subheader NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1))
                  AND r.deleted = 0
                  AND IF('{{checkActive:RE:all:Sm}}' != '', r.hidden = 0 , 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>
                tail = </tr></thead>
                fbeg = <th>
                fend = </th>
            }

            # SQL statement will find and list all the relevant tt_content (only executed if a search term is entered)
            150 {
                sql = SELECT IF(r.hidden = 0, '<tr>', '<tr class="text-muted">') AS _noWrap
                   , CONCAT('i:uid=', r.uid, '|b:btn btn-default edit-report-float-left|t:', r.uid) AS _link
                   , p.slug
                   , p.title
                   , r.header
                   , r.subheader
                   , IF(r.hidden = 0, 'No', 'Yes')
                   , CONCAT(IF(r.uid LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', r.uid NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Uid:</strong><br>', QMARK(r.uid, '{{search:RE:all:Sm}}')),'')
                            , IF(r.bodytext LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', r.bodytext NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Bodytext:</strong><br>', QMARK(r.bodytext, '{{search:RE:all:Sm}}')),'')
                            , IF(r.header LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', r.header NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Header:</strong><br>', QMARK(r.header, '{{search:RE:all:Sm}}')),'')
                            , IF(r.subheader LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', r.subheader NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Subheader:</strong><br>', QMARK(r.subheader, '{{search:RE:all:Sm}}')),''))

                FROM {{dbNameT3:Y}}.tt_content AS r, {{dbNameT3:Y}}.pages AS p

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

                {{queryLimit:RE:alnumx}}


                head = <tbody>
                tail = </tbody></table></div></div><input id='r-box-counter' value='{{30.60.130.150.line.count}}' type='hidden'>
                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:Sm}}' != ''
                  AND (p.uid LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.uid NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.title LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.subtitle LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1))
                  AND p.deleted = 0
                  AND IF('{{checkActive:RE:all:Sm}}' != '', p.hidden = 0, 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 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>
                tail = </tr></thead>
                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 IF(p.hidden = 0, '<tr>', '<tr class="text-muted">') AS _noWrap
                   , p.uid
                   , p.pid
                   , p.title
                   , p.subtitle
                   , p.{{columnNameAliasSlug:XE}}
                   , IF(p.hidden = 0, 'No', 'Yes')
                   , CONCAT(IF(p.uid LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', p.uid NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Uid:</strong><br>', QMARK(p.uid, '{{search:RE:all:Sm}}')),'')
                            , IF(p.title LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Title:</strong><br>', QMARK(p.title, '{{search:RE:all:Sm}}')),'')
                            , IF(p.subtitle LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Subtitle:</strong><br>', QMARK(p.subtitle, '{{search:RE:all:Sm}}')),'')
                            , IF(p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all:Sm}}'
                                AND IF('{{excludedContent:RE:all:Sm}}' <> '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                            , CONCAT('<strong>Alias:</strong><br>', QMARK(p.{{columnNameAliasSlug:XE}}, '{{search:RE:all:Sm}}')),''))
                   , p.uid AS _pageId

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

                WHERE '{{finalSearch:RE:all:Sm}}' != ''
                  AND (p.uid LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.uid NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.title LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.title NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.subtitle LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.subtitle NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1)
                  OR p.{{columnNameAliasSlug:XE}} LIKE '{{finalSearch:RE:all:Sm}}'
                  AND IF('{{excludedContent:RE:all:Sm}}' != '', p.{{columnNameAliasSlug:XE}} NOT LIKE CONCAT('%', '{{excludedContent:RE:all:Sm}}', '%'), 1=1))
                  AND p.deleted = 0
                  AND IF('{{checkActive:RE:all:Sm}}' != '', p.hidden = 0, 1=1)

                {{queryLimit:RE:alnumx}}


                head = <tbody>
                tail = </tbody></table><input id='p-box-counter' value='{{30.60.160.180.line.count}}' type='hidden'>
                renr = </tr>
                fbeg = <td>
                fend = </td>

                # 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-', IF(r.hidden = 0, 'default', 'warning|o:tt_content is disabled')) 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>
                }
            }
        }
    }

    # If no results are found, show a message
    200 {
        sql = SELECT '<h2 class="alert alert-info">Nothing found in tables.</h2>'
                FROM (SELECT '') AS fake
                WHERE ('{{30.60.70.80.line.total}}' + '{{30.60.100.110.line.total}}' + '{{30.60.130.140.line.total}}' + '{{30.60.160.170.line.total}}') = 0
                  AND '{{search:CE:all:Sm}}' != ''
    }

    210 {
        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>'
    }
}