#
# Issue
# Table of issues based on filter parameters from URL
# Detailed view of issue with comments
#
# {{urlparam:SE}}
# {{iss:SC0:digit}}
# {{action:SE::w}}
# {{deleteId:S0}}
# {{formId:S0}}
# {{formName:SE}}
# {{xid:S0}}
# {{pIdUser:Y}}
# {{emailUser:Y}}

form = {{form:SE}}
render = both

# Get parameters from URL
# Normalize variables
{
  sql = SELECT
            /* URL parameter */
            QIFEMPTY('{{urlparam:SE}}', '{{queryString:RE}}') AS _parameters
            , '{{iss:SC0:digit}}' AS _issueId
            , '{{action:SE::w}}' AS _action
            , '{{deleteId:S0}}' AS _deleteId
            , '{{formId:S0}}' AS _formId
            , '{{formName:SE}}' AS _formName
            , '{{xid:S0}}' AS _xId

            /* Grp ids */
            , grProjectAccessWrite.id AS _grProjectAccessWriteId
            , grProjectAccessRead.id AS _grProjectAccessReadId
            , grProjectAccessAdmin.id AS _grProjectAccessAdminId
            , grUploadIssue.id AS _grUploadIssueId
            , grUploadComment.id AS _grUploadCommentId
            , grTagIssue.id AS _grTagIssue

            /* User */
            , '{{pIdUser:Y}}' AS _pIdUser
            , '{{emailUser:Y}}' AS _emailUser
          FROM Grp AS grProjectAccessWrite, Grp AS grProjectAccessRead, Grp AS grProjectAccessAdmin, Grp AS grUploadIssue, Grp AS grUploadComment, Grp AS grTagIssue
          WHERE grProjectAccessWrite.reference = 'qfq_project_access_write'
            AND grProjectAccessRead.reference = 'qfq_project_access_read'
            AND grProjectAccessAdmin.reference = 'qfq_project_access_admin'
            AND grUploadIssue.reference = 'qfq_upload_issue'
            AND grUploadComment.reference = 'qfq_upload_comment'
            AND grTagIssue.reference = 'qfq_tag_issue'
}

# ACTION: DELETE ISSUE
######################
{
  sql = UPDATE Issue
            SET deleted = 'yes'
          WHERE '{{action:RE}}' = 'deleteIssue'
            AND id = '{{deleteId:R0}}'
}

# ACTION: DELETE COMMENT
########################
{
  sql = UPDATE Comment
            SET deleted = 'yes'
          WHERE '{{action:RE}}' = 'deleteComment'
            AND id = '{{deleteId:R0}}'
}

