/*
** Copyright (c) 2007 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)

** This program is distributed in the hope that it will be useful,
** but without any warranty; without even the implied warranty of
** merchantability or fitness for a particular purpose.
**
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*******************************************************************************
**  
** Code to generate the ticket listings
*/
#include "config.h"
#include "report.h"
#include <assert.h>

/* Forward references to static routines */
static void report_format_hints(void);

/*
** WEBPAGE: /reportlist
*/
void view_list(void){
  const char *zScript;
  Blob ril;   /* Report Item List */
  Stmt q;
  int rn = 0;
  int cnt = 0;

  login_check_credentials();
  if( !g.okRdTkt && !g.okNewTkt ){ login_needed(); return; }
  style_header("Ticket Main Menu");
  if( g.thTrace ) Th_Trace("BEGIN_REPORTLIST<br />\n", -1);
  zScript = ticket_reportlist_code();
  if( g.thTrace ) Th_Trace("BEGIN_REPORTLIST_SCRIPT<br />\n", -1);
  
  blob_zero(&ril);
  ticket_init();

  db_prepare(&q, "SELECT rn, title, owner FROM reportfmt ORDER BY title");
  while( db_step(&q)==SQLITE_ROW ){
    const char *zTitle = db_column_text(&q, 1);
    const char *zOwner = db_column_text(&q, 2);
    if( zTitle[0] =='_' && !g.okTktFmt ){
      continue;
    }
    rn = db_column_int(&q, 0);
    cnt++;
    blob_appendf(&ril, "<li>");
    if( zTitle[0] == '_' ){
      blob_appendf(&ril, "%s", zTitle);
    } else {
      blob_appendf(&ril, "<a href=\"rptview?rn=%d\" rel=\"nofollow\">%h</a>", rn, zTitle);
    }
    blob_appendf(&ril, "&nbsp;&nbsp;&nbsp;");
    if( g.okWrite && zOwner && zOwner[0] ){
      blob_appendf(&ril, "(by <i>%h</i></i>) ", zOwner);
    }
    if( g.okTktFmt ){
      blob_appendf(&ril, "[<a href=\"rptedit?rn=%d&amp;copy=1\" rel=\"nofollow\">copy</a>] ", rn);
    }
    if( g.okAdmin || (g.okWrTkt && zOwner && strcmp(g.zLogin,zOwner)==0) ){
      blob_appendf(&ril, "[<a href=\"rptedit?rn=%d\" rel=\"nofollow\">edit</a>] ", rn);
    }
    if( g.okTktFmt ){
      blob_appendf(&ril, "[<a href=\"rptsql?rn=%d\" rel=\"nofollow\">sql</a>] ", rn);
    }
    blob_appendf(&ril, "</li>\n");
  }

  Th_Store("report_items", blob_str(&ril));
  
  Th_Render(zScript);
  
  blob_reset(&ril);
  if( g.thTrace ) Th_Trace("END_REPORTLIST<br />\n", -1);

  style_footer();
}

/*
** Remove whitespace from both ends of a string.
*/
char *trim_string(const char *zOrig){
  int i;
  while( isspace(*zOrig) ){ zOrig++; }
  i = strlen(zOrig);
  while( i>0 && isspace(zOrig[i-1]) ){ i--; }
  return mprintf("%.*s", i, zOrig);
}

/*
** Extract a numeric (integer) value from a string.
*/
char *extract_integer(const char *zOrig){
  if( zOrig == NULL || zOrig[0] == 0 ) return "";
  while( *zOrig && !isdigit(*zOrig) ){ zOrig++; }
  if( *zOrig ){
    /* we have a digit. atoi() will get as much of the number as it
    ** can. We'll run it through mprintf() to get a string. Not
    ** an efficient way to do it, but effective.
    */
    return mprintf("%d", atoi(zOrig));
  }
  return "";
}

/*
** Remove blank lines from the beginning of a string and
** all whitespace from the end. Removes whitespace preceeding a NL,
** which also converts any CRNL sequence into a single NL.
*/
char *remove_blank_lines(const char *zOrig){
  int i, j, n;
  char *z;
  for(i=j=0; isspace(zOrig[i]); i++){ if( zOrig[i]=='\n' ) j = i+1; }
  n = strlen(&zOrig[j]);
  while( n>0 && isspace(zOrig[j+n-1]) ){ n--; }
  z = mprintf("%.*s", n, &zOrig[j]);
  for(i=j=0; z[i]; i++){
    if( z[i+1]=='\n' && z[i]!='\n' && isspace(z[i]) ){
      z[j] = z[i];
      while(isspace(z[j]) && z[j] != '\n' ){ j--; }
      j++;
      continue;
    }

    z[j++] = z[i];
  }
  z[j] = 0;
  return z;
}


