{# crate_anon/crateweb/research/templates/query_edit_select.html #}
{% extends "base.html" %}
{% block extrahead %}
<style>{{ sql_highlight_css }}</style>
{% endblock %}
{% block content %}
{% comment %}
{% include "query_nav.html" %}
{% endcomment %}
<h1>Create/edit SQL query</h1>
<form action="{% url 'query' %}" method="post">
{% csrf_token %}
<table class="formtable">
{{ form }}
</table>
<input type="submit" name="submit_add" value="Add" />
<input type="submit" name="submit_run" value="Run" />
<input type="submit" name="submit_filter" value="Filter" />
<input type="submit" name="submit_builder"
value="Send to query builder" />
</form>
<h2>Tips</h2>
<ul>
<li>Most browsers will let you resize the SQL edit box; try dragging
the corner.</li>
<li>If you’re new to SQL, try a tutorial such as those by
<a href="https://sqlzoo.net/">SQLZoo</a>,
<a href="http://www.sql-tutorial.net/">SQL-Tutorial</a>,
<a href="https://www.codecademy.com/learn/learn-sql">Codecademy</a>, or
<a href="https://www.w3schools.com/sql/">W3Schools</a>.</li>
{% if dialect_mysql %}
<li>The research database runs MySQL.</li>
<li>Append <b><code>LIMIT row_count</code></b> or
<b><code>LIMIT offset, row_count</code></b> to limit the results,
where <code>offset</code> is numbered from 0 (not 1), and
<code>row_count</code> is the number of records to retrieve
(so <code>LIMIT 50</code> or <code>LIMIT 0, 50</code> retrieves
the first 50 records, and <code>LIMIT 50, 50</code> retrieves the
next 50).</li>
{% elif dialect_mssql %}
<li>The research database runs Microsoft SQL Server.</li>
<li>Use <b><code>SELECT TOP 100 ...</code></b>
to limit the results to the first 100 rows.
</li>
{% endif %}
{% if dbinfolist %}
<li>If you are registered with the website as a clinician, you can
use the following method to convert unanonymised patient ids to
anonymised research ids:<br>
Write your query as normal but replace the condition
<code>rid = ridvalue</code> with
<b><code>~pid:dbname = pidvalue</code></b> or
<b><code>~mpid:dbname = mpidvalue</code></b>.
where 'dbname' is the website's name for the database whose
whose rid you want to find (choices are
{% for dbinfo in dbinfolist %}
'{{dbinfo.name}}',
{% endfor %}).
Note:
<ul>
<li>This works with the 'IN' operator as well.</li>
<li>'pid' refers to the patient id, which for the databases
in the order listed above means
{% for dbinfo in dbinfolist %}
'{{dbinfo.pid_pseudo_field}}',
{% endfor %}
</li>
<li>'mpid' refers to the master patient id, which for the
databases in the order listed above means
{% for dbinfo in dbinfolist %}
'{{dbinfo.mpid_pseudo_field}}',
{% endfor %}
</li>
<li>'rid' refers to the research id, which for the
databases in the order listed above means
{% for dbinfo in dbinfolist %}
'{{dbinfo.rid_field}}',
{% endfor %}
</li>
</ul>
</li>
{% endif %}
<li>The query builder is not as sophisticated as raw SQL, so it may
fail to alter complex SQL properly.</li>
</ul>
<h1>Previous queries</h1>
{% include "expand_collapse_buttons.html" %}
{% if queries %}
<table>
<tr>
<th>Created at</th>
<th>Delete query</th>
<th>Active?</th>
<th>Run since last<br />database update?</th>
<th>Choose this one</th>
<th>Filter display</th>
<th>SQL</th>
</tr>
{% for query in queries %}
<tr>
<td>{{ query.created }}</td>
<td>
<form action="{% url 'delete_query' query.id %}" method="post">
{% csrf_token %}
<input type="submit" value="Delete" />
</form>
</td>
<td>
{% if query.active %}<b>{% endif %}
{{ query.active|yesno }}
{% if query.active %}</b>{% endif %}
</td>
<td>
{% if query.run_since_update is None %}
<span>No information</span>
{% elif query.run_since_update is True %}
<span style="color: green">Yes</span>
{% else %}
<span style="color: red">No</span>
{% endif %}
</td>
<td>
{% if query.active %}
(activated)
{% else %}
<form action="{% url 'activate_query' query.id %}" method="post">
{% csrf_token %}
<input type="submit" value="Choose" />
</form>
{% endif %}
</td>
<td>
<form action="{% url 'edit_display' query.id %}" method="post">
{% csrf_token %}
<input type="submit" value="Filter" />
</form>
</td>
{% comment %}Widest, so last:{% endcomment %}
{% if query.truncated_sql %}
<td>{{ query.truncated_sql|safe }}... <strong>Long query. See
<a href="{% url 'show_query' query.id %}">here</a></strong></td>
{% else %}
<td>{{ query.formatted_query_safe|safe }}</td>
{% endif %}
</tr>
{% endfor %}
</table>
{% include "page_nav.html" with page=queries %}
{% else %}
<div>None.</div>
{% endif %}
{% endblock %}
{% block helpurl %}{{ HelpUrl.sql }}{% endblock %}