# TABLE OF ISSUES
#################
{
  sql = SELECT ''
          FROM DUAL
          WHERE '{{form:SE}}' = ''
            AND '{{issueId:R0}}' = 0

  # Recursively extract params from URL or query string
  {
    sql = WITH RECURSIVE parameters AS (
              SELECT 1 AS counter
              , SUBSTRING_INDEX(SUBSTRING_INDEX('{{parameters:RE}}', '&', 1), '=', 1) AS paramName
              , SUBSTRING_INDEX(SUBSTRING_INDEX('{{parameters:RE}}', '&', 1), '=', -1) AS paramValue
              , '{{parameters:RE}}' AS originalParams
            UNION ALL
              SELECT counter + 1
              , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(originalParams, '&', counter + 1), '&', -1), '=', 1)
              , SUBSTRING_INDEX(SUBSTRING_INDEX(originalParams, '&', counter + 1), '=', -1)
              , originalParams
            FROM parameters
            WHERE counter < LENGTH(originalParams) - LENGTH(REPLACE(originalParams, '&', '')) + 1
            )

              /* Build SQL query based on params: Map to column */
              SELECT GROUP_CONCAT(CASE
              WHEN paramName = 'pro' THEN CONCAT('i.prId = "', paramValue, '"')
              WHEN paramName = 'sta' THEN CONCAT('i.stId = "', paramValue, '"')
              WHEN paramName = 'xid' THEN CONCAT('i.xId = "', paramValue, '"')
              WHEN paramName = 'fid' THEN CONCAT('i.formId = "', paramValue, '"')
              WHEN paramName = 'fna' THEN CONCAT('i.formName = "', paramValue, '"')
              WHEN paramName = 'cre' THEN CONCAT('i.pIdCreator = "', paramValue, '"')
              WHEN paramName = 'ass' THEN CONCAT('i.pIdAssignee = "', paramValue, '"')
              WHEN paramName = 'con' THEN CONCAT('i.pIdContact = "', paramValue, '"')
              WHEN paramName = 'mco' THEN CONCAT('i.mailContact = "', paramValue, '"')
              WHEN paramName = 'mws' THEN CONCAT('i.mailWatcherSet = "', paramValue, '"')
              WHEN paramName = 'mns' THEN CONCAT('i.mailNotificationSet = "', paramValue, '"')
              WHEN paramName = 'mno' THEN CONCAT('i.mailNotification = "', paramValue, '"')
              WHEN paramName = 'tit' THEN CONCAT('i.title = "', paramValue, '"')
              WHEN paramName = 'des' THEN CONCAT('i.description = "', paramValue, '"')
              WHEN paramName = 'str' THEN CONCAT('i.start = "', paramValue, '"')
              WHEN paramName = 'due' THEN CONCAT('i.due = "', paramValue, '"')
              WHEN paramName = 'del' THEN CONCAT('i.deleted = "', paramValue, '"')
              WHEN paramName = 'sea' THEN CONCAT('(i.title LIKE "%', paramValue, '%" OR i.description LIKE "%', paramValue, '%")')
              WHEN paramName = 'tag' THEN CONCAT('FIND_IN_SET(grTag.name, "', paramValue, '")')
              WHEN paramName = 'rem' THEN IF(paramValue, 'i.reminder < NOW() AND i.reminder != "0"', NULL)
              END
              SEPARATOR ' AND ') AS _query
              , MAX(paramName = 'del') AS _hasDel
              , IFNULL(MAX(CASE
              WHEN paramName = 'dnd' AND paramValue = 'yes' THEN 'table-draggable'
              WHEN paramName = 'dnd' AND paramValue = 'no' THEN ''
              ELSE NULL
              END), '') AS _dragAndDropClass
              , IFNULL(MAX(CASE
              WHEN paramName = 'tso' AND paramValue = 'yes' THEN 'tablesorter tablesorter-filter'
              WHEN paramName = 'tso' AND paramValue = 'no' THEN ''
              ELSE NULL
              END), 'tablesorter tablesorter-filter') AS _tablesorterClasses
              , IFNULL(MAX(CASE
              WHEN paramName = 'col' THEN paramValue
              ELSE NULL
              END), 'Project,Status,Title,Description,Assignee,Start,Due,Updated') AS _columnSet
            FROM parameters
  }

  # Recursively create table headers
  {
    sql = WITH RECURSIVE headers AS (
              SELECT 1 AS counter
              , SUBSTRING_INDEX('{{columnSet:RE}}', ',', 1) AS headerName
              , '{{columnSet:RE}}' AS originalHeaders
            UNION ALL
              SELECT
              counter + 1
              , SUBSTRING_INDEX(SUBSTRING_INDEX(originalHeaders, ',', counter + 1), ',', -1)
              , originalHeaders
            FROM headers
            WHERE counter < LENGTH(originalHeaders) - LENGTH(REPLACE(originalHeaders, ',', '')) + 1
            )

              SELECT IF(FIND_IN_SET(headerName, 'Project,Status,Assignee'), CONCAT('<th class="filter-select">', headerName, '</th>'), CONCAT('<th>', headerName, '</th>'))
            FROM headers
            WHERE headerName != ''

    shead = <table class="table qfq-table-50 {{tablesorterClasses:RE}} {{dragAndDropClass:RE}}">
              <thead>
                <tr>
                  <th>{{p:/issue?form=issue&r=0&pIdCreator={{pIdUser:R0}}&pIdUser={{pIdUser:R0}}&formId={{formId:R0}}&formName={{formName:RE}}&xId={{xId:R0}}|s|N AS _link}}</th>
    stail =     </tr>
              </thead>
  }

  # Filter issues based on params
  # Build table
  {
    sql = SELECT IF('{{dragAndDropClass:RE}}' != '', CONCAT('<tr id="{{random:V}}-', i.id, '" data-record-id="', i.id, '" data-drop-zone-table-origin="Issue">'), '<tr>')
              , CONCAT('p:/issue'
              , '?iss=', i.id
              , '|t:<span class="badge">#', i.id, '</span>'
              , '|v:<td>'
              , '|V:</td>'
              , '|b') AS _link
              , IF(FIND_IN_SET('Project', '{{columnSet:RE}}'), CONCAT('<td>', IFNULL(pr.name, ''), '</td>'), '')
              , IF(FIND_IN_SET('Status', '{{columnSet:RE}}'), CONCAT('<td>', st.name, '</td>'), '')
              , IF(FIND_IN_SET('Title', '{{columnSet:RE}}'), CONCAT('<td>', i.title, '</td>'), '')
              , IF(FIND_IN_SET('Description', '{{columnSet:RE}}'), CONCAT('<td>', QMORE(STRIP_TAGS(i.description), 100), '</td>'), '')
              , IF(FIND_IN_SET('Assignee', '{{columnSet:RE}}'), CONCAT('<td>', CONCAT_WS(' ', pAs.`{{personFirstNameColumnName:Y}}`, pAs.`{{personLastNameColumnName:Y}}`), '</td>'), '')
              , IF(FIND_IN_SET('Start', '{{columnSet:RE}}'), CONCAT('<td>', IF(i.start = '0000-00-00', '', DATE_FORMAT(i.start, '%d.%m.%Y')), '</td>'), '')
              , IF(FIND_IN_SET('Due', '{{columnSet:RE}}'), CONCAT('<td>', IF(i.due = '0000-00-00', '', DATE_FORMAT(i.due, '%d.%m.%Y')), '</td>'), '')
              , IF(FIND_IN_SET('Updated', '{{columnSet:RE}}'), CONCAT('<td>', QDATE_FORMAT(i.modified), '</td>'), '')
            FROM Issue AS i
            LEFT JOIN Status AS st
              ON st.id = i.stId
            LEFT JOIN `{{personTableName:Y}}` AS pAs
              ON pAs.id = i.pIdAssignee
            LEFT JOIN Project AS pr
              ON pr.id = i.prId
            LEFT JOIN (Glue AS glTag, Grp AS grTag)
              ON grTag.grId = '{{grTagIssue:R0}}'
              AND glTag.grId = grTag.id
              AND glTag.xId = i.id

              /* User access (write, read or admin) */
            LEFT JOIN Glue AS gl
              ON gl.grId IN ('{{grProjectAccessWriteId:R0}}', '{{grProjectAccessReadId:R0}}', '{{grProjectAccessAdminId:R0}}')
              AND gl.xId = pr.id
              AND gl.pId = '{{pIdUser:R0}}'

            WHERE {{query:RE::-:1}}
              AND IF('{{hasDel:R0}}', 1, i.deleted = 'no')

              /* Check user access */
              AND CASE

              /* User has permissions on project (write, read or admin) */
              WHEN NOT ISNULL(gl.id) THEN 1

              /* User is contact person or watcher */
              WHEN i.accessContact = 'yes' AND ((i.pIdContact = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0) OR ((i.mailContact = '{{emailUser:RE}}' OR FIND_IN_SET('{{emailUser:RE}}', i.mailWatcherSet)) AND '{{emailUser:RE}}' != '')) THEN 1

              /* User is creator */
              WHEN i.pIdCreator = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0 THEN 1

              /* User is assignee */
              WHEN i.pIdAssignee = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0 THEN 1

              /* Default: No permission */
              ELSE 0
              END
            GROUP BY i.id
            ORDER BY i.modified DESC

    shead =   <tbody>
    rend =      </tr>
    stail =   </tbody>
            </table>
  }
}