/*********************************************************************/

/*
** This is the SQLite authorizer callback used to make sure that the
** SQL statements entered by users do not try to do anything untoward.
** If anything suspicious is tried, set *(char**)pError to an error
** message obtained from malloc.
*/
int report_query_authorizer(
  void *pError,
  int code,
  const char *zArg1,
  const char *zArg2,
  const char *zArg3,
  const char *zArg4
){
  int rc = SQLITE_OK;
  if( *(char**)pError ){
    /* We've already seen an error.  No need to continue. */
    return SQLITE_OK;
  }
  switch( code ){
    case SQLITE_SELECT:
    case SQLITE_FUNCTION: {
      break;
    }
    case SQLITE_READ: {
      static const char *azAllowed[] = {
         "ticket",
         "blob",
         "filename",
         "mlink",
         "plink",
         "event",
         "tag",
         "tagxref",
      };
      int i;
      for(i=0; i<sizeof(azAllowed)/sizeof(azAllowed[0]); i++){
        if( strcasecmp(zArg1, azAllowed[i])==0 ) break;
      }
      if( i>=sizeof(azAllowed)/sizeof(azAllowed[0]) ){
        *(char**)pError = mprintf("access to table \"%s\" is restricted",zArg1);
        rc = SQLITE_DENY;
      }else if( !g.okRdAddr && strncmp(zArg2, "private_", 8)==0 ){
        rc = SQLITE_IGNORE;
      }
      break;
    }
    default: {
      *(char**)pError = mprintf("only SELECT statements are allowed");
      rc = SQLITE_DENY;
      break;
    }
  }
  return rc;
}


/*
** Check the given SQL to see if is a valid query that does not
** attempt to do anything dangerous.  Return 0 on success and a
** pointer to an error message string (obtained from malloc) if
** there is a problem.
*/
char *verify_sql_statement(char *zSql){
  int i;
  char *zErr = 0;
  const char *zTail;
  sqlite3_stmt *pStmt;
  int rc;

  /* First make sure the SQL is a single query command by verifying that
  ** the first token is "SELECT" and that there are no unquoted semicolons.
  */
  for(i=0; isspace(zSql[i]); i++){}
  if( strncasecmp(&zSql[i],"select",6)!=0 ){
    return mprintf("The SQL must be a SELECT statement");
  }
  for(i=0; zSql[i]; i++){
    if( zSql[i]==';' ){
      int bad;
      int c = zSql[i+1];
      zSql[i+1] = 0;
      bad = sqlite3_complete(zSql);
      zSql[i+1] = c;
      if( bad ){
        /* A complete statement basically means that an unquoted semi-colon
        ** was found. We don't actually check what's after that.
        */
        return mprintf("Semi-colon detected! "
                       "Only a single SQL statement is allowed");
      }
    }
  }
  
  /* Compile the statement and check for illegal accesses or syntax errors. */
  sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr);
  rc = sqlite3_prepare(g.db, zSql, -1, &pStmt, &zTail);
  if( rc!=SQLITE_OK ){
    zErr = mprintf("Syntax error: %s", sqlite3_errmsg(g.db));
  }
  if( pStmt ){
    sqlite3_finalize(pStmt);
  }
  sqlite3_set_authorizer(g.db, 0, 0);
  return zErr;
}

/*
** WEBPAGE: /rptsql
*/
void view_see_sql(void){
  int rn;
  const char *zTitle;
  const char *zSQL;
  const char *zOwner;
  const char *zClrKey;
  Stmt q;

  login_check_credentials();
  if( !g.okTktFmt ){
    login_needed();
    return;
  }
  rn = atoi(PD("rn","0"));
  db_prepare(&q, "SELECT title, sqlcode, owner, cols "
                   "FROM reportfmt WHERE rn=%d",rn);
  style_header("SQL For Report Format Number %d", rn);
  if( db_step(&q)!=SQLITE_ROW ){
    @ <p>Unknown report number: %d(rn)</p>
    style_footer();
    return;
  }
  zTitle = db_column_text(&q, 0);
  zSQL = db_column_text(&q, 1);
  zOwner = db_column_text(&q, 2);
  zClrKey = db_column_text(&q, 3);
  @ <table cellpadding=0 cellspacing=0 border=0>
  @ <tr><td valign="top" align="right">Title:</td><td width=15></td>
  @ <td colspan="3">%h(zTitle)</td></tr>
  @ <tr><td valign="top" align="right">Owner:</td><td></td>
  @ <td colspan="3">%h(zOwner)</td></tr>
  @ <tr><td valign="top" align="right">SQL:</td><td></td>
  @ <td valign="top"><pre>
  @ %h(zSQL)
  @ </pre></td>
  @ <td width=15></td><td valign="top">
  output_color_key(zClrKey, 0, "border=0 cellspacing=0 cellpadding=3");
  @ </td>
  @ </tr></table>
  report_format_hints();
  style_footer();
}

