diff options
Diffstat (limited to 'src/report.c')
| -rw-r--r-- | src/report.c | 1403 |
1 files changed, 1403 insertions, 0 deletions
diff --git a/src/report.c b/src/report.c new file mode 100644 index 0000000..99530fb --- /dev/null +++ b/src/report.c @@ -0,0 +1,1403 @@ +/* +** 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 <time.h> +#include "report.h" +#include <assert.h> + +/* Forward references to static routines */ +static void report_format_hints(void); + +#ifndef SQLITE_RECURSIVE +# define SQLITE_RECURSIVE 33 +#endif + +/* Settings that can be used to control ticket reports */ +/* +** SETTING: ticket-default-report width=80 +** If this setting has a string value, then when the ticket +** search page query is blank, the report with this title is shown. +** If the setting is blank (default), then no report is shown. +*/ + +/* +** WEBPAGE: reportlist +** +** Main menu for Tickets. +*/ +void view_list(void){ + const char *zScript; + Blob ril; /* Report Item List */ + Stmt q; + int rn = 0; + char *defaultReport = db_get("ticket-default-report", 0); + + login_check_credentials(); + if( !g.perm.RdTkt && !g.perm.NewTkt ){ + login_needed(g.anon.RdTkt || g.anon.NewTkt); + return; + } + style_header("Ticket Main Menu"); + ticket_standard_submenu(T_ALL_BUT(T_REPLIST)); + 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.perm.TktFmt ){ + continue; + } + rn = db_column_int(&q, 0); + blob_appendf(&ril, "<li>"); + if( zTitle[0] == '_' ){ + blob_appendf(&ril, "%s", zTitle); + } else { + blob_appendf(&ril, "%z%h</a>", href("%R/rptview/%d", rn), zTitle); + } + blob_appendf(&ril, " "); + if( g.perm.Write && zOwner && zOwner[0] ){ + blob_appendf(&ril, "(by <i>%h</i>) ", zOwner); + } + if( g.perm.TktFmt ){ + blob_appendf(&ril, "[%zcopy</a>] ", + href("%R/rptedit/%d?copy=1", rn)); + } + if( g.perm.Admin + || (g.perm.WrTkt && zOwner && fossil_strcmp(g.zLogin,zOwner)==0) + ){ + blob_appendf(&ril, "[%zedit</a>]", + href("%R/rptedit/%d", rn)); + } + if( g.perm.TktFmt ){ + blob_appendf(&ril, "[%zsql</a>]", + href("%R/rptsql/%d", rn)); + } + if( fossil_strcmp(zTitle, defaultReport)==0 ){ + blob_appendf(&ril, " ← default"); + } + blob_appendf(&ril, "</li>\n"); + } + db_finalize(&q); + + Th_Store("report_items", blob_str(&ril)); + + Th_Render(zScript); + + blob_reset(&ril); + if( g.thTrace ) Th_Trace("END_REPORTLIST<br>\n", -1); + + style_finish_page(); +} + +/* +** Remove whitespace from both ends of a string. +*/ +char *trim_string(const char *zOrig){ + int i; + while( fossil_isspace(*zOrig) ){ zOrig++; } + i = strlen(zOrig); + while( i>0 && fossil_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 && !fossil_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 preceding a LF, +** which also converts any CRLF sequence into a single LF. +*/ +char *remove_blank_lines(const char *zOrig){ + int i, j, n; + char *z; + for(i=j=0; fossil_isspace(zOrig[i]); i++){ if( zOrig[i]=='\n' ) j = i+1; } + n = strlen(&zOrig[j]); + while( n>0 && fossil_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' && fossil_isspace(z[i]) ){ + z[j] = z[i]; + while(fossil_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. +** +** Use the "fossil test-db-prepare --auth-report SQL" command to perform +** manual testing of this authorizer. +*/ +static 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_DENY; + } + switch( code ){ + case SQLITE_SELECT: + case SQLITE_RECURSIVE: + case SQLITE_FUNCTION: { + break; + } + case SQLITE_READ: { + static const char *const azAllowed[] = { + "backlink", + "blob", + "event", + "filename", + "json_each", + "json_tree", + "mlink", + "plink", + "tag", + "tagxref", + "ticket", + "ticketchng", + "unversioned", + }; + int lwr = 0; + int upr = count(azAllowed) - 1; + int cmp = 0; + if( zArg1==0 ){ + /* Some legacy versions of SQLite will sometimes send spurious + ** READ authorizations that have no table name. These can be + ** ignored. */ + rc = SQLITE_IGNORE; + break; + } + while( lwr<=upr ){ + int i = (lwr+upr)/2; + cmp = fossil_stricmp(zArg1, azAllowed[i]); + if( cmp<0 ){ + upr = i - 1; + }else if( cmp>0 ){ + lwr = i + 1; + }else{ + break; + } + } + if( cmp ){ + /* Always ok to access tables whose names begin with "fx_" */ + cmp = sqlite3_strnicmp(zArg1, "fx_", 3); + } + if( cmp ){ + *(char**)pError = mprintf("access to table \"%s\" is restricted",zArg1); + rc = SQLITE_DENY; + }else if( !g.perm.RdAddr && sqlite3_strnicmp(zArg2, "private_", 8)==0 ){ + rc = SQLITE_IGNORE; + } + break; + } + default: { + *(char**)pError = mprintf("only SELECT statements are allowed"); + rc = SQLITE_DENY; + break; + } + } + return rc; +} + +/* +** Make sure the reportfmt table is up-to-date. It should contain +** the "jx" column (as of version 2.21). If it does not, add it. +** +** The "jx" column is intended to hold a JSON object containing optional +** key-value pairs. +*/ +void report_update_reportfmt_table(void){ + if( db_table_has_column("repository","reportfmt","jx")==0 ){ + db_multi_exec("ALTER TABLE repository.reportfmt" + " ADD COLUMN jx TEXT DEFAULT '{}';"); + } +} + +/* +** Activate the ticket report query authorizer. Must be followed by an +** eventual call to report_unrestrict_sql(). +*/ +void report_restrict_sql(char **pzErr){ + db_set_authorizer(report_query_authorizer,(void*)pzErr,"Ticket-Report"); + sqlite3_limit(g.db, SQLITE_LIMIT_VDBE_OP, 10000); +} +void report_unrestrict_sql(void){ + db_clear_authorizer(); +} + + +/* +** 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" or "WITH" and that there are no unquoted + ** semicolons. + */ + for(i=0; fossil_isspace(zSql[i]); i++){} + if( fossil_strnicmp(&zSql[i], "select", 6)!=0 + && fossil_strnicmp(&zSql[i], "with", 4)!=0 ){ + return mprintf("The SQL must be a SELECT or WITH 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. */ + report_restrict_sql(&zErr); + rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, &zTail); + if( rc!=SQLITE_OK ){ + zErr = mprintf("Syntax error: %s", sqlite3_errmsg(g.db)); + } + if( !sqlite3_stmt_readonly(pStmt) ){ + zErr = mprintf("SQL must not modify the database"); + } + if( pStmt ){ + sqlite3_finalize(pStmt); + } + report_unrestrict_sql(); + return zErr; +} + +/* +** Get a report number from query parameters. This can be done in various +** ways: +** +** (1) (legacy) rn=NNN where NNN is the reportfmt.rn integer primary key. +** +** (2) name=NNN where NNN is the rn. +** +** (3) name=TAG where TAG matches reportfmt.jx->>tag +** +** Regardless of how the report is specified, return the primary key, rn. +** Return 0 if not found. +*/ +static int report_number(void){ + int rn; + const char *zName; + char *zEnd; + + /* Case (1) */ + rn = atoi(PD("rn","0")); + if( rn>0 ) return rn; + + zName = P("name"); + if( zName==0 || zName[0]==0 ) return 0; + if( fossil_isdigit(zName[0]) + && (rn = strtol(zName, &zEnd, 10))>0 + && zEnd[0]==0 + ){ + /* Case 2 */ + return rn; + } + + rn = db_int(0, "SELECT rn FROM reportfmt WHERE jx->>'tag'==%Q", zName); + return rn; +} + +/* +** WEBPAGE: rptsql +** URL: /rptsql/N +** +** Display the SQL query used to generate a ticket report. The N value +** is either the report number of a report tag. +*/ +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.perm.TktFmt ){ + login_needed(g.anon.TktFmt); + return; + } + rn = report_number(); + db_prepare(&q, "SELECT title, sqlcode, owner, cols " + "FROM reportfmt WHERE rn=%d",rn); + style_set_current_feature("report"); + style_header("SQL For Report Format Number %d", rn); + if( db_step(&q)!=SQLITE_ROW ){ + @ <p>Unknown report number: %d(rn)</p> + style_finish_page(); + db_finalize(&q); + 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> + @ <code class="language-sql">%h(zSQL)</code> + @ </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_finish_page(); + db_finalize(&q); +} + +/* +** WEBPAGE: rptnew +** WEBPAGE: rptedit +** +** Create (/rptnew) or edit (/rptedit) a ticket report format. +** Query parameters: +** +** name=N Ticket report number or tag. +** rn=N Ticket report number (legacy). +** ^^^-- one of the two previous is required. +** t=TITLE Title of the report format +** w=USER Owner of the report format +** s=SQL SQL text used to implement the report +** k=KEY Color key +** d=DESC Optional descriptive text +** m=MIMETYPE Mimetype for DESC +** x=TAG Symbolic name for the report +*/ +void view_edit(void){ + int rn; + const char *zTitle; /* Title of the report */ + const char *z; + const char *zOwner; /* Owner of the report */ + const char *zClrKey; /* Color key - used to add colors to lines */ + char *zSQL; /* The SQL text that generates the report */ + char *zErr = 0; /* An error message */ + const char *zDesc; /* Extra descriptive text about the report */ + const char *zMimetype; /* Mimetype for zDesc */ + const char *zTag; /* Symbolic name for this report */ + int dflt = P("dflt") ? 1 : 0; + + login_check_credentials(); + if( !g.perm.TktFmt ){ + login_needed(g.anon.TktFmt); + return; + } + style_set_current_feature("report"); + /*view_add_functions(0);*/ + rn = report_number(); + zTitle = P("t"); + zOwner = PD("w",g.zLogin); + z = P("s"); + zSQL = z ? trim_string(z) : 0; + zClrKey = trim_string(PD("k","")); + zDesc = trim_string(PD("d","")); + zMimetype = P("m"); + zTag = P("x"); + report_update_reportfmt_table(); + if( rn>0 && P("del2") && cgi_csrf_safe(2) ){ + db_multi_exec("DELETE FROM reportfmt WHERE rn=%d", rn); + cgi_redirect("reportlist"); + return; + }else if( rn>0 && P("del1") && cgi_csrf_safe(2) ){ + 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_finish_page(); + 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 + && db_exists("SELECT 1 FROM reportfmt WHERE title=%Q and rn<>%d", + zTitle, rn) + ){ + zErr = mprintf("There is already another report named \"%h\"", zTitle); + } + if( zErr==0 && cgi_csrf_safe(2) ){ + if( zTag && zTag[0]==0 ) zTag = 0; + if( zDesc && zDesc[0]==0 ){ zDesc = 0; zMimetype = 0; } + if( zMimetype && zMimetype[0]==0 ){ zDesc = 0; zMimetype = 0; } + if( rn>0 ){ + db_multi_exec( + "UPDATE reportfmt SET title=%Q, sqlcode=%Q," + " owner=%Q, cols=%Q, mtime=now(), " + " jx=json_patch(jx,json_object('desc',%Q,'descmt',%Q,'tag',%Q))" + " WHERE rn=%d", + zTitle, zSQL, zOwner, zClrKey, zDesc, zMimetype, zTag, rn); + }else{ + db_multi_exec( + "INSERT INTO reportfmt(title,sqlcode,owner,cols,mtime,jx) " + "VALUES(%Q,%Q,%Q,%Q,now()," + "json_object('desc',%Q,'descmt',%Q,'tag',%Q))", + zTitle, zSQL, zOwner, zClrKey, zDesc, zMimetype, zTag); + rn = db_last_insert_rowid(); + } + if( dflt ){ + db_set("ticket-default-report", zTitle, 0); + }else{ + char *defaultReport = db_get("ticket-default-report", 0); + if( fossil_strcmp(zTitle, defaultReport)==0 ){ + db_set("ticket-default-report", "", 0); + } + } + cgi_redirect(mprintf("rptview/%d", rn)); + return; + } + }else if( rn==0 ){ + zTitle = ""; + zSQL = ticket_report_template(); + zClrKey = ticket_key_template(); + }else{ + Stmt q; + int hasJx = 0; + zDesc = 0; + zMimetype = 0; + zTag = 0; + db_prepare(&q, "SELECT title, sqlcode, owner, cols, json_valid(jx) " + "FROM reportfmt WHERE rn=%d",rn); + if( db_step(&q)==SQLITE_ROW ){ + char *defaultReport = db_get("ticket-default-report", 0); + zTitle = db_column_malloc(&q, 0); + zSQL = db_column_malloc(&q, 1); + zOwner = db_column_malloc(&q, 2); + zClrKey = db_column_malloc(&q, 3); + dflt = fossil_strcmp(zTitle, defaultReport)==0; + hasJx = db_column_int(&q, 4); + } + db_finalize(&q); + if( hasJx ){ + db_prepare(&q, "SELECT jx->>'desc', jx->>'descmt', jx->>'tag'" + " FROM reportfmt WHERE rn=%d", rn); + if( db_step(&q)==SQLITE_ROW ){ + zDesc = db_column_malloc(&q, 0); + zMimetype = db_column_malloc(&q, 1); + zTag = db_column_malloc(&q, 2); + } + 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", "%R/reportlist"); + style_header("%s", 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.perm.Admin ){ + @ <p>Report owner: + @ <input type="text" name="w" size="20" value="%h(zOwner)"> + @ </p> + @ <p>Tag: + @ <input type="text" name="x" size="20" value="%h(zTag?zTag:"")"> + @ </p> + } else { + @ <input type="hidden" name="w" value="%h(zOwner)"> + if( zTag && zTag[0] ){ + @ <input type="hidden" name="x" value="%h(zTag)"> + } + } + @ <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> + + @ <p>Optional human-readable description for this report<br> + @ %z(href("%R/markup_help"))Markup style</a>: + mimetype_option_menu(zMimetype, "m"); + @ <br><textarea aria-label="Description:" name="d" class="wikiedit" \ + @ cols="80" rows="15" wrap="virtual">%h(zDesc)</textarea> + @ </p> + + @ <p><label><input type="checkbox" name="dflt" %s(dflt?"checked":"")> \ + @ Make this the default report</label></p> + if( !g.perm.Admin && fossil_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_finish_page(); + 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_finish_page(); +} + +/* +** 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_schema WHERE name='ticket'"); + if( zSchema==0 ){ + zSchema = db_text(0,"SELECT sql FROM repository.sqlite_schema" + " WHERE name='ticket'"); + } + @ <hr><h3>TICKET Schema</h3> + @ <blockquote><pre> + @ <code class="language-sql">%h(zSchema)</code> + @ </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 text of all columns prior to the first column whose name begins + @ with underscore ("_") is shown character-for-character as it appears in + @ the database. In other words, it is assumed to have a mimetype of + @ text/plain. + @ + @ <li><p>The first column whose name begins with underscore ("_") and all + @ subsequent columns are shown on their own rows in the table and with + @ wiki formatting. In other words, such rows are shown with a mimetype + @ of text/x-fossil-wiki. This is recommended for the "description" field + @ 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' -- content is rendered as wiki + @ 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 */ + int wikiFlags; /* Flags passed into wiki_convert() */ + const char *zWikiStart; /* HTML before display of multi-line wiki */ + const char *zWikiEnd; /* HTML after display of multi-line wiki */ +}; + +/* +** 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 */ + const char **azArg, /* Text of data in all columns */ + const char **azName /* Names of the columns */ +){ + struct GenerateHTML *pState = (struct GenerateHTML*)pUser; + int i; + const char *zTid; /* Ticket hash. (value of column named '#') */ + const char *zBg = 0; /* Use this background color */ + + /* Do initialization + */ + if( pState->nCount==0 ){ + /* Turn off the authorizer. It is no longer doing anything since the + ** query has already been prepared. + */ + db_clear_authorizer(); + + /* 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' && fossil_strcmp(azName[i],"bgcolor")==0 ){ + pState->iBg = i; + continue; + } + if( g.perm.Write && azName[i][0]=='#' ){ + pState->nCol++; + } + if( !pState->isMultirow ){ + if( azName[i][0]=='_' ){ + pState->isMultirow = 1; + pState->iNewRow = i; + pState->wikiFlags = WIKI_NOBADLINKS; + pState->zWikiStart = ""; + pState->zWikiEnd = ""; + if( P("plaintext") ){ + pState->wikiFlags |= WIKI_LINKSONLY; + pState->zWikiStart = "<pre class='verbatim'>"; + pState->zWikiEnd = "</pre>"; + style_submenu_element("Formatted", "%R/rptview/%d", pState->rn); + }else{ + style_submenu_element("Plaintext", "%R/rptview/%d?plaintext", + pState->rn); + } + }else{ + pState->nCol++; + } + } + } + + /* The first time this routine is called, output a table header + */ + @ <thead><tr> + zTid = 0; + for(i=0; i<nArg; i++){ + const char *zName = azName[i]; + if( i==pState->iBg ) continue; + if( pState->iNewRow>=0 && i>=pState->iNewRow ){ + if( g.perm.Write && zTid ){ + @ <th> </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.perm.Write && zTid ){ + @ <th> </th> + } + @ </tr></thead><tbody> + } + 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)" style="padding:0px"> + @ <hr style="margin:0px"></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; + for(i=0; i<nArg; i++){ + const char *zData; + if( i==pState->iBg ) continue; + zData = azArg[i]; + if( zData==0 ) zData = ""; + if( pState->iNewRow>=0 && i>=pState->iNewRow ){ + if( zTid && g.perm.Write ){ + @ <td valign="top">%z(href("%R/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)> + @ %s(pState->zWikiStart) + blob_init(&content, zData, -1); + wiki_convert(&content, 0, pState->wikiFlags); + blob_reset(&content); + @ %s(pState->zWikiEnd) + } + }else if( azName[i][0]=='#' ){ + zTid = zData; + @ <td valign="top">%z(href("%R/tktview/%h",zData))%h(zData)</a></td> + }else if( zData[0]==0 ){ + @ <td valign="top"> </td> + }else{ + @ <td valign="top"> + @ %h(zData) + @ </td> + } + } + if( zTid && g.perm.Write ){ + @ <td valign="top">%z(href("%R/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] && (!fossil_isspace(z[i]) || z[i]==' '); i++){} + if( i>0 ){ + cgi_printf("%.*s", i, z); + } + for(j=i; fossil_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 */ + const char **azArg, /* Text of data in all columns */ + const 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; + const char *zSafeKey; + char *zToFree; + while( fossil_isspace(*zClrKey) ) zClrKey++; + if( zClrKey[0]==0 ) return; + @ <table %s(zTabArgs)> + if( horiz ){ + @ <tr> + } + zSafeKey = zToFree = mprintf("%h", zClrKey); + while( zSafeKey[0] ){ + while( fossil_isspace(*zSafeKey) ) zSafeKey++; + for(i=0; zSafeKey[i] && !fossil_isspace(zSafeKey[i]); i++){} + for(j=i; fossil_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> +} + +/* +** Execute a single read-only SQL statement. Invoke xCallback() on each +** row. +*/ +static int db_exec_readonly( + sqlite3 *db, /* The database on which the SQL executes */ + const char *zSql, /* The SQL to be executed */ + int (*xCallback)(void*,int,const char**, const char**), + /* Invoke this callback routine */ + void *pArg, /* First argument to xCallback() */ + char **pzErrMsg /* Write error messages here */ +){ + int rc = SQLITE_OK; /* Return code */ + const char *zLeftover; /* Tail of unprocessed SQL */ + sqlite3_stmt *pStmt = 0; /* The current SQL statement */ + const char **azCols = 0; /* Names of result columns */ + int nCol; /* Number of columns of output */ + const char **azVals = 0; /* Text of all output columns */ + int i; /* Loop counter */ + int nVar; /* Number of parameters */ + + pStmt = 0; + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); + assert( rc==SQLITE_OK || pStmt==0 ); + if( rc!=SQLITE_OK ){ + return rc; + } + if( !pStmt ){ + /* this happens for a comment or white-space */ + return SQLITE_OK; + } + if( !sqlite3_stmt_readonly(pStmt) ){ + sqlite3_finalize(pStmt); + return SQLITE_ERROR; + } + + nVar = sqlite3_bind_parameter_count(pStmt); + for(i=1; i<=nVar; i++){ + const char *zVar = sqlite3_bind_parameter_name(pStmt, i); + if( zVar==0 ) continue; + if( zVar[0]!='$' && zVar[0]!='@' && zVar[0]!=':' ) continue; + if( !fossil_islower(zVar[1]) ) continue; + if( strcmp(zVar, "$login")==0 ){ + sqlite3_bind_text(pStmt, i, g.zLogin, -1, SQLITE_TRANSIENT); + }else{ + sqlite3_bind_text(pStmt, i, P(zVar+1), -1, SQLITE_TRANSIENT); + } + } + nCol = sqlite3_column_count(pStmt); + azVals = fossil_malloc(2*nCol*sizeof(const char*) + 1); + while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){ + if( azCols==0 ){ + azCols = &azVals[nCol]; + for(i=0; i<nCol; i++){ + azCols[i] = sqlite3_column_name(pStmt, i); + } + } + for(i=0; i<nCol; i++){ + azVals[i] = (const char *)sqlite3_column_text(pStmt, i); + } + if( xCallback(pArg, nCol, azVals, azCols) ){ + break; + } + } + rc = sqlite3_finalize(pStmt); + fossil_free((void *)azVals); + return rc; +} + +/* +** 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){ + rptview_page_content(0, 1, 1); +} + +/* +** Render a report. +*/ +void rptview_page_content( + const char *defaultTitleSearch, /* If rn and title query parameters are + blank, search reports by this title. */ + int pageWrap, /* If true, render full page; otherwise, just the report */ + int redirectMissing /* If true and report not found, go to reportlist */ +){ + int count = 0; + int rn, rc; + char *zSql; + char *zTitle; + char *zOwner; + char *zClrKey; + char *zDesc; + char *zMimetype; + int tabs; + Stmt q; + char *zErr1 = 0; + char *zErr2 = 0; + + login_check_credentials(); + if( !g.perm.RdTkt ){ login_needed(g.anon.RdTkt); return; } + report_update_reportfmt_table(); + rn = report_number(); + tabs = P("tablist")!=0; + db_prepare(&q, + "SELECT title, sqlcode, owner, cols, rn, jx->>'desc', jx->>'descmt'" + " FROM reportfmt WHERE rn=%d", rn); + rc = db_step(&q); + if( rc!=SQLITE_ROW ){ + const char *titleSearch = + defaultTitleSearch==0 || trim_string(defaultTitleSearch)[0]==0 ? + P("title") : defaultTitleSearch; + db_finalize(&q); + db_prepare(&q, + "SELECT title, sqlcode, owner, cols, rn, jx->>'desc', jx->>'descmt'" + " FROM reportfmt WHERE title GLOB %Q", + titleSearch); + rc = db_step(&q); + } + if( rc!=SQLITE_ROW ){ + db_finalize(&q); + if( redirectMissing ) { + 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); + rn = db_column_int(&q,4); + zDesc = db_column_malloc(&q, 5); + zMimetype = db_column_malloc(&q, 6); + 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 = { 0, 0, 0, 0, 0, 0, 0, 0, 0 }; + const char *zQS = PD("QUERY_STRING",""); + + db_multi_exec("PRAGMA empty_result_callbacks=ON"); + style_set_current_feature("report"); + if( pageWrap ) { + /* style_finish_page() should provide escaping via %h formatting */ + if( zQS[0] ){ + if( g.zExtra && g.zExtra[0] ){ + style_submenu_element("Raw","%R/%s/%s?tablist=1&%s", + g.zPath, g.zExtra, zQS); + }else{ + style_submenu_element("Raw","%R/%s?tablist=1&%s",g.zPath,zQS); + } + style_submenu_element("Reports","%R/reportlist?%s",zQS); + } else { + if( g.zExtra && g.zExtra[0] ){ + style_submenu_element("Raw","%R/%s/%s?tablist=1",g.zPath,g.zExtra); + }else{ + style_submenu_element("Raw","%R/%s?tablist=1",g.zPath); + } + style_submenu_element("Reports","%R/reportlist"); + } + if( g.perm.Admin + || (g.perm.TktFmt && g.zLogin && fossil_strcmp(g.zLogin,zOwner)==0) ){ + style_submenu_element("Edit", "%R/rptedit/%d", rn); + } + if( g.perm.TktFmt ){ + style_submenu_element("SQL", "%R/rptsql/%d",rn); + } + if( g.perm.NewTkt ){ + style_submenu_element("New Ticket", "%R/tktnew"); + } + style_header("%s", zTitle); + } + if( zDesc && zDesc[0] && zMimetype ){ + Blob src; + blob_init(&src, zDesc, -1); + wiki_render_by_mimetype(&src, zMimetype); + blob_reset(&src); + @ <br> + } + output_color_key(zClrKey, 1, + "border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\""); + @ <table border="1" cellpadding="2" cellspacing="0" class="report sortable" + @ data-column-types='' data-init-sort='0'> + sState.rn = rn; + sState.nCount = 0; + report_restrict_sql(&zErr1); + db_exec_readonly(g.db, zSql, generate_html, &sState, &zErr2); + report_unrestrict_sql(); + @ </tbody></table> + if( zErr1 ){ + @ <p class="reportError">Error: %h(zErr1)</p> + }else if( zErr2 ){ + @ <p class="reportError">Error: %h(zErr2)</p> + } + style_table_sorter(); + if( pageWrap ) { + style_finish_page(); + } + }else{ + report_restrict_sql(&zErr1); + db_exec_readonly(g.db, zSql, output_tab_separated, &count, &zErr2); + report_unrestrict_sql(); + 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; + fossil_print("Available reports:\n"); + fossil_print("%s\t%s\n","report number","report title"); + fossil_print("%s\t%s\n",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); + + fossil_print("%s\t%s\n",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); + fossil_print("%s",zFosZ); + free(zFosZ); + } + break; + } + default: + while( z && z[0] ){ + int i, j; + for(i=0; z[i] && (!fossil_isspace(z[i]) || z[i]==' '); i++){} + if( i>0 ){ + fossil_print("%.*s", i, z); + } + for(j=i; fossil_isspace(z[j]); j++){} + if( j>i ){ + fossil_print("%*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 */ + const char **azArg, /* Text of data in all columns */ + const 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]); + fossil_print("%s", i<nArg-1 ? (zSep?zSep:"\t") : "\n"); + } + } + ++*pCount; + for(i=0; i<nArg; i++){ + output_no_tabs_file(azArg[i]); + fossil_print("%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 parameter +** is a full WHERE-condition. +*/ +void rptshow( + const char *zRep, + const char *zSepIn, + const char *zFilter, + tTktShowEncoding enc +){ + Stmt q; + char *zSql; + char *zErr1 = 0; + char *zErr2 = 0; + int count = 0; + int rn; + + if( !zRep || !strcmp(zRep,zFullTicketRptRn) + || !strcmp(zRep,zFullTicketRptTitle) ){ + zSql = "SELECT * FROM ticket"; + }else{ + rn = atoi(zRep); + if( rn ){ + db_prepare(&q, + "SELECT sqlcode FROM reportfmt WHERE rn=%d", rn); + }else{ + db_prepare(&q, + "SELECT sqlcode FROM reportfmt WHERE title=%Q", zRep); + } + if( db_step(&q)!=SQLITE_ROW ){ + db_finalize(&q); + rpt_list_reports(); + fossil_fatal("unknown report format(%s)!",zRep); + } + zSql = db_column_malloc(&q, 0); + db_finalize(&q); + } + if( zFilter ){ + zSql = mprintf("SELECT * FROM (%s) WHERE %s",zSql,zFilter); + } + count = 0; + tktEncode = enc; + zSep = zSepIn; + report_restrict_sql(&zErr1); + db_exec_readonly(g.db, zSql, output_separated_file, &count, &zErr2); + report_unrestrict_sql(); + if( zFilter ){ + free(zSql); + } +} |