<?php
$B = htmlspecialchars(rtrim((string) ($baseUrl ?? ''), '/'));
if (!function_exists('pp_where')) {
    function pp_where(string $path, string $lang = 'php'): string {
        $l = $lang === 'js' ? 'JS' : 'PHP';
        return '<div class="help-where"><span class="help-where-tag"><i class="bi bi-geo-alt-fill"></i> Where</span>'
             . '<span class="help-where-path">' . $path . '</span>'
             . '<span class="help-where-lang ' . htmlspecialchars($lang) . '">' . $l . '</span></div>';
    }
}
?>
<h1><i class="bi bi-braces-asterisk"></i> SQL Variables</h1>
<p class="lead">Make your queries <strong>dynamic</strong> without writing PHP glue. Drop a token like
<code>:userid</code> or <code>:session.region</code> into a WHERE clause and it's replaced at run time with a
<strong>safe, correctly-quoted</strong> value — the logged-in user, a session value, a request parameter, and more.</p>

<div class="help-callout help-callout-info">
    <i class="bi bi-shield-check"></i>
    <div><strong>Always safe.</strong> Values are escaped and quoted <em>for you</em>, by type — a string is quoted, a
        number goes in raw, a list becomes a comma-separated set for <code>IN (…)</code>. <strong>Never add quotes
        yourself</strong> around a variable. This is injection-safe even when the value came from the browser.</div>
</div>

<h2>The variables</h2>
<table class="help-table">
    <thead><tr><th>Variable</th><th>Resolves to</th><th>Example</th></tr></thead>
    <tbody>
        <tr><td><code>:userid</code></td><td>The logged-in user's id</td><td><code>owner_id = :userid</code></td></tr>
        <tr><td><code>:username</code></td><td>The logged-in user's login name</td><td><code>rep = :username</code></td></tr>
        <tr><td><code>:user.<i>field</i></code></td><td>Any column from the current user's row</td><td><code>dept = :user.department</code></td></tr>
        <tr><td><code>:session.<i>key</i></code></td><td>A value you stored in the session</td><td><code>region = :session.region</code></td></tr>
        <tr><td><code>:get.<i>key</i></code> · <code>:post.<i>key</i></code></td><td>A request parameter</td><td><code>status = :get.status</code></td></tr>
        <tr><td><code>:field.<i>name</i></code></td><td>Another field's value on the <b>same form</b> (Add/Edit) — cascading dropdowns</td><td><code>country_id = :field.country</code></td></tr>
        <tr><td><code>:master.<i>field</i></code></td><td>A field from the master record (master/detail)</td><td><code>order_id = :master.id</code></td></tr>
        <tr><td><code>:language</code></td><td>The current UI language</td><td><code>lang = :language</code></td></tr>
        <tr><td><code>:{<i>name with spaces</i>}</code></td><td>Braces for names with spaces/dots</td><td><code>:{master.Customer ID}</code></td></tr>
    </tbody>
</table>
<p>A missing value becomes an empty string (<code>''</code>) — it never breaks the query. Positional
<code>:1</code>, <code>:2</code> placeholders and PostgreSQL <code>::casts</code> are left alone.</p>
<p class="help-note"><strong>Also available:</strong> <code>:form.<i>name</i></code> (alias of <code>:field</code>),
<code>:request.<i>key</i></code> (either GET or POST), <code>:usergroup</code> (alias of <code>:userrole</code>), and
the full offset family up to <code>:yearsago.<i>N</i></code> / <code>:yearsfromnow.<i>N</i></code>.</p>