/*
** WEBPAGE: /rptnew
** WEBPAGE: /rptedit
*/
void view_edit(void){
  int rn;
  const char *zTitle;
  const char *z;
  const char *zOwner;
  const char *zClrKey;
  char *zSQL;
  char *zErr = 0;

  login_check_credentials();
  if( !g.okTktFmt ){
    login_needed();
    return;
  }
  /*view_add_functions(0);*/
  rn = atoi(PD("rn","0"));
  zTitle = P("t");
  zOwner = PD("w",g.zLogin);
  z = P("s");
  zSQL = z ? trim_string(z) : 0;
  zClrKey = trim_string(PD("k",""));
  if( rn>0 && P("del2") ){
    login_verify_csrf_secret();
    db_multi_exec("DELETE FROM reportfmt WHERE rn=%d", rn);
    cgi_redirect("reportlist");
    return;
  }else if( rn>0 && P("del1") ){
    zTitle = db_text(0, "SELECT title FROM reportfmt "
                         "WHERE rn=%d", rn);
    if( zTitle==0 ) cgi_redirect("reportlist");

    style_header("Are You Sure?");
    @ <form action="rptedit" method="post">
    @ <p>You are about to delete all traces of the report
    @ <strong>%h(zTitle)</strong> from
    @ the database.  This is an irreversible operation.  All records
    @ related to this report will be removed and cannot be recovered.</p>
    @
    @ <input type="hidden" name="rn" value="%d(rn)">
    login_insert_csrf_secret();
    @ <input type="submit" name="del2" value="Delete The Report">
    @ <input type="submit" name="can" value="Cancel">
    @ </form>
    style_footer();
    return;
  }else if( P("can") ){
    /* user cancelled */
    cgi_redirect("reportlist");
    return;
  }
  if( zTitle && zSQL ){
    if( zSQL[0]==0 ){
      zErr = "Please supply an SQL query statement";
    }else if( (zTitle = trim_string(zTitle))[0]==0 ){
      zErr = "Please supply a title"; 
    }else{
      zErr = verify_sql_statement(zSQL);
    }
    if( zErr==0 ){
      login_verify_csrf_secret();
      if( rn>0 ){
        db_multi_exec("UPDATE reportfmt SET title=%Q, sqlcode=%Q,"
                      " owner=%Q, cols=%Q WHERE rn=%d",
           zTitle, zSQL, zOwner, zClrKey, rn);
      }else{
        db_multi_exec("INSERT INTO reportfmt(title,sqlcode,owner,cols) "
           "VALUES(%Q,%Q,%Q,%Q)",
           zTitle, zSQL, zOwner, zClrKey);
        rn = db_last_insert_rowid();
      }
      cgi_redirect(mprintf("rptview?rn=%d", rn));
      return;
    }
  }else if( rn==0 ){
    zTitle = "";
    zSQL = ticket_report_template();
    zClrKey = ticket_key_template();
  }else{
    Stmt q;
    db_prepare(&q, "SELECT title, sqlcode, owner, cols "
                     "FROM reportfmt WHERE rn=%d",rn);
    if( db_step(&q)==SQLITE_ROW ){
      zTitle = db_column_malloc(&q, 0);
      zSQL = db_column_malloc(&q, 1);
      zOwner = db_column_malloc(&q, 2);
      zClrKey = db_column_malloc(&q, 3);
    }
    db_finalize(&q);
    if( P("copy") ){
      rn = 0;
      zTitle = mprintf("Copy Of %s", zTitle);
      zOwner = g.zLogin;
    }
  }
  if( zOwner==0 ) zOwner = g.zLogin;
  style_submenu_element("Cancel", "Cancel", "reportlist");
  if( rn>0 ){
    style_submenu_element("Delete", "Delete", "rptedit?rn=%d&amp;del1=1", rn);
  }
  style_header(rn>0 ? "Edit Report Format":"Create New Report Format");
  if( zErr ){
    @ <blockquote class="reportError">%h(zErr)</blockquote>
  }
  @ <form action="rptedit" method="post"><div>
  @ <input type="hidden" name="rn" value="%d(rn)" />
  @ <p>Report Title:<br />
  @ <input type="text" name="t" value="%h(zTitle)" size="60" /></p>
  @ <p>Enter a complete SQL query statement against the "TICKET" table:<br />
  @ <textarea name="s" rows="20" cols="80">%h(zSQL)</textarea>
  @ </p>
  login_insert_csrf_secret();
  if( g.okAdmin ){
    @ <p>Report owner:
    @ <input type="text" name="w" size="20" value="%h(zOwner)" />
    @ </p>
  } else {
    @ <input type="hidden" name="w" value="%h(zOwner)" />
  }
  @ <p>Enter an optional color key in the following box.  (If blank, no
  @ color key is displayed.)  Each line contains the text for a single
  @ entry in the key.  The first token of each line is the background
  @ color for that line.<br />
  @ <textarea name="k" rows="8" cols="50">%h(zClrKey)</textarea>
  @ </p>
  if( !g.okAdmin && strcmp(zOwner,g.zLogin)!=0 ){
    @ <p>This report format is owned by %h(zOwner).  You are not allowed
    @ to change it.</p>
    @ </form>
    report_format_hints();
    style_footer();
    return;
  }
  @ <input type="submit" value="Apply Changes" />
  if( rn>0 ){
    @ <input type="submit" value="Delete This Report" name="del1" />
  }
  @ </div></form>
  report_format_hints();
  style_footer();
}

