#
# Form
#
# a) List of forms: {{form:S}}='', {{formIdHistory:S}}=''
# b) Edit Form: {{form:S}} - Open form {{form:S}} with record {{r:S}} - typically the FormEditor
# c) Use history of a given form: {{formIdHistory:S}}
#
# {{form:S}}
# {{formIdHistory:S0}} - usage history of form '{{formIdHistory:S}}'

form={{form:SE}}

dbIndex={{indexQfq:Y}}

10.sql = SELECT f.id
  , f.name 
  , f.title
  , f.tableName
  , COUNT(fsl.id) AS submit_count
  , MIN(fsl.created) AS submit_first
  , MAX(fsl.created) AS submit_last
  , GROUP_CONCAT(DISTINCT fsl.pageId ORDER BY fsl.pageId) AS submit_pages 
FROM Form AS f
LEFT JOIN FormSubmitLog AS fsl ON fsl.formId=f.id
WHERE '{{form:SE}}'='' AND {{formIdHistory:S0}}=0
GROUP BY f.id ORDER BY f.name
               
10.twig = {% if result|length > 0 %}
<table class="table table-hover qfq-table-50 tablesorter tablesorter-filter" id="{{store.typo3.pageAlias}}-form"> 
<thead class="qfq-sticky"><tr>
       <th data-sorter="false" class="filter-false">
         <div class="btn-group" role="group">
           {{ ('p:'~store.typo3.pageAlias~'|s|N|U:form=form') | qfqlink }}
           {{ ('p:'~store.typo3.pageAlias~'|s|G:glyphicon-th-list|o:Edit as JSON|U:form=formJson') | qfqlink }}
         </div>
       </th>
       <th>Name</th>
       <th>Title</th>
       <th>Table</th>
       <th>#</th>
       <th><em>Frist</em></th>
       <th><em>Last</em></th>
       <th><em>PageId</em></th>
</tr></thead>
<tbody>

{% for row in result %}
<tr>
  <td>
    {{ ('p:'~store.typo3.pageAlias~'|s|E|U:form=form&r='~row.id) | qfqlink }}
    {{ ('p:'~store.typo3.pageAlias~'|s|G:glyphicon-th-list|o:Edit as JSON|U:form=formJson&r='~row.id) | qfqlink }}
  </td>
  <td>{{ row.name }} <span class="text-muted">({{ row.id }})</span></td>
  <td>
    {{ row.title[:50] }}{%- if row.title|length > 50 -%}
	<span class="qfq-more-text">{{ row.title[50:] }}</span>
    {% endif %}
  </td> 
  <td>{{ row.tableName }}</td>
  <td>
    {% if row.submit_count == 0 %}
       {% set mode = "|r:3" %}
    {% else %}
       {% set mode = "" %}                                                                 
    {% endif %}
    {{ ('p:'~store.typo3.pageAlias~mode~'|U:formIdHistory='~row.id~'|s|b|t:<span class="badge">'~row.submit_count~'</span>') | qfqlink }}
  </td>
  <td><em>{{ row.submit_first ? row.submit_first|date('Y-m-d') : "-" }}</em></td>
  <td><em>{{ row.submit_last ? (date(row.submit_last).diff(date()).days~" days ago") : "-" }}</em></td>
  <td><em>{{ row.submit_pages }}</em></td>
</tr>
{% endfor %}
</tbody></table>                                                                 
{% endif %}

  

       
20.sql = SELECT f.name
               , fsl.feUser
               , fsl.recordId
               , fsl.pageId
               , fsl.created
           FROM FormSubmitLog AS fsl
           LEFT JOIN Form AS f
           ON fsl.formId=f.id
           WHERE fsl.formId={{formIdHistory:S0}}
           ORDER BY fsl.created DESC

20.twig = {% if result|length > 0 %}
<h3>Submit History for {{ result.0.name }}</h3>
<a href="javascript:history.back()">Back</a> 
<table class="table table-hover qfq-table-50 tablesorter tablesorter-filter" id="{{store.typo3.pageAlias}}-formHistory">
<thead class="qfq-sticky"><tr><th>Form</th><th>feUser</th><th>recordId</th><th>pageId</th><th>Submit</th></tr></thead>
<tbody>
{% for row in result %}
<tr>
  {% for col in row %}
  <td>{{ col }}</td>
  {% endfor %}
</tr>
{% else %}
  {# this should never happen, as we do not provide a link if there is no history #}
  <tr><td></td><td></td><td></td><td>no history found</td><td></td><td></td><td></td><td></td><td></td></tr>
{% endfor %}
</tbody></table>
<a href="javascript:history.back()">Back</a> 
{% endif %}