<h2>Dynamic date &amp; context variables</h2>
<p>These resolve at run time — perfect for rolling-window filters and audit columns, with no PHP.</p>
<table class="help-table">
    <thead><tr><th>Variable</th><th>Resolves to</th><th>Example</th></tr></thead>
    <tbody>
        <tr><td><code>:today</code> · <code>:now</code></td><td>Current date · date-time</td><td><code>due &lt;= :today</code></td></tr>
        <tr><td><code>:yesterday</code> · <code>:tomorrow</code></td><td>±1 day</td><td><code>created &gt;= :yesterday</code></td></tr>
        <tr><td><code>:startofweek</code> · <code>:endofweek</code></td><td>This week's Mon / Sun</td><td><code>c &gt;= :startofweek</code></td></tr>
        <tr><td><code>:startofmonth</code> · <code>:endofmonth</code></td><td>This month's first / last day</td><td><code>c BETWEEN :startofmonth AND :endofmonth</code></td></tr>
        <tr><td><code>:startofyear</code> · <code>:endofyear</code></td><td>This year's first / last day</td><td><code>c &gt;= :startofyear</code></td></tr>
        <tr><td><code>:daysago.<i>N</i></code> · <code>:daysfromnow.<i>N</i></code></td><td>A rolling window of N days</td><td><code>created &gt;= :daysago.30</code></td></tr>
        <tr><td><code>:hoursago.<i>N</i></code> · <code>:monthsago.<i>N</i></code></td><td>N hours / months back (also <code>…fromnow</code>)</td><td><code>ts &gt;= :hoursago.24</code></td></tr>
        <tr><td><code>:userrole</code></td><td>The current user's role/group</td><td><code>visibility = :userrole</code></td></tr>
        <tr><td><code>:ip</code></td><td>The visitor's IP address</td><td><code>last_ip = :ip</code></td></tr>
        <tr><td><code>:cookie.<i>key</i></code></td><td>A request cookie</td><td><code>theme = :cookie.theme</code></td></tr>
        <tr><td><code>:uuid</code></td><td>A fresh UUID v4 (handy on INSERT)</td><td><code>SET token = :uuid</code></td></tr>
    </tbody>
</table>
<pre><code>// "orders placed this month"
created BETWEEN :startofmonth AND :today
// "everything in the last 30 days"
created &gt;= :daysago.30
// "logged in the last day, from this IP"
last_seen &gt;= :hoursago.24 AND ip = :ip</code></pre>

<h2>Where you can use them</h2>
<table class="help-table">
    <thead><tr><th>Place</th><th>How</th></tr></thead>
    <tbody>
        <tr><td><b>WHERE&nbsp;tabs</b> (List page filters)</td><td>Type the variable straight into a tab's condition.</td></tr>
        <tr><td><b>Lookup&nbsp;Wizard</b> WHERE</td><td>Make a dropdown depend on the current user/session (see below).</td></tr>
        <tr><td><b>Dashboard charts</b></td><td>A chart component's <em>WHERE</em> box.</td></tr>
        <tr><td><b>Master/Detail child grids</b></td><td>A relation's extra <em>filter</em> — scope the child rows shown under a master.</td></tr>
        <tr><td><b>Export</b></td><td>The active <em>WHERE tab</em> carries into the export automatically.</td></tr>
        <tr><td><b>Your own SQL</b></td><td>Wrap it in <a href="<?= $B ?>/help/DB#preparesql"><code>DB::PrepareSQL()</code></a> in any event / Custom Button.</td></tr>
    </tbody>
</table>

<h2>Recipes</h2>

<h3>0 · A cascading dropdown (depends on another field)</h3>
<p>The classic dependent lookup — a <b>City</b> dropdown that only shows cities in the <b>Country</b> chosen elsewhere
on the same form. Reference the sibling field with <code>:field.name</code>; the list <b>re-filters live</b> the moment
that field changes (the control sends its value automatically).</p>
<?= pp_where('Designer → the City field → Lookup Wizard → <b>WHERE condition</b>', 'php') ?>
<pre><code>country_id = :field.country</code></pre>
<p>Need data from <em>another table</em>? Use a sub-query — still fed by the form:</p>
<?= pp_where('Lookup Wizard → <b>WHERE condition</b>', 'php') ?>
<pre><code>-- only cities whose region matches the selected country's region
region_id IN (SELECT region_id FROM countries WHERE id = :field.country)</code></pre>