/*
** Output a bunch of text that provides information about report
** formats
*/
static void report_format_hints(void){
  char *zSchema;
  zSchema = db_text(0,"SELECT sql FROM sqlite_master WHERE name='ticket'");
  if( zSchema==0 ){
    zSchema = db_text(0,"SELECT sql FROM repository.sqlite_master"
                        " WHERE name='ticket'");
  }
  @ <hr /><h3>TICKET Schema</h3>
  @ <blockquote><pre>
  @ %h(zSchema)
  @ </pre></blockquote>
  @ <h3>Notes</h3>
  @ <ul>
  @ <li><p>The SQL must consist of a single SELECT statement</p></li>
  @
  @ <li><p>If a column of the result set is named "#" then that column
  @ is assumed to hold a ticket number.  A hyperlink will be created from
  @ that column to a detailed view of the ticket.</p></li>
  @
  @ <li><p>If a column of the result set is named "bgcolor" then the content
  @ of that column determines the background color of the row.</p></li>
  @
  @ <li><p>The first column whose name begins with underscore ("_") and all
  @ subsequent columns are shown on their own rows in the table.  This might
  @ be useful for displaying the description of tickets.
  @ </p></li>
  @
  @ <li><p>The query can join other tables in the database besides TICKET.
  @ </p></li>
  @ </ul>
  @
  @ <h3>Examples</h3>
  @ <p>In this example, the first column in the result set is named
  @ "bgcolor".  The value of this column is not displayed.  Instead, it
  @ selects the background color of each row based on the TICKET.STATUS
  @ field of the database.  The color key at the right shows the various
  @ color codes.</p>
  @ <table class="rpteditex">
  @ <tr style="background-color:#f2dcdc;"><td class="rpteditex">new or active</td></tr>
  @ <tr style="background-color:#e8e8bd;"><td class="rpteditex">review</td></tr>
  @ <tr style="background-color:#cfe8bd;"><td class="rpteditex">fixed</td></tr>
  @ <tr style="background-color:#bde5d6;"><td class="rpteditex">tested</td></tr>
  @ <tr style="background-color:#cacae5;"><td class="rpteditex">defer</td></tr>
  @ <tr style="background-color:#c8c8c8;"><td class="rpteditex">closed</td></tr>
  @ </table>
  @ <blockquote><pre>
  @ SELECT
  @   CASE WHEN status IN ('new','active') THEN '#f2dcdc'
  @        WHEN status='review' THEN '#e8e8bd'
  @        WHEN status='fixed' THEN '#cfe8bd'
  @        WHEN status='tested' THEN '#bde5d6'
  @        WHEN status='defer' THEN '#cacae5'
  @        ELSE '#c8c8c8' END as 'bgcolor',
  @   tn AS '#',
  @   type AS 'Type',
  @   status AS 'Status',
  @   sdate(origtime) AS 'Created',
  @   owner AS 'By',
  @   subsystem AS 'Subsys',
  @   sdate(changetime) AS 'Changed',
  @   assignedto AS 'Assigned',
  @   severity AS 'Svr',
  @   priority AS 'Pri',
  @   title AS 'Title'
  @ FROM ticket
  @ </pre></blockquote>
  @ <p>To base the background color on the TICKET.PRIORITY or
  @ TICKET.SEVERITY fields, substitute the following code for the
  @ first column of the query:</p>
  @ <table class="rpteditex">
  @ <tr style="background-color:#f2dcdc;"><td class="rpteditex">1</td></tr>
  @ <tr style="background-color:#e8e8bd;"><td class="rpteditex">2</td></tr>
  @ <tr style="background-color:#cfe8bd;"><td class="rpteditex">3</td></tr>
  @ <tr style="background-color:#cacae5;"><td class="rpteditex">4</td></tr>
  @ <tr style="background-color:#c8c8c8;"><td class="rpteditex">5</td></tr>
  @ </table>
  @ <blockquote><pre>
  @ SELECT
  @   CASE priority WHEN 1 THEN '#f2dcdc'
  @        WHEN 2 THEN '#e8e8bd'
  @        WHEN 3 THEN '#cfe8bd'
  @        WHEN 4 THEN '#cacae5'
  @        ELSE '#c8c8c8' END as 'bgcolor',
  @ ...
  @ FROM ticket
  @ </pre></blockquote>
#if 0
  @ <p>You can, of course, substitute different colors if you choose.
  @ Here is a palette of suggested background colors:</p>
  @ <blockquote>
  @ <table border=1 cellspacing=0 width=300>
  @ <tr><td align="center" bgcolor="#ffbdbd">#ffbdbd</td>
  @     <td align="center" bgcolor="#f2dcdc">#f2dcdc</td></tr>
  @ <tr><td align="center" bgcolor="#ffffbd">#ffffbd</td>
  @     <td align="center" bgcolor="#e8e8bd">#e8e8bd</td></tr>
  @ <tr><td align="center" bgcolor="#c0ebc0">#c0ebc0</td>
  @     <td align="center" bgcolor="#cfe8bd">#cfe8bd</td></tr>
  @ <tr><td align="center" bgcolor="#c0c0f4">#c0c0f4</td>
  @     <td align="center" bgcolor="#d6d6e8">#d6d6e8</td></tr>
  @ <tr><td align="center" bgcolor="#d0b1ff">#d0b1ff</td>
  @     <td align="center" bgcolor="#d2c0db">#d2c0db</td></tr>
  @ <tr><td align="center" bgcolor="#bbbbbb">#bbbbbb</td>
  @     <td align="center" bgcolor="#d0d0d0">#d0d0d0</td></tr>
  @ </table>
  @ </blockquote>
#endif
  @ <p>To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include
  @ them as the last two columns of the result set and given them names
  @ that begin with an underscore.  Like this:</p>
  @ <blockquote><pre>
  @  SELECT
  @    tn AS '#',
  @    type AS 'Type',
  @    status AS 'Status',
  @    sdate(origtime) AS 'Created',
  @    owner AS 'By',
  @    subsystem AS 'Subsys',
  @    sdate(changetime) AS 'Changed',
  @    assignedto AS 'Assigned',
  @    severity AS 'Svr',
  @    priority AS 'Pri',
  @    title AS 'Title',
  @    description AS '_Description',   -- When the column name begins with '_'
  @    remarks AS '_Remarks'            -- the data is shown on a separate row.
  @  FROM ticket
  @ </pre></blockquote>
  @
  @ <p>Or, to see part of the description on the same row, use the
  @ <b>wiki()</b> function with some string manipulation. Using the
  @ <b>tkt()</b> function on the ticket number will also generate a linked
  @ field, but without the extra <i>edit</i> column:
  @ </p>
  @ <blockquote><pre>
  @  SELECT
  @    tkt(tn) AS '',
  @    title AS 'Title',
  @    wiki(substr(description,0,80)) AS 'Description'
  @  FROM ticket
  @ </pre></blockquote>
  @
}