# VIEW OF ISSUE
###############
{
  sql = SELECT ''
          FROM DUAL
          WHERE '{{form:SE}}' = ''
            AND '{{issueId:R0}}' != 0

  # Single issues based on filter
  # Build view
  {
    sql = SELECT CONCAT_WS(' ', pCr.`{{personFirstNameColumnName:Y}}`, pCr.`{{personLastNameColumnName:Y}}`) AS _creator
              , DATE_FORMAT(i.created, '%d.%m.%Y') AS _created
              , QDATE_FORMAT(i.modified) AS _modified
              , CONCAT('p:{{pageSlug:T}}'
              , '?form=issue'
              , '&r=', i.id
              , '&pIdUser={{pIdUser:R0}}'
              , '&isAdmin=', NOT ISNULL(glAccessAdmin.id)
              , CASE
              WHEN NOT ISNULL(glAccessAdmin.id) THEN ''
              WHEN NOT ISNULL(glAccessWrite.id) THEN ''
              WHEN NOT ISNULL(glAccessRead.id) THEN '&formModeGlobal=readonly'
              ELSE ''
              END
              , '|s|E|t:Edit') AS '_link|issueLink|_hide'
              , CONCAT('p:{{pageSlug:T}}'
              , '?form=issueComment'
              , '&r=0'
              , '&isId=', i.id
              , '&pIdCreator={{pIdUser:R0}}'
              , '&pIdUser={{pIdUser:R0}}'
              , '&isAdmin=', NOT ISNULL(glAccessAdmin.id)
              , CASE
              WHEN NOT ISNULL(glAccessAdmin.id) THEN ''
              WHEN NOT ISNULL(glAccessWrite.id) THEN ''
              WHEN NOT ISNULL(glAccessRead.id) THEN '&formModeGlobal=readonly'
              ELSE ''
              END
              , '|b'
              , '|t:Comment'
              , '|s|N') AS '_link|newCommentLink|_hide'
              , CONCAT('y:{{baseUrl:Y}}{{pageSlug:T}}?iss=', i.id, '|t:Copy URL|o:') AS '_yank|copy|_hide'
              , i.title AS _title
              , QNL2BR(i.description) AS _description
              , pr.name AS _project
              , st.name AS _status
              , IF(i.accessContact = 'yes', 'checked="checked"', '') AS _accessContactChecked
              , CONCAT_WS(' ', pAs.`{{personFirstNameColumnName:Y}}`, pAs.`{{personLastNameColumnName:Y}}`) AS _assignee
              , CONCAT_WS(' ', pCo.`{{personFirstNameColumnName:Y}}`, pCo.`{{personLastNameColumnName:Y}}`) AS _contactPerson
              , i.mailContact AS _contactPersonMail
              , i.mailWatcherSet AS _watcherMailSet
              , i.mailNotificationSet AS _mailNotificationSet
              , DATE_FORMAT(i.start, '%d.%m.%Y') AS _start
              , DATE_FORMAT(i.due, '%d.%m.%Y') AS _due
              , i.estimatedTime AS _estimatedTime
              , QDATE_FORMAT(i.reminder) AS _reminder
            FROM (Issue AS i, `{{personTableName:Y}}` AS pCr)
            LEFT JOIN Status AS st
              ON st.id = i.stId
            LEFT JOIN `{{personTableName:Y}}` AS pAs
              ON pAs.id = i.pIdAssignee
            LEFT JOIN `{{personTableName:Y}}` AS pCo
              ON pCo.id = i.pIdContact
            LEFT JOIN Project AS pr
              ON pr.id = i.prId
            LEFT JOIN FileUpload AS fu
              ON fu.grId = '{{grUploadIssueId:R0}}'
              AND fu.xId = i.id

              /* User access: Write */
            LEFT JOIN Glue AS glAccessWrite
              ON glAccessWrite.grId = '{{grProjectAccessWriteId:R0}}'
              AND glAccessWrite.xId = pr.id
              AND glAccessWrite.pId = '{{pIdUser:R0}}'

              /* User access: Read */
            LEFT JOIN Glue AS glAccessRead
              ON glAccessRead.grId = '{{grProjectAccessReadId:R0}}'
              AND glAccessRead.xId = pr.id
              AND glAccessRead.pId = '{{pIdUser:R0}}'

              /* User access: Admin */
            LEFT JOIN Glue AS glAccessAdmin
              ON glAccessAdmin.grId = '{{grProjectAccessAdminId:R0}}'
              AND glAccessAdmin.xId = pr.id
              AND glAccessAdmin.pId = '{{pIdUser:R0}}'

            WHERE i.id = '{{issueId:R0}}'
              AND i.pIdCreator = pCr.id
              AND i.deleted = 'no'

              /* Check user access */
              AND CASE

              /* User has permissions on project (write, read or admin) */
              WHEN NOT ISNULL(glAccessWrite.id) OR NOT ISNULL(glAccessRead.id) OR NOT ISNULL(glAccessAdmin.id) THEN 1

              /* User is contact person or watcher */
              WHEN i.accessContact = 'yes' AND ((i.pIdContact = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0) OR ((i.mailContact = '{{emailUser:RE}}' OR FIND_IN_SET('{{emailUser:RE}}', i.mailWatcherSet)) AND '{{emailUser:RE}}' != '')) THEN 1

              /* User is creator */
              WHEN i.pIdCreator = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0 THEN 1

              /* User is assignee */
              WHEN i.pIdAssignee = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0 THEN 1

              /* Default: No permission */
              ELSE 0
              END
            GROUP BY i.id

    uploadsIssue {
      sql = SELECT CONCAT('F:', fu.pathFileName
                , '|d:', SUBSTRING_INDEX(fu.pathFileName, '/', -1)
                , '|t:', SUBSTRING_INDEX(fu.pathFileName, '/', -1)
                , '|v:<span class="glyphicon glyphicon-file"></span>'
                , '|g:_blank'
                , '|b:0'
                ) AS _file
              FROM FileUpload AS fu
              WHERE fu.grId = '{{grUploadIssueId:R0}}'
                AND fu.xId = '{{issueId:R0}}'
              ORDER BY fu.id ASC

      rsep = <br>
      content = hide
    }

    uploadsIssueMetaData {
      sql = SELECT CONCAT(p.`{{personFirstNameColumnName:Y}}`, ' ', p.`{{personLastNameColumnName:Y}}`
                , ', '
                , QDATE_FORMAT(fu.created)
                , ', '
                , ROUND(fu.fileSize / 1024 / 1024, 2), ' MB'
              )
              FROM FileUpload AS fu, `{{personTableName:Y}}` AS p
              WHERE fu.grId = '{{grUploadIssueId:R0}}'
                AND fu.xId = '{{issueId:R0}}'
                AND fu.pId = p.id
              ORDER BY fu.id ASC

      rsep = <br>
      content = hide
    }

    # Tags
    {
      sql = SELECT GROUP_CONCAT('<span class="badge">', gr.name, '</span>' SEPARATOR '&nbsp;') AS _tags
              FROM Glue AS gl, Grp AS gr
              WHERE gr.grId = '{{grTagIssue:R0}}'
                AND gl.grId = gr.id
                AND gl.xId = '{{issueId:R0}}'
    }

    # User does not have access
    althead = <p style="font-weight: bold;">Forbidden</p>

    tail = <div class="row">
             <div class="col-md-12 col-lg-10">
               <div class="panel panel-default">
                 <div class="panel-heading">
                   <div style="display: flex; align-items: center;">
                     <div>
                       <span class="panel-title" style="font-weight: bold;">Issue #{{issueId:R0}}</span>
                       <br>
                       <span>Created by {{creator:RE}} on {{created:RE}}, updated on {{modified:RE}}</span>
                     </div>
                     <span class="pull-right">
                       <div class="btn-group">
                         {{&issueLink:RE::-}}
                         {{&newCommentLink:RE::-}}
                         {{&copy:RE::-}}
                       </div>
                     </span>
                     <span style="clear: both;"></span>
                   </div>
                 </div>
                 <div class="panel-body">
                   <fieldset class="qfq-fieldset">
                     <legend>General</legend>
                     <div class="row">
                       <div class="col-md-12">
                         <div class="qfq-issue-input qfq-disabled">
                           {{title:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                     <div class="row">
                       <div class="col-md-12">
                         <div class="qfq-issue-input qfq-disabled">
                           {{description:RE::-:&nbsp;}}
                         </div>
                       </div>
                     </div>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Status
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-input qfq-disabled">
                           {{status:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                   </fieldset>
                   <fieldset class="qfq-fieldset">
                     <legend>Context</legend>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Project
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-input qfq-disabled">
                           {{project:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Access
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-checkbox">
                           <label class="checkbox qfq-disabled">
                             <input type="checkbox" disabled {{accessContactChecked:RE::-}}></input>
                             <span class="checkmark" aria="hidden"></span>
                           </label>
                           <span style="padding-left: 25px;">Contact/Watcher</span>
                         </div>
                       </div>
                     </div>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Assignee
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-input qfq-disabled">
                           {{assignee:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Tags
                         </div>
                       </div>
                       <div class="col-md-10">
                         <div class="qfq-issue-tags">
                           {{tags:RE::-}}
                         </div>
                       </div>
                     </div>
                   </fieldset>
                   <fieldset class="qfq-fieldset">
                     <legend>Contact</legend>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Contact person
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-input qfq-disabled">
                           {{contactPerson:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Contact person mail
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-input qfq-disabled">
                           {{contactPersonMail:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Watcher mail (list)
                         </div>
                       </div>
                       <div class="col-md-10">
                         <div class="qfq-issue-input qfq-disabled">
                           {{watcherMailSet:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Notification
                         </div>
                       </div>
                       <div class="col-md-10">
                         <div class="qfq-issue-input qfq-disabled">
                           {{mailNotificationSet:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                   </fieldset>
                   <fieldset class="qfq-fieldset">
                     <legend>Timeline</legend>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Start date
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-input qfq-disabled">
                           {{start:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Due date
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-input qfq-disabled">
                           {{due:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Reminder
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-input qfq-disabled">
                           {{reminder:RE:::&nbsp;}}
                         </div>
                       </div>
                       <div class="col-md-2">
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           Estimated time
                         </div>
                       </div>
                       <div class="col-md-2">
                         <div class="qfq-issue-input qfq-disabled">
                           {{estimatedTime:RE:::&nbsp;}}
                         </div>
                       </div>
                     </div>
                   </fieldset>
                   <fieldset class="qfq-fieldset {{SELECT IF('{{uploadsIssue.line.total}}' = 0, 'hidden', '')}}">
                     <legend>Attachments</legend>
                     <div class="row">
                       <div class="col-md-2">
                         <div class="qfq-issue-label">
                           File(s)
                         </div>
                       </div>
                       <div class="col-md-6">
                         <div class="qfq-issue-uploads">
                           {{uploadsIssue.line.content}}
                         </div>
                       </div>
                       <div class="col-md-4">
                         <div class="qfq-issue-uploads">
                           {{uploadsIssueMetaData.line.content}}
                         </div>
                       </div>
                     </div>
                   </fieldset>
                 </div>
               </div>
             </div>
           </div>
  }
}

# COMMENTS OF ISSUE
###################
{
  sql = SELECT ''
          FROM DUAL
          WHERE ('{{form:SE}}' = ''
            AND '{{issueId:R0}}' != 0)
            OR '{{form:SE}}' = 'issue'

  comments {
    sql = SELECT CONCAT('p:{{pageSlug:T}}'
              , '?form=issueComment'
              , '&r=', co.id
              , '&pIdUser={{pIdUser:R0}}'
              , '&isAdmin=', NOT ISNULL(glAccessAdmin.id)
              , CASE
              WHEN NOT ISNULL(glAccessAdmin.id) THEN ''
              WHEN NOT ISNULL(glAccessWrite.id) THEN ''
              WHEN NOT ISNULL(glAccessRead.id) THEN '&formModeGlobal=readonly'
              ELSE ''
              END
              , '|b:btn btn-default btn-small'
              , '|t:Edit'
              , '|s|E') AS '_link|commentLink|_hide'
              , QNL2BR(co.comment) AS _comment
              , CONCAT('<span class="badge"', IF(co.accessContact = 'yes', '>', 'style="text-decoration: line-through;">'), 'Access: Contact/Watcher</span>') AS _accessContact
              , QDATE_FORMAT(co.modified) AS _coModified
              , CONCAT_WS(' ', p.`{{personFirstNameColumnName:Y}}`, p.lastName) AS _author
              , co.id AS _commentId

              /* HTML based on view */
              , IF('{{r:S0}}' != 0, '', '<div class="row">') AS _divRowStart
              , IF('{{r:S0}}' != 0, '', '</div>') AS _divRowEnd
              , IF(co.pIdCreator = '{{pIdUser:R0}}', 'qfq-issue-comment-panel', '') AS _panelClass
              , IF(co.pIdCreator = '{{pIdUser:R0}}', 'qfq-issue-comment-panel-heading', '') AS _panelHeadingClass
            FROM (Issue AS i, `{{personTableName:Y}}` AS p, Comment AS co)
            LEFT JOIN Project AS pr
              ON pr.id = i.prId

              /* User access: Write */
            LEFT JOIN Glue AS glAccessWrite
              ON glAccessWrite.grId = '{{grProjectAccessWriteId:R0}}'
              AND glAccessWrite.xId = pr.id
              AND glAccessWrite.pId = '{{pIdUser:R0}}'

              /* User access: Read */
            LEFT JOIN Glue AS glAccessRead
              ON glAccessRead.grId = '{{grProjectAccessReadId:R0}}'
              AND glAccessRead.xId = pr.id
              AND glAccessRead.pId = '{{pIdUser:R0}}'

              /* User access: Admin */
            LEFT JOIN Glue AS glAccessAdmin
              ON glAccessAdmin.grId = '{{grProjectAccessAdminId:R0}}'
              AND glAccessAdmin.xId = pr.id
              AND glAccessAdmin.pId = '{{pIdUser:R0}}'

            WHERE i.id = QIFEMPTY('{{issueId:R0}}', '{{r:S0}}')
              AND i.deleted = 'no'
              AND co.deleted = 'no'
              AND co.isId = i.id
              AND co.pIdCreator = p.id

              /* Check user access */
              AND CASE

              /* User has permissions on project (write, read or admin) */
              WHEN NOT ISNULL(glAccessWrite.id) OR NOT ISNULL(glAccessRead.id) OR NOT ISNULL(glAccessAdmin.id) THEN 1

              /* User is contact person or watcher */
              WHEN co.accessContact = 'yes' AND ((i.pIdContact = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0) OR ((i.mailContact = '{{emailUser:RE}}' OR FIND_IN_SET('{{emailUser:RE}}', i.mailWatcherSet)) AND '{{emailUser:RE}}' != '')) THEN 1

              /* User is creator */
              WHEN co.pIdCreator = '{{pIdUser:R0}}' AND '{{pIdUser:R0}}' != 0 THEN 1

              /* Default: No permission */
              ELSE 0
              END
            GROUP BY co.id
            ORDER BY co.created ASC

    uploadsComment {
      sql = SELECT CONCAT('F:', fu.pathFileName
                , '|d:', SUBSTRING_INDEX(fu.pathFileName, '/', -1)
                , '|t:', SUBSTRING_INDEX(fu.pathFileName, '/', -1)
                , '|v:<span class="glyphicon glyphicon-file"></span>'
                , '|g:_blank'
                , '|b:0'
                ) AS _file
              FROM FileUpload AS fu
              WHERE fu.grId = '{{grUploadCommentId:R0}}'
                AND fu.xId = '{{commentId:R0}}'
              ORDER BY fu.id ASC

      rsep = <br>
      content = hide
    }

    uploadsCommentMetaData {
      sql = SELECT CONCAT(p.`{{personFirstNameColumnName:Y}}`, ' ', p.`{{personLastNameColumnName:Y}}`
                , ', '
                , QDATE_FORMAT(fu.created)
                , ', '
                , ROUND(fu.fileSize / 1024 / 1024, 2), ' MB'
              )
              FROM FileUpload AS fu, `{{personTableName:Y}}` AS p
              WHERE fu.grId = '{{grUploadCommentId:R0}}'
                AND fu.xId = '{{commentId:R0}}'
                AND fu.pId = p.id
              ORDER BY fu.id ASC

      rsep = <br>
      content = hide
    }

    renr = {{divRowStart:RE::-}}
           <div class="col-md-12 col-lg-10">
             <div class="panel panel-default {{panelClass:RE}}" {{SELECT IF('{{r:S0}}' != 0 AND '{{comments.line.count}}' = 1, 'style="margin-top: 20px"', '')}}>
               <div class="panel-heading {{panelHeadingClass:RE}}" style="display: flex; align-items: center;">
                 <span>Comment by {{author:RE}} on {{coModified:RE}}</span>
                 <span class="pull-right">
                   <div style="display: flex; align-items: center; gap: 2px;">
                     {{accessContact:RE::-}}
                     {{&commentLink:RE::-}}
                   </div>
                 </span>
                 <span style="clear: both;"></span>
               </div>
               <div class="panel-body">
                 <div class="row">
                   <div class="col-md-12">
                     <div class="qfq-issue-input">
                       {{comment:RE::-}}
                     </div>
                   </div>
                 </div>
                 <div class="row {{SELECT IF('{{uploadsComment.line.total}}' = 0, 'hidden', '')}}">
                   <div class="col-md-2">
                     <div class="qfq-issue-label">
                       File(s)
                     </div>
                   </div>
                   <div class="col-md-6">
                     <div class="qfq-issue-uploads">
                       {{uploadsComment.line.content}}
                     </div>
                   </div>
                   <div class="col-md-4">
                     <div class="qfq-issue-uploads">
                       {{uploadsCommentMetaData.line.content}}
                     </div>
                   </div>
                 </div>
               </div>
             </div>
           </div>
           {{divRowEnd:RE::-}}
    stail = <div class="row">
              <div class="col-md-12 col-lg-10">
                <span class="pull-right">
                  <div class="btn-group">
                    {{&issueLink:RE::-}}
                    {{&newCommentLink:RE::-}}
                    {{&copy:RE::-}}
                  </div>
                </span>
                <span style="clear: both;"></span>
              </div>
            </div>
          </div>
  }
}