<h3>1 · A dropdown that only shows the current user's records</h3>
<p>The classic dependent lookup — a Lookup Wizard field whose options are scoped to whoever is logged in.</p>
<?= pp_where('Designer → a field → Lookup Wizard → <b>WHERE condition</b>', 'php') ?>
<pre><code>owner_id = :userid</code></pre>
<p>Now the dropdown lists only rows the signed-in user owns — no PHP required.</p>

<h3>2 · A List-page tab scoped to me</h3>
<?= pp_where('SQL Query / Additional WHERE tabs → a tab\'s <b>condition</b>', 'php') ?>
<pre><code>assigned_to = :userid AND status = 'open'</code></pre>

<h3>3 · Filter by a value you stashed in the session</h3>
<?= pp_where('Global Events → Application → <b>After Login</b> (store it once)', 'php') ?>
<pre><code>$_SESSION['region'] = pp()->user()['region'] ?? '';</code></pre>
<?= pp_where('…then any WHERE tab / Lookup / chart', 'php') ?>
<pre><code>region = :session.region</code></pre>

<h3>4 · In your own query</h3>
<?= pp_where('Custom Button → <b>Server</b>, a field event, or an <code>action_*</code>', 'php') ?>
<pre><code>// :userid is expanded to the logged-in id, then run
$mine = dbAll(DB::PrepareSQL("SELECT * FROM tickets WHERE assigned_to = :userid ORDER BY due"));

// mix with positional :1 for other values
$sql  = DB::PrepareSQL("SELECT * FROM logs WHERE user_id = :userid AND level >= :1", 3);
$rows = dbAll($sql);</code></pre>

<h3>5 · A Master/Detail child grid, scoped</h3>
<p>Give a relation an extra <em>filter</em> to control which child rows appear under a master — on top of the
foreign-key link. Dynamic variables shine here.</p>
<?= pp_where('Tables → Relation → <b>Filter (WHERE)</b>', 'php') ?>
<pre><code>status = 'active'                     // only active line items
// or dynamic:
created &gt;= :daysago.30                 // only the last 30 days of children
assigned_to = :userid                 // only children assigned to me</code></pre>

<h3>6 · An export that follows the visible tab</h3>
<p>When a List page has <strong>Additional WHERE tabs</strong>, whichever one is
active is applied to the export too — so "Export" from a <em>This&nbsp;month</em> or <em>My&nbsp;records</em> tab gives
exactly those rows. Just put the variable in the tab's condition:</p>
<?= pp_where('SQL Query / WHERE tabs → a tab\'s condition', 'php') ?>
<pre><code>created BETWEEN :startofmonth AND :today
assigned_to = :userid</code></pre>

<h3>7 · An IN-list from a session array</h3>
<?= pp_where('anywhere a WHERE is accepted', 'php') ?>
<pre><code>// $_SESSION['cartIds'] = [12, 15, 20];
product_id IN (:session.cartIds)      // → product_id IN (12,15,20)</code></pre>

<div class="help-callout help-callout-info">
    <i class="bi bi-lightbulb"></i>
    <div>Prefer plain bound <code>?</code> parameters (<a href="<?= $B ?>/help/DB"><code>dbAll / dbRow / dbValue</code></a>)
        for values you already have in PHP. Reach for SQL variables when the query lives in the Designer (a WHERE tab,
        a lookup, a chart) and needs to know <em>who's looking</em> — that's where they shine. The permission-aware
        <a href="<?= $B ?>/help/pp_server_api"><code>pp()->table()</code></a> builder is the code-side equivalent:
        <code>pp()->table('tickets')->where('assigned_to', pp()->userId())</code>.</div>
</div>