/*
** The state of the report generation.
*/
struct GenerateHTML {
  int rn;          /* Report number */
  int nCount;      /* Row number */
  int nCol;        /* Number of columns */
  int isMultirow;  /* True if multiple table rows per query result row */
  int iNewRow;     /* Index of first column that goes on separate row */
  int iBg;         /* Index of column that defines background color */
};

/*
** The callback function for db_query
*/
static int generate_html(
  void *pUser,     /* Pointer to output state */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **azName    /* Names of the columns */
){
  struct GenerateHTML *pState = (struct GenerateHTML*)pUser;
  int i;
  const char *zTid;  /* Ticket UUID.  (value of column named '#') */
  int rn;            /* Report number */
  char *zBg = 0;     /* Use this background color */
  char zPage[30];    /* Text version of the ticket number */

  /* Get the report number
  */
  rn = pState->rn;

  /* Do initialization
  */
  if( pState->nCount==0 ){
    /* Turn off the authorizer.  It is no longer doing anything since the
    ** query has already been prepared.
    */
    sqlite3_set_authorizer(g.db, 0, 0);

    /* Figure out the number of columns, the column that determines background
    ** color, and whether or not this row of data is represented by multiple
    ** rows in the table.
    */
    pState->nCol = 0;
    pState->isMultirow = 0;
    pState->iNewRow = -1;
    pState->iBg = -1;
    for(i=0; i<nArg; i++){
      if( azName[i][0]=='b' && strcmp(azName[i],"bgcolor")==0 ){
        pState->iBg = i;
        continue;
      }
      if( g.okWrite && azName[i][0]=='#' ){
        pState->nCol++;
      }
      if( !pState->isMultirow ){
        if( azName[i][0]=='_' ){
          pState->isMultirow = 1;
          pState->iNewRow = i;
        }else{
          pState->nCol++;
        }
      }
    }

    /* The first time this routine is called, output a table header
    */
    @ <tr>
    zTid = 0;
    for(i=0; i<nArg; i++){
      char *zName = azName[i];
      if( i==pState->iBg ) continue;
      if( pState->iNewRow>=0 && i>=pState->iNewRow ){
        if( g.okWrite && zTid ){
          @ <th>&nbsp;</th>
          zTid = 0;
        }
        if( zName[0]=='_' ) zName++;
        @ </tr><tr><th colspan=%d(pState->nCol)>%h(zName)</th>
      }else{
        if( zName[0]=='#' ){
          zTid = zName;
        }
        @ <th>%h(zName)</th>
      }
    }
    if( g.okWrite && zTid ){
      @ <th>&nbsp;</th>
    }
    @ </tr>
  }
  if( azArg==0 ){
    @ <tr><td colspan="%d(pState->nCol)">
    @ <i>No records match the report criteria</i>
    @ </td></tr>
    return 0;
  }
  ++pState->nCount;

  /* Output the separator above each entry in a table which has multiple lines
  ** per database entry.
  */
  if( pState->iNewRow>=0 ){
    @ <tr><td colspan=%d(pState->nCol)><font size=1>&nbsp;</font></td></tr>
  }

  /* Output the data for this entry from the database
  */
  zBg = pState->iBg>=0 ? azArg[pState->iBg] : 0;
  if( zBg==0 ) zBg = "white";
  @ <tr style="background-color:%h(zBg)">
  zTid = 0;
  zPage[0] = 0;
  for(i=0; i<nArg; i++){
    char *zData;
    if( i==pState->iBg ) continue;
    zData = azArg[i];
    if( zData==0 ) zData = "";
    if( pState->iNewRow>=0 && i>=pState->iNewRow ){
      if( zTid && g.okWrite ){
        @ <td valign="top"><a href="tktedit/%h(zTid)">edit</a></td>
        zTid = 0;
      }
      if( zData[0] ){
        Blob content;
        @ </tr><tr style="background-color:%h(zBg)"><td colspan=%d(pState->nCol)>
        blob_init(&content, zData, -1);
        wiki_convert(&content, 0, 0);
        blob_reset(&content);
      }
    }else if( azName[i][0]=='#' ){
      zTid = zData;
      if( g.okHistory ){
        @ <td valign="top"><a href="tktview?name=%h(zData)">%h(zData)</a></td>
      }else{
        @ <td valign="top">%h(zData)</td>
      }
    }else if( zData[0]==0 ){
      @ <td valign="top">&nbsp;</td>
    }else{
      @ <td valign="top">
      @ %h(zData)
      @ </td>
    }
  }
  if( zTid && g.okWrite ){
    @ <td valign="top"><a href="tktedit/%h(zTid)">edit</a></td>
  }
  @ </tr>
  return 0;
}

/*
** Output the text given in the argument.  Convert tabs and newlines into
** spaces.
*/
static void output_no_tabs(const char *z){
  while( z && z[0] ){
    int i, j;
    for(i=0; z[i] && (!isspace(z[i]) || z[i]==' '); i++){}
    if( i>0 ){
      cgi_printf("%.*s", i, z);
    }
    for(j=i; isspace(z[j]); j++){}
    if( j>i ){
      cgi_printf("%*s", j-i, "");
    }
    z += j;
  }
}

/*
** Output a row as a tab-separated line of text.
*/
static int output_tab_separated(
  void *pUser,     /* Pointer to row-count integer */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **azName    /* Names of the columns */
){
  int *pCount = (int*)pUser;
  int i;

  if( *pCount==0 ){
    for(i=0; i<nArg; i++){
      output_no_tabs(azName[i]);
      cgi_printf("%c", i<nArg-1 ? '\t' : '\n');
    }
  }
  ++*pCount;
  for(i=0; i<nArg; i++){
    output_no_tabs(azArg[i]);
    cgi_printf("%c", i<nArg-1 ? '\t' : '\n');
  }
  return 0;
}

/*
** Generate HTML that describes a color key.
*/
void output_color_key(const char *zClrKey, int horiz, char *zTabArgs){
  int i, j, k;
  char *zSafeKey, *zToFree;
  while( isspace(*zClrKey) ) zClrKey++;
  if( zClrKey[0]==0 ) return;
  @ <table %s(zTabArgs)>
  if( horiz ){
    @ <tr>
  }
  zToFree = zSafeKey = mprintf("%h", zClrKey);
  while( zSafeKey[0] ){
    while( isspace(*zSafeKey) ) zSafeKey++;
    for(i=0; zSafeKey[i] && !isspace(zSafeKey[i]); i++){}
    for(j=i; isspace(zSafeKey[j]); j++){}
    for(k=j; zSafeKey[k] && zSafeKey[k]!='\n' && zSafeKey[k]!='\r'; k++){}
    if( !horiz ){
      cgi_printf("<tr style=\"background-color: %.*s;\"><td>%.*s</td></tr>\n",
        i, zSafeKey, k-j, &zSafeKey[j]);
    }else{
      cgi_printf("<td style=\"background-color: %.*s;\">%.*s</td>\n",
        i, zSafeKey, k-j, &zSafeKey[j]);
    }
    zSafeKey += k;
  }
  free(zToFree);
  if( horiz ){
    @ </tr>
  }
  @ </table>
}


/*
** WEBPAGE: /rptview
**
** Generate a report.  The rn query parameter is the report number
** corresponding to REPORTFMT.RN.  If the tablist query parameter exists,
** then the output consists of lines of tab-separated fields instead of
** an HTML table.
*/
void rptview_page(void){
  int count = 0;
  int rn;
  char *zSql;
  char *zTitle;
  char *zOwner;
  char *zClrKey;
  int tabs;
  Stmt q;
  char *zErr1 = 0;
  char *zErr2 = 0;

  login_check_credentials();
  if( !g.okRdTkt ){ login_needed(); return; }
  rn = atoi(PD("rn","0"));
  if( rn==0 ){
    cgi_redirect("reportlist");
    return;
  }
  tabs = P("tablist")!=0;
  /* view_add_functions(tabs); */
  db_prepare(&q,
    "SELECT title, sqlcode, owner, cols FROM reportfmt WHERE rn=%d", rn);
  if( db_step(&q)!=SQLITE_ROW ){
    cgi_redirect("reportlist");
    return;
  }
  zTitle = db_column_malloc(&q, 0);
  zSql = db_column_malloc(&q, 1);
  zOwner = db_column_malloc(&q, 2);
  zClrKey = db_column_malloc(&q, 3);
  db_finalize(&q);

  if( P("order_by") ){
    /*
    ** If the user wants to do a column sort, wrap the query into a sub
    ** query and then sort the results. This is a whole lot easier than
    ** trying to insert an ORDER BY into the query itself, especially
    ** if the query is already ordered.
    */
    int nField = atoi(P("order_by"));
    if( nField > 0 ){
      const char* zDir = PD("order_dir","");
      zDir = !strcmp("ASC",zDir) ? "ASC" : "DESC";
      zSql = mprintf("SELECT * FROM (%s) ORDER BY %d %s", zSql, nField, zDir);
    }
  }

  count = 0;
  if( !tabs ){
    struct GenerateHTML sState;

    db_multi_exec("PRAGMA empty_result_callbacks=ON");
    style_submenu_element("Raw", "Raw", 
      "rptview?tablist=1&amp;%h", PD("QUERY_STRING",""));
    if( g.okAdmin 
       || (g.okTktFmt && g.zLogin && zOwner && strcmp(g.zLogin,zOwner)==0) ){
      style_submenu_element("Edit", "Edit", "rptedit?rn=%d", rn);
    }
    if( g.okTktFmt ){
      style_submenu_element("SQL", "SQL", "rptsql?rn=%d",rn);
    }
    if( g.okNewTkt ){
      style_submenu_element("New Ticket", "Create a new ticket",
        "%s/tktnew", g.zTop);
    }
    style_header(zTitle);
    output_color_key(zClrKey, 1, 
        "border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\"");
    @ <table border="1" cellpadding="2" cellspacing="0" class="report">
    sState.rn = rn;
    sState.nCount = 0;
    sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr1);
    sqlite3_exec(g.db, zSql, generate_html, &sState, &zErr2);
    sqlite3_set_authorizer(g.db, 0, 0);
    @ </table>
    if( zErr1 ){
      @ <p class="reportError">Error: %h(zErr1)</p>
    }else if( zErr2 ){
      @ <p class="reportError">Error: %h(zErr2)</p>
    }
    style_footer();
  }else{
    sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr1);
    sqlite3_exec(g.db, zSql, output_tab_separated, &count, &zErr2);
    sqlite3_set_authorizer(g.db, 0, 0);
    cgi_set_content_type("text/plain");
  }
}

/*
** report number for full table ticket export
*/
static const char zFullTicketRptRn[] = "0";

/*
** report title for full table ticket export
*/
static const char zFullTicketRptTitle[] = "full ticket export";

/*
** show all reports, which can be used for ticket show.
** Output is written to stdout as tab delimited table
*/
void rpt_list_reports(void){
  Stmt q;
  char const aRptOutFrmt[] = "%s\t%s\n";

  printf("Available reports:\n");
  printf(aRptOutFrmt,"report number","report title");
  printf(aRptOutFrmt,zFullTicketRptRn,zFullTicketRptTitle);
  db_prepare(&q,"SELECT rn,title FROM reportfmt ORDER BY rn");
  while( db_step(&q)==SQLITE_ROW ){
    const char *zRn = db_column_text(&q, 0);
    const char *zTitle = db_column_text(&q, 1);

    printf(aRptOutFrmt,zRn,zTitle);
  }
  db_finalize(&q);
}

/*
** user defined separator used by ticket show command
*/
static const char *zSep = 0;

/*
** select the quoting algorithm for "ticket show"
*/
#if INTERFACE
typedef enum eTktShowEnc { tktNoTab=0, tktFossilize=1 } tTktShowEncoding;
#endif
static tTktShowEncoding tktEncode = tktNoTab;

/*
** Output the text given in the argument.  Convert tabs and newlines into
** spaces.
*/
static void output_no_tabs_file(const char *z){
  switch( tktEncode ){
    case tktFossilize:
      { char *zFosZ;

        if( z && *z ){
          zFosZ = fossilize(z,-1);
          printf("%s",zFosZ);
          free(zFosZ);
        }
        break;
      }
    default:
      while( z && z[0] ){
        int i, j;
        for(i=0; z[i] && (!isspace(z[i]) || z[i]==' '); i++){}
        if( i>0 ){
          printf("%.*s", i, z);
        }
        for(j=i; isspace(z[j]); j++){}
        if( j>i ){
          printf("%*s", j-i, "");
        }
        z += j;
      }
      break; 
  }
}

/*
** Output a row as a tab-separated line of text.
*/
int output_separated_file(
  void *pUser,     /* Pointer to row-count integer */
  int nArg,        /* Number of columns in this result row */
  char **azArg,    /* Text of data in all columns */
  char **azName    /* Names of the columns */
){
  int *pCount = (int*)pUser;
  int i;

  if( *pCount==0 ){
    for(i=0; i<nArg; i++){
      output_no_tabs_file(azName[i]);
      printf("%s", i<nArg-1 ? (zSep?zSep:"\t") : "\n");
    }
  }
  ++*pCount;
  for(i=0; i<nArg; i++){
    output_no_tabs_file(azArg[i]);
    printf("%s", i<nArg-1 ? (zSep?zSep:"\t") : "\n");
  }
  return 0;
}

/*
** Generate a report.  The rn query parameter is the report number.
** The output is written to stdout as flat file. The zFilter paramater
** is a full WHERE-condition.
*/
void rptshow( 
    const char *zRep,
    const char *zSepIn,
    const char *zFilter,
    tTktShowEncoding enc
){
  Stmt q;
  char *zSql;
  const char *zTitle;
  const char *zOwner;
  const char *zClrKey;
  char *zErr1 = 0;
  char *zErr2 = 0;
  int count = 0;
  int rn;

  if (!zRep || !strcmp(zRep,zFullTicketRptRn) || !strcmp(zRep,zFullTicketRptTitle) ){
    zTitle = zFullTicketRptTitle;
    zSql = "SELECT * FROM ticket";
    zOwner = g.zLogin;
    zClrKey = "";
  }else{
    rn = atoi(zRep);
    if( rn ){
      db_prepare(&q,
       "SELECT title, sqlcode, owner, cols FROM reportfmt WHERE rn=%d", rn);
    }else{
      db_prepare(&q,
       "SELECT title, sqlcode, owner, cols FROM reportfmt WHERE title='%s'", zRep);
    }
    if( db_step(&q)!=SQLITE_ROW ){
      db_finalize(&q);
      rpt_list_reports();
      fossil_fatal("unkown report format(%s)!",zRep);
    }
    zTitle = db_column_malloc(&q, 0);
    zSql = db_column_malloc(&q, 1);
    zOwner = db_column_malloc(&q, 2);
    zClrKey = db_column_malloc(&q, 3);
    db_finalize(&q);
  }
  if( zFilter ){
    zSql = mprintf("SELECT * FROM (%s) WHERE %s",zSql,zFilter);
  }
  count = 0;
  tktEncode = enc;
  zSep = zSepIn;
  sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr1);
  sqlite3_exec(g.db, zSql, output_separated_file, &count, &zErr2);
  sqlite3_set_authorizer(g.db, 0, 0);
  if( zFilter ){
    free(zSql);
  }
}