62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Copyright (c) 2007 D. Richard Hipp 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ** This program is free software; you can redistribute it and/or 62f37c9722 2007-11-26 drh: ** modify it under the terms of the GNU General Public 62f37c9722 2007-11-26 drh: ** License as published by the Free Software Foundation; either 62f37c9722 2007-11-26 drh: ** version 2 of the License, or (at your option) any later version. 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ** This program is distributed in the hope that it will be useful, 62f37c9722 2007-11-26 drh: ** but WITHOUT ANY WARRANTY; without even the implied warranty of 62f37c9722 2007-11-26 drh: ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 62f37c9722 2007-11-26 drh: ** General Public License for more details. 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ** You should have received a copy of the GNU General Public 62f37c9722 2007-11-26 drh: ** License along with this library; if not, write to the 62f37c9722 2007-11-26 drh: ** Free Software Foundation, Inc., 59 Temple Place - Suite 330, 62f37c9722 2007-11-26 drh: ** Boston, MA 02111-1307, USA. 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ** Author contact information: 62f37c9722 2007-11-26 drh: ** drh@hwaci.com 62f37c9722 2007-11-26 drh: ** http://www.hwaci.com/drh/ 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ******************************************************************************* 62f37c9722 2007-11-26 drh: ** cf9ee7d67e 2009-01-23 drh: ** Code to generate the ticket listings 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: #include "config.h" 62f37c9722 2007-11-26 drh: #include "report.h" 62f37c9722 2007-11-26 drh: #include <assert.h> 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* Forward references to static routines */ 62f37c9722 2007-11-26 drh: static void report_format_hints(void); 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** WEBPAGE: /reportlist 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: void view_list(void){ 62f37c9722 2007-11-26 drh: Stmt q; 86ed68ba34 2008-05-17 drh: int rn = 0; d3e711fd2f 2008-07-15 drh: int cnt = 0; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: login_check_credentials(); 86ed68ba34 2008-05-17 drh: if( !g.okRdTkt && !g.okNewTkt ){ login_needed(); return; } cf9ee7d67e 2009-01-23 drh: style_header("Ticket Main Menu"); 86ed68ba34 2008-05-17 drh: if( g.okNewTkt ){ cf9ee7d67e 2009-01-23 drh: @ <p>Enter a new ticket:</p> cf9ee7d67e 2009-01-23 drh: @ <ol><li value="1"><a href="tktnew">New ticket</a></li></ol> 86ed68ba34 2008-05-17 drh: @ d3e711fd2f 2008-07-15 drh: cnt++; 86ed68ba34 2008-05-17 drh: } 86ed68ba34 2008-05-17 drh: if( !g.okRdTkt ){ cf9ee7d67e 2009-01-23 drh: @ <p>You are not authorized to view existing tickets.</p> 86ed68ba34 2008-05-17 drh: }else{ 86ed68ba34 2008-05-17 drh: db_prepare(&q, "SELECT rn, title, owner FROM reportfmt ORDER BY title"); 86ed68ba34 2008-05-17 drh: @ <p>Choose a report format from the following list:</p> 86ed68ba34 2008-05-17 drh: @ <ol> 86ed68ba34 2008-05-17 drh: while( db_step(&q)==SQLITE_ROW ){ 86ed68ba34 2008-05-17 drh: const char *zTitle = db_column_text(&q, 1); 86ed68ba34 2008-05-17 drh: const char *zOwner = db_column_text(&q, 2); 9e2d2676a4 2008-06-08 drh: rn = db_column_int(&q, 0); 49380d500a 2008-05-28 drh: cnt++; 49380d500a 2008-05-28 drh: @ <li value="%d(cnt)"><a href="rptview?rn=%d(rn)" 86ed68ba34 2008-05-17 drh: @ rel="nofollow">%h(zTitle)</a> 86ed68ba34 2008-05-17 drh: if( g.okWrite && zOwner && zOwner[0] ){ 86ed68ba34 2008-05-17 drh: @ (by <i>%h(zOwner)</i>) 86ed68ba34 2008-05-17 drh: } 49380d500a 2008-05-28 drh: if( g.okTktFmt ){ 86ed68ba34 2008-05-17 drh: @ [<a href="rptedit?rn=%d(rn)&copy=1" rel="nofollow">copy</a>] 86ed68ba34 2008-05-17 drh: } 86ed68ba34 2008-05-17 drh: if( g.okAdmin || (g.okWrTkt && zOwner && strcmp(g.zLogin,zOwner)==0) ){ 86ed68ba34 2008-05-17 drh: @ [<a href="rptedit?rn=%d(rn)" rel="nofollow">edit</a>] 86ed68ba34 2008-05-17 drh: } d3e711fd2f 2008-07-15 drh: if( g.okTktFmt ){ d3e711fd2f 2008-07-15 drh: @ [<a href="rptsql?rn=%d(rn)" rel="nofollow">sql</a>] d3e711fd2f 2008-07-15 drh: } 86ed68ba34 2008-05-17 drh: @ </li> 86ed68ba34 2008-05-17 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ </ol> 49380d500a 2008-05-28 drh: if( g.okTktFmt ){ cf9ee7d67e 2009-01-23 drh: @ <p>Create a new ticket display format:</p> 86ed68ba34 2008-05-17 drh: @ <ol> 49380d500a 2008-05-28 drh: @ <li value="%d(cnt+1)"><a href="rptnew">New report format</a></li> 86ed68ba34 2008-05-17 drh: @ </ol> 86ed68ba34 2008-05-17 drh: } 55342eb9fb 2008-05-17 drh: style_footer(); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Remove whitespace from both ends of a string. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: char *trim_string(const char *zOrig){ 62f37c9722 2007-11-26 drh: int i; 62f37c9722 2007-11-26 drh: while( isspace(*zOrig) ){ zOrig++; } 62f37c9722 2007-11-26 drh: i = strlen(zOrig); 62f37c9722 2007-11-26 drh: while( i>0 && isspace(zOrig[i-1]) ){ i--; } 62f37c9722 2007-11-26 drh: return mprintf("%.*s", i, zOrig); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Extract a numeric (integer) value from a string. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: char *extract_integer(const char *zOrig){ 62f37c9722 2007-11-26 drh: if( zOrig == NULL || zOrig[0] == 0 ) return ""; 62f37c9722 2007-11-26 drh: while( *zOrig && !isdigit(*zOrig) ){ zOrig++; } 62f37c9722 2007-11-26 drh: if( *zOrig ){ 62f37c9722 2007-11-26 drh: /* we have a digit. atoi() will get as much of the number as it 62f37c9722 2007-11-26 drh: ** can. We'll run it through mprintf() to get a string. Not 62f37c9722 2007-11-26 drh: ** an efficient way to do it, but effective. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: return mprintf("%d", atoi(zOrig)); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: return ""; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Remove blank lines from the beginning of a string and 62f37c9722 2007-11-26 drh: ** all whitespace from the end. Removes whitespace preceeding a NL, 62f37c9722 2007-11-26 drh: ** which also converts any CRNL sequence into a single NL. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: char *remove_blank_lines(const char *zOrig){ 62f37c9722 2007-11-26 drh: int i, j, n; 62f37c9722 2007-11-26 drh: char *z; 62f37c9722 2007-11-26 drh: for(i=j=0; isspace(zOrig[i]); i++){ if( zOrig[i]=='\n' ) j = i+1; } 62f37c9722 2007-11-26 drh: n = strlen(&zOrig[j]); 62f37c9722 2007-11-26 drh: while( n>0 && isspace(zOrig[j+n-1]) ){ n--; } 62f37c9722 2007-11-26 drh: z = mprintf("%.*s", n, &zOrig[j]); 62f37c9722 2007-11-26 drh: for(i=j=0; z[i]; i++){ 62f37c9722 2007-11-26 drh: if( z[i+1]=='\n' && z[i]!='\n' && isspace(z[i]) ){ 62f37c9722 2007-11-26 drh: z[j] = z[i]; 62f37c9722 2007-11-26 drh: while(isspace(z[j]) && z[j] != '\n' ){ j--; } 62f37c9722 2007-11-26 drh: j++; 62f37c9722 2007-11-26 drh: continue; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: z[j++] = z[i]; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: z[j] = 0; 62f37c9722 2007-11-26 drh: return z; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /*********************************************************************/ 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** This is the SQLite authorizer callback used to make sure that the 62f37c9722 2007-11-26 drh: ** SQL statements entered by users do not try to do anything untoward. 62f37c9722 2007-11-26 drh: ** If anything suspicious is tried, set *(char**)pError to an error 62f37c9722 2007-11-26 drh: ** message obtained from malloc. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: static int report_query_authorizer( 62f37c9722 2007-11-26 drh: void *pError, 62f37c9722 2007-11-26 drh: int code, 62f37c9722 2007-11-26 drh: const char *zArg1, 62f37c9722 2007-11-26 drh: const char *zArg2, 62f37c9722 2007-11-26 drh: const char *zArg3, 62f37c9722 2007-11-26 drh: const char *zArg4 62f37c9722 2007-11-26 drh: ){ 21326fb6f7 2008-07-19 drh: int rc = SQLITE_OK; 21326fb6f7 2008-07-19 drh: if( *(char**)pError ){ 62f37c9722 2007-11-26 drh: /* We've already seen an error. No need to continue. */ 62f37c9722 2007-11-26 drh: return SQLITE_OK; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: switch( code ){ 62f37c9722 2007-11-26 drh: case SQLITE_SELECT: 62f37c9722 2007-11-26 drh: case SQLITE_FUNCTION: { 62f37c9722 2007-11-26 drh: break; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: case SQLITE_READ: { 62f37c9722 2007-11-26 drh: static const char *azAllowed[] = { 62f37c9722 2007-11-26 drh: "ticket", 62f37c9722 2007-11-26 drh: "blob", 62f37c9722 2007-11-26 drh: "filename", 62f37c9722 2007-11-26 drh: "mlink", 62f37c9722 2007-11-26 drh: "plink", 62f37c9722 2007-11-26 drh: "event", 62f37c9722 2007-11-26 drh: "tag", 62f37c9722 2007-11-26 drh: "tagxref", 55342eb9fb 2008-05-17 drh: }; 62f37c9722 2007-11-26 drh: int i; 62f37c9722 2007-11-26 drh: for(i=0; i<sizeof(azAllowed)/sizeof(azAllowed[0]); i++){ 62f37c9722 2007-11-26 drh: if( strcasecmp(zArg1, azAllowed[i])==0 ) break; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( i>=sizeof(azAllowed)/sizeof(azAllowed[0]) ){ 21326fb6f7 2008-07-19 drh: *(char**)pError = mprintf("access to table \"%s\" is restricted",zArg1); 21326fb6f7 2008-07-19 drh: rc = SQLITE_DENY; 21326fb6f7 2008-07-19 drh: }else if( !g.okRdAddr && strncmp(zArg2, "private_", 8)==0 ){ 21326fb6f7 2008-07-19 drh: rc = SQLITE_IGNORE; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: break; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: default: { 21326fb6f7 2008-07-19 drh: *(char**)pError = mprintf("only SELECT statements are allowed"); 21326fb6f7 2008-07-19 drh: rc = SQLITE_DENY; 62f37c9722 2007-11-26 drh: break; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 21326fb6f7 2008-07-19 drh: return rc; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Check the given SQL to see if is a valid query that does not 62f37c9722 2007-11-26 drh: ** attempt to do anything dangerous. Return 0 on success and a 62f37c9722 2007-11-26 drh: ** pointer to an error message string (obtained from malloc) if 62f37c9722 2007-11-26 drh: ** there is a problem. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: char *verify_sql_statement(char *zSql){ 62f37c9722 2007-11-26 drh: int i; 55342eb9fb 2008-05-17 drh: char *zErr = 0; 55342eb9fb 2008-05-17 drh: const char *zTail; 55342eb9fb 2008-05-17 drh: sqlite3_stmt *pStmt; 55342eb9fb 2008-05-17 drh: int rc; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* First make sure the SQL is a single query command by verifying that 62f37c9722 2007-11-26 drh: ** the first token is "SELECT" and that there are no unquoted semicolons. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: for(i=0; isspace(zSql[i]); i++){} 62f37c9722 2007-11-26 drh: if( strncasecmp(&zSql[i],"select",6)!=0 ){ 62f37c9722 2007-11-26 drh: return mprintf("The SQL must be a SELECT statement"); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: for(i=0; zSql[i]; i++){ 62f37c9722 2007-11-26 drh: if( zSql[i]==';' ){ 62f37c9722 2007-11-26 drh: int bad; 62f37c9722 2007-11-26 drh: int c = zSql[i+1]; 62f37c9722 2007-11-26 drh: zSql[i+1] = 0; 62f37c9722 2007-11-26 drh: bad = sqlite3_complete(zSql); 62f37c9722 2007-11-26 drh: zSql[i+1] = c; 62f37c9722 2007-11-26 drh: if( bad ){ 62f37c9722 2007-11-26 drh: /* A complete statement basically means that an unquoted semi-colon 62f37c9722 2007-11-26 drh: ** was found. We don't actually check what's after that. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: return mprintf("Semi-colon detected! " 62f37c9722 2007-11-26 drh: "Only a single SQL statement is allowed"); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* Compile the statement and check for illegal accesses or syntax errors. */ 62f37c9722 2007-11-26 drh: sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr); 62f37c9722 2007-11-26 drh: rc = sqlite3_prepare(g.db, zSql, -1, &pStmt, &zTail); 62f37c9722 2007-11-26 drh: if( rc!=SQLITE_OK ){ 62f37c9722 2007-11-26 drh: zErr = mprintf("Syntax error: %s", sqlite3_errmsg(g.db)); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( pStmt ){ 62f37c9722 2007-11-26 drh: sqlite3_finalize(pStmt); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: sqlite3_set_authorizer(g.db, 0, 0); 62f37c9722 2007-11-26 drh: return zErr; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** WEBPAGE: /rptsql 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: void view_see_sql(void){ 55342eb9fb 2008-05-17 drh: int rn; 55342eb9fb 2008-05-17 drh: const char *zTitle; 55342eb9fb 2008-05-17 drh: const char *zSQL; 55342eb9fb 2008-05-17 drh: const char *zOwner; 55342eb9fb 2008-05-17 drh: const char *zClrKey; 62f37c9722 2007-11-26 drh: Stmt q; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: login_check_credentials(); d3e711fd2f 2008-07-15 drh: if( !g.okTktFmt ){ 62f37c9722 2007-11-26 drh: login_needed(); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: rn = atoi(PD("rn","0")); 62f37c9722 2007-11-26 drh: db_prepare(&q, "SELECT title, sqlcode, owner, cols " 62f37c9722 2007-11-26 drh: "FROM reportfmt WHERE rn=%d",rn); 62f37c9722 2007-11-26 drh: style_header("SQL For Report Format Number %d", rn); 62f37c9722 2007-11-26 drh: if( db_step(&q)!=SQLITE_ROW ){ 62f37c9722 2007-11-26 drh: @ <p>Unknown report number: %d(rn)</p> 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: zTitle = db_column_text(&q, 0); 62f37c9722 2007-11-26 drh: zSQL = db_column_text(&q, 1); 62f37c9722 2007-11-26 drh: zOwner = db_column_text(&q, 2); 62f37c9722 2007-11-26 drh: zClrKey = db_column_text(&q, 3); 62f37c9722 2007-11-26 drh: @ <table cellpadding=0 cellspacing=0 border=0> 62f37c9722 2007-11-26 drh: @ <tr><td valign="top" align="right">Title:</td><td width=15></td> 62f37c9722 2007-11-26 drh: @ <td colspan=3>%h(zTitle)</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td valign="top" align="right">Owner:</td><td></td> 62f37c9722 2007-11-26 drh: @ <td colspan=3>%h(zOwner)</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td valign="top" align="right">SQL:</td><td></td> 62f37c9722 2007-11-26 drh: @ <td valign="top"><pre> 62f37c9722 2007-11-26 drh: @ %h(zSQL) 62f37c9722 2007-11-26 drh: @ </pre></td> 62f37c9722 2007-11-26 drh: @ <td width=15></td><td valign="top"> 62f37c9722 2007-11-26 drh: output_color_key(zClrKey, 0, "border=0 cellspacing=0 cellpadding=3"); 62f37c9722 2007-11-26 drh: @ </td> 62f37c9722 2007-11-26 drh: @ </tr></table> 62f37c9722 2007-11-26 drh: report_format_hints(); 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** WEBPAGE: /rptnew 62f37c9722 2007-11-26 drh: ** WEBPAGE: /rptedit 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: void view_edit(void){ 62f37c9722 2007-11-26 drh: int rn; 62f37c9722 2007-11-26 drh: const char *zTitle; 62f37c9722 2007-11-26 drh: const char *z; 62f37c9722 2007-11-26 drh: const char *zOwner; 49380d500a 2008-05-28 drh: const char *zClrKey; 62f37c9722 2007-11-26 drh: char *zSQL; 62f37c9722 2007-11-26 drh: char *zErr = 0; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: login_check_credentials(); d3e711fd2f 2008-07-15 drh: if( !g.okTktFmt ){ 62f37c9722 2007-11-26 drh: login_needed(); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 55342eb9fb 2008-05-17 drh: /*view_add_functions(0);*/ 62f37c9722 2007-11-26 drh: rn = atoi(PD("rn","0")); 62f37c9722 2007-11-26 drh: zTitle = P("t"); 62f37c9722 2007-11-26 drh: zOwner = PD("w",g.zLogin); 62f37c9722 2007-11-26 drh: z = P("s"); 62f37c9722 2007-11-26 drh: zSQL = z ? trim_string(z) : 0; 62f37c9722 2007-11-26 drh: zClrKey = trim_string(PD("k","")); 62f37c9722 2007-11-26 drh: if( rn>0 && P("del2") ){ 0be54823ba 2008-10-18 drh: login_verify_csrf_secret(); 62f37c9722 2007-11-26 drh: db_multi_exec("DELETE FROM reportfmt WHERE rn=%d", rn); 62f37c9722 2007-11-26 drh: cgi_redirect("reportlist"); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: }else if( rn>0 && P("del1") ){ 62f37c9722 2007-11-26 drh: zTitle = db_text(0, "SELECT title FROM reportfmt " 62f37c9722 2007-11-26 drh: "WHERE rn=%d", rn); 62f37c9722 2007-11-26 drh: if( zTitle==0 ) cgi_redirect("reportlist"); 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: style_header("Are You Sure?"); 62f37c9722 2007-11-26 drh: @ <form action="rptedit" method="POST"> 62f37c9722 2007-11-26 drh: @ <p>You are about to delete all traces of the report 62f37c9722 2007-11-26 drh: @ <strong>%h(zTitle)</strong> from 62f37c9722 2007-11-26 drh: @ the database. This is an irreversible operation. All records 62f37c9722 2007-11-26 drh: @ related to this report will be removed and cannot be recovered.</p> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <input type="hidden" name="rn" value="%d(rn)"> 0be54823ba 2008-10-18 drh: login_insert_csrf_secret(); 62f37c9722 2007-11-26 drh: @ <input type="submit" name="del2" value="Delete The Report"> 62f37c9722 2007-11-26 drh: @ <input type="submit" name="can" value="Cancel"> 62f37c9722 2007-11-26 drh: @ </form> 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: }else if( P("can") ){ 62f37c9722 2007-11-26 drh: /* user cancelled */ 62f37c9722 2007-11-26 drh: cgi_redirect("reportlist"); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( zTitle && zSQL ){ 62f37c9722 2007-11-26 drh: if( zSQL[0]==0 ){ 62f37c9722 2007-11-26 drh: zErr = "Please supply an SQL query statement"; 62f37c9722 2007-11-26 drh: }else if( (zTitle = trim_string(zTitle))[0]==0 ){ 62f37c9722 2007-11-26 drh: zErr = "Please supply a title"; 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: zErr = verify_sql_statement(zSQL); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( zErr==0 ){ 0be54823ba 2008-10-18 drh: login_verify_csrf_secret(); 62f37c9722 2007-11-26 drh: if( rn>0 ){ 62f37c9722 2007-11-26 drh: db_multi_exec("UPDATE reportfmt SET title=%Q, sqlcode=%Q," 62f37c9722 2007-11-26 drh: " owner=%Q, cols=%Q WHERE rn=%d", 62f37c9722 2007-11-26 drh: zTitle, zSQL, zOwner, zClrKey, rn); 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: db_multi_exec("INSERT INTO reportfmt(title,sqlcode,owner,cols) " 62f37c9722 2007-11-26 drh: "VALUES(%Q,%Q,%Q,%Q)", 62f37c9722 2007-11-26 drh: zTitle, zSQL, zOwner, zClrKey); 62f37c9722 2007-11-26 drh: rn = db_last_insert_rowid(); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: cgi_redirect(mprintf("rptview?rn=%d", rn)); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: }else if( rn==0 ){ 62f37c9722 2007-11-26 drh: zTitle = ""; 49380d500a 2008-05-28 drh: zSQL = ticket_report_template(); 49380d500a 2008-05-28 drh: zClrKey = ticket_key_template(); 62f37c9722 2007-11-26 drh: }else{ 55342eb9fb 2008-05-17 drh: Stmt q; 62f37c9722 2007-11-26 drh: db_prepare(&q, "SELECT title, sqlcode, owner, cols " 62f37c9722 2007-11-26 drh: "FROM reportfmt WHERE rn=%d",rn); 62f37c9722 2007-11-26 drh: if( db_step(&q)==SQLITE_ROW ){ 62f37c9722 2007-11-26 drh: zTitle = db_column_malloc(&q, 0); 62f37c9722 2007-11-26 drh: zSQL = db_column_malloc(&q, 1); 62f37c9722 2007-11-26 drh: zOwner = db_column_malloc(&q, 2); 62f37c9722 2007-11-26 drh: zClrKey = db_column_malloc(&q, 3); 62f37c9722 2007-11-26 drh: } 55342eb9fb 2008-05-17 drh: db_finalize(&q); 62f37c9722 2007-11-26 drh: if( P("copy") ){ 62f37c9722 2007-11-26 drh: rn = 0; 62f37c9722 2007-11-26 drh: zTitle = mprintf("Copy Of %s", zTitle); 62f37c9722 2007-11-26 drh: zOwner = g.zLogin; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( zOwner==0 ) zOwner = g.zLogin; 62f37c9722 2007-11-26 drh: style_submenu_element("Cancel", "Cancel", "reportlist"); 62f37c9722 2007-11-26 drh: if( rn>0 ){ 62f37c9722 2007-11-26 drh: style_submenu_element("Delete", "Delete", "rptedit?rn=%d&del1=1", rn); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: style_header(rn>0 ? "Edit Report Format":"Create New Report Format"); 62f37c9722 2007-11-26 drh: if( zErr ){ 62f37c9722 2007-11-26 drh: @ <blockquote><font color="#ff0000"><b>%h(zErr)</b></font></blockquote> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ <form action="rptedit" method="POST"> 62f37c9722 2007-11-26 drh: @ <input type="hidden" name="rn" value="%d(rn)"> 62f37c9722 2007-11-26 drh: @ <p>Report Title:<br> 62f37c9722 2007-11-26 drh: @ <input type="text" name="t" value="%h(zTitle)" size="60"></p> 62f37c9722 2007-11-26 drh: @ <p>Enter a complete SQL query statement against the "TICKET" table:<br> 62f37c9722 2007-11-26 drh: @ <textarea name="s" rows="20" cols="80">%h(zSQL)</textarea> 62f37c9722 2007-11-26 drh: @ </p> 0be54823ba 2008-10-18 drh: login_insert_csrf_secret(); 62f37c9722 2007-11-26 drh: if( g.okAdmin ){ 62f37c9722 2007-11-26 drh: @ <p>Report owner: 62f37c9722 2007-11-26 drh: @ <input type="text" name="w" size="20" value="%h(zOwner)"> 62f37c9722 2007-11-26 drh: @ </p> 62f37c9722 2007-11-26 drh: } else { 62f37c9722 2007-11-26 drh: @ <input type="hidden" name="w" value="%h(zOwner)"> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ <p>Enter an optional color key in the following box. (If blank, no 62f37c9722 2007-11-26 drh: @ color key is displayed.) Each line contains the text for a single 62f37c9722 2007-11-26 drh: @ entry in the key. The first token of each line is the background 62f37c9722 2007-11-26 drh: @ color for that line.<br> 62f37c9722 2007-11-26 drh: @ <textarea name="k" rows="8" cols="50">%h(zClrKey)</textarea> 62f37c9722 2007-11-26 drh: @ </p> 62f37c9722 2007-11-26 drh: if( !g.okAdmin && strcmp(zOwner,g.zLogin)!=0 ){ 62f37c9722 2007-11-26 drh: @ <p>This report format is owned by %h(zOwner). You are not allowed 62f37c9722 2007-11-26 drh: @ to change it.</p> 62f37c9722 2007-11-26 drh: @ </form> 62f37c9722 2007-11-26 drh: report_format_hints(); 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ <input type="submit" value="Apply Changes"> 62f37c9722 2007-11-26 drh: if( rn>0 ){ 62f37c9722 2007-11-26 drh: @ <input type="submit" value="Delete This Report" name="del1"> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ </form> 62f37c9722 2007-11-26 drh: report_format_hints(); 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Output a bunch of text that provides information about report 62f37c9722 2007-11-26 drh: ** formats 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: static void report_format_hints(void){ 62f37c9722 2007-11-26 drh: char *zSchema; 62f37c9722 2007-11-26 drh: zSchema = db_text(0,"SELECT sql FROM sqlite_master WHERE name='ticket'"); d3e711fd2f 2008-07-15 drh: if( zSchema==0 ){ d3e711fd2f 2008-07-15 drh: zSchema = db_text(0,"SELECT sql FROM repository.sqlite_master" d3e711fd2f 2008-07-15 drh: " WHERE name='ticket'"); d3e711fd2f 2008-07-15 drh: } 62f37c9722 2007-11-26 drh: @ <hr><h3>TICKET Schema</h3> 62f37c9722 2007-11-26 drh: @ <blockquote><pre> 62f37c9722 2007-11-26 drh: @ %h(zSchema) 62f37c9722 2007-11-26 drh: @ </pre></blockquote> 62f37c9722 2007-11-26 drh: @ <h3>Notes</h3> 62f37c9722 2007-11-26 drh: @ <ul> 62f37c9722 2007-11-26 drh: @ <li><p>The SQL must consist of a single SELECT statement</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>If a column of the result set is named "#" then that column 62f37c9722 2007-11-26 drh: @ is assumed to hold a ticket number. A hyperlink will be created from 62f37c9722 2007-11-26 drh: @ that column to a detailed view of the ticket.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>If a column of the result set is named "bgcolor" then the content 62f37c9722 2007-11-26 drh: @ of that column determines the background color of the row.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The <b>user()</b> SQL function returns a string 62f37c9722 2007-11-26 drh: @ which is the login of the current user.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The first column whose name begins with underscore ("_") and all 62f37c9722 2007-11-26 drh: @ subsequent columns are shown on their own rows in the table. This might 62f37c9722 2007-11-26 drh: @ be useful for displaying the description of tickets. 62f37c9722 2007-11-26 drh: @ </p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The <b>aux()</b> SQL function takes a parameter name as an argument 62f37c9722 2007-11-26 drh: @ and returns the value that the user enters in the resulting HTML form. A 62f37c9722 2007-11-26 drh: @ second optional parameter provides a default value for the field.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The <b>option()</b> SQL function takes a parameter name 62f37c9722 2007-11-26 drh: @ and a quoted SELECT statement as parameters. The query results are 62f37c9722 2007-11-26 drh: @ presented as an HTML dropdown menu and the function returns 62f37c9722 2007-11-26 drh: @ the currently selected value. Results may be a single value column or 62f37c9722 2007-11-26 drh: @ two <b>value,description</b> columns. The first row is the default.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The <b>cgi()</b> SQL function takes a parameter name as an argument 62f37c9722 2007-11-26 drh: @ and returns the value of a corresponding CGI query value. If the CGI 62f37c9722 2007-11-26 drh: @ parameter doesn't exist, an optional second argument will be returned 62f37c9722 2007-11-26 drh: @ instead.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>If a column is wrapped by the <b>wiki()</b> SQL function, it will 62f37c9722 2007-11-26 drh: @ be rendered as wiki formatted content.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>If a column is wrapped by the <b>tkt()</b> SQL function, it will 62f37c9722 2007-11-26 drh: @ be shown as a ticket id with a link to the appropriate page</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>If a column is wrapped by the <b>chng()</b> SQL function, it will 62f37c9722 2007-11-26 drh: @ be shown as a baseline id with a link to the appropriate page.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The <b>search()</b> SQL function takes a keyword pattern and 62f37c9722 2007-11-26 drh: @ a search text. The function returns an integer score which is 62f37c9722 2007-11-26 drh: @ higher depending on how well the search went.</p></li> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <li><p>The query can join other tables in the database besides TICKET. 62f37c9722 2007-11-26 drh: @ </p></li> 62f37c9722 2007-11-26 drh: @ </ul> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <h3>Examples</h3> 62f37c9722 2007-11-26 drh: @ <p>In this example, the first column in the result set is named 62f37c9722 2007-11-26 drh: @ "bgcolor". The value of this column is not displayed. Instead, it 62f37c9722 2007-11-26 drh: @ selects the background color of each row based on the TICKET.STATUS 62f37c9722 2007-11-26 drh: @ field of the database. The color key at the right shows the various 62f37c9722 2007-11-26 drh: @ color codes.</p> 62f37c9722 2007-11-26 drh: @ <table align="right" style="margin: 0 5px;" border=1 cellspacing=0 width=125> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#f2dcdc"><td align="center">new or active</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#e8e8bd"><td align="center">review</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#cfe8bd"><td align="center">fixed</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#bde5d6"><td align="center">tested</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#cacae5"><td align="center">defer</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#c8c8c8"><td align="center">closed</td></tr> 62f37c9722 2007-11-26 drh: @ </table> 62f37c9722 2007-11-26 drh: @ <blockquote><pre> 62f37c9722 2007-11-26 drh: @ SELECT 62f37c9722 2007-11-26 drh: @ CASE WHEN status IN ('new','active') THEN '#f2dcdc' 62f37c9722 2007-11-26 drh: @ WHEN status='review' THEN '#e8e8bd' 62f37c9722 2007-11-26 drh: @ WHEN status='fixed' THEN '#cfe8bd' 62f37c9722 2007-11-26 drh: @ WHEN status='tested' THEN '#bde5d6' 62f37c9722 2007-11-26 drh: @ WHEN status='defer' THEN '#cacae5' 62f37c9722 2007-11-26 drh: @ ELSE '#c8c8c8' END as 'bgcolor', 62f37c9722 2007-11-26 drh: @ tn AS '#', 62f37c9722 2007-11-26 drh: @ type AS 'Type', 62f37c9722 2007-11-26 drh: @ status AS 'Status', 62f37c9722 2007-11-26 drh: @ sdate(origtime) AS 'Created', 62f37c9722 2007-11-26 drh: @ owner AS 'By', 62f37c9722 2007-11-26 drh: @ subsystem AS 'Subsys', 62f37c9722 2007-11-26 drh: @ sdate(changetime) AS 'Changed', 62f37c9722 2007-11-26 drh: @ assignedto AS 'Assigned', 62f37c9722 2007-11-26 drh: @ severity AS 'Svr', 62f37c9722 2007-11-26 drh: @ priority AS 'Pri', 62f37c9722 2007-11-26 drh: @ title AS 'Title' 62f37c9722 2007-11-26 drh: @ FROM ticket 62f37c9722 2007-11-26 drh: @ </pre></blockquote> 62f37c9722 2007-11-26 drh: @ <p>To base the background color on the TICKET.PRIORITY or 62f37c9722 2007-11-26 drh: @ TICKET.SEVERITY fields, substitute the following code for the 62f37c9722 2007-11-26 drh: @ first column of the query:</p> 62f37c9722 2007-11-26 drh: @ <table align="right" style="margin: 0 5px;" border=1 cellspacing=0 width=125> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#f2dcdc"><td align="center">1</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#e8e8bd"><td align="center">2</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#cfe8bd"><td align="center">3</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#cacae5"><td align="center">4</td></tr> 62f37c9722 2007-11-26 drh: @ <tr bgcolor="#c8c8c8"><td align="center">5</td></tr> 62f37c9722 2007-11-26 drh: @ </table> 62f37c9722 2007-11-26 drh: @ <blockquote><pre> 62f37c9722 2007-11-26 drh: @ SELECT 62f37c9722 2007-11-26 drh: @ CASE priority WHEN 1 THEN '#f2dcdc' 62f37c9722 2007-11-26 drh: @ WHEN 2 THEN '#e8e8bd' 62f37c9722 2007-11-26 drh: @ WHEN 3 THEN '#cfe8bd' 62f37c9722 2007-11-26 drh: @ WHEN 4 THEN '#cacae5' 62f37c9722 2007-11-26 drh: @ ELSE '#c8c8c8' END as 'bgcolor', 62f37c9722 2007-11-26 drh: @ ... 62f37c9722 2007-11-26 drh: @ FROM ticket 62f37c9722 2007-11-26 drh: @ </pre></blockquote> 62f37c9722 2007-11-26 drh: #if 0 62f37c9722 2007-11-26 drh: @ <p>You can, of course, substitute different colors if you choose. 62f37c9722 2007-11-26 drh: @ Here is a palette of suggested background colors:</p> 62f37c9722 2007-11-26 drh: @ <blockquote> 62f37c9722 2007-11-26 drh: @ <table border=1 cellspacing=0 width=300> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#ffbdbd">#ffbdbd</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#f2dcdc">#f2dcdc</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#ffffbd">#ffffbd</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#e8e8bd">#e8e8bd</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#c0ebc0">#c0ebc0</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#cfe8bd">#cfe8bd</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#c0c0f4">#c0c0f4</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#d6d6e8">#d6d6e8</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#d0b1ff">#d0b1ff</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#d2c0db">#d2c0db</td></tr> 62f37c9722 2007-11-26 drh: @ <tr><td align="center" bgcolor="#bbbbbb">#bbbbbb</td> 62f37c9722 2007-11-26 drh: @ <td align="center" bgcolor="#d0d0d0">#d0d0d0</td></tr> 62f37c9722 2007-11-26 drh: @ </table> 62f37c9722 2007-11-26 drh: @ </blockquote> 62f37c9722 2007-11-26 drh: #endif 62f37c9722 2007-11-26 drh: @ <p>To see the TICKET.DESCRIPTION and TICKET.REMARKS fields, include 62f37c9722 2007-11-26 drh: @ them as the last two columns of the result set and given them names 62f37c9722 2007-11-26 drh: @ that begin with an underscore. Like this:</p> 62f37c9722 2007-11-26 drh: @ <blockquote><pre> 62f37c9722 2007-11-26 drh: @ SELECT 62f37c9722 2007-11-26 drh: @ tn AS '#', 62f37c9722 2007-11-26 drh: @ type AS 'Type', 62f37c9722 2007-11-26 drh: @ status AS 'Status', 62f37c9722 2007-11-26 drh: @ sdate(origtime) AS 'Created', 62f37c9722 2007-11-26 drh: @ owner AS 'By', 62f37c9722 2007-11-26 drh: @ subsystem AS 'Subsys', 62f37c9722 2007-11-26 drh: @ sdate(changetime) AS 'Changed', 62f37c9722 2007-11-26 drh: @ assignedto AS 'Assigned', 62f37c9722 2007-11-26 drh: @ severity AS 'Svr', 62f37c9722 2007-11-26 drh: @ priority AS 'Pri', 62f37c9722 2007-11-26 drh: @ title AS 'Title', 62f37c9722 2007-11-26 drh: @ description AS '_Description', -- When the column name begins with '_' 62f37c9722 2007-11-26 drh: @ remarks AS '_Remarks' -- the data is shown on a separate row. 62f37c9722 2007-11-26 drh: @ FROM ticket 62f37c9722 2007-11-26 drh: @ </pre></blockquote> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: @ <p>Or, to see part of the description on the same row, use the 62f37c9722 2007-11-26 drh: @ <b>wiki()</b> function with some string manipulation. Using the 62f37c9722 2007-11-26 drh: @ <b>tkt()</b> function on the ticket number will also generate a linked 62f37c9722 2007-11-26 drh: @ field, but without the extra <i>edit</i> column: 62f37c9722 2007-11-26 drh: @ </p> 62f37c9722 2007-11-26 drh: @ <blockquote><pre> 62f37c9722 2007-11-26 drh: @ SELECT 62f37c9722 2007-11-26 drh: @ tkt(tn) AS '', 62f37c9722 2007-11-26 drh: @ title AS 'Title', 62f37c9722 2007-11-26 drh: @ wiki(substr(description,0,80)) AS 'Description' 62f37c9722 2007-11-26 drh: @ FROM ticket 62f37c9722 2007-11-26 drh: @ </pre></blockquote> 62f37c9722 2007-11-26 drh: @ 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: static void column_header(int rn,const char *zCol, int nCol, int nSorted, 62f37c9722 2007-11-26 drh: const char *zDirection, const char *zExtra 62f37c9722 2007-11-26 drh: ){ 62f37c9722 2007-11-26 drh: int set = (nCol==nSorted); 62f37c9722 2007-11-26 drh: int desc = !strcmp(zDirection,"DESC"); 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Clicking same column header 3 times in a row resets any sorting. 62f37c9722 2007-11-26 drh: ** Note that we link to rptview, which means embedded reports will get 62f37c9722 2007-11-26 drh: ** sent to the actual report view page as soon as a user tries to do 62f37c9722 2007-11-26 drh: ** any sorting. I don't see that as a Bad Thing. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: if(set && desc){ 62f37c9722 2007-11-26 drh: @ <th bgcolor="%s(BG1)" class="bkgnd1"> 62f37c9722 2007-11-26 drh: @ <a href="rptview?rn=%d(rn)%s(zExtra)">%h(zCol)</a></th> 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: if(set){ 62f37c9722 2007-11-26 drh: @ <th bgcolor="%s(BG1)" class="bkgnd1"><a 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: @ <th><a 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ href="rptview?rn=%d(rn)&order_by=%d(nCol)&\ 62f37c9722 2007-11-26 drh: @ order_dir=%s(desc?"ASC":"DESC")\ 62f37c9722 2007-11-26 drh: @ %s(zExtra)">%h(zCol)</a></th> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 55342eb9fb 2008-05-17 drh: /* 55342eb9fb 2008-05-17 drh: ** The state of the report generation. 55342eb9fb 2008-05-17 drh: */ 62f37c9722 2007-11-26 drh: struct GenerateHTML { 86ed68ba34 2008-05-17 drh: int rn; /* Report number */ 86ed68ba34 2008-05-17 drh: int nCount; /* Row number */ 86ed68ba34 2008-05-17 drh: int nCol; /* Number of columns */ 86ed68ba34 2008-05-17 drh: int isMultirow; /* True if multiple table rows per query result row */ 86ed68ba34 2008-05-17 drh: int iNewRow; /* Index of first column that goes on separate row */ 86ed68ba34 2008-05-17 drh: int iBg; /* Index of column that defines background color */ 62f37c9722 2007-11-26 drh: }; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** The callback function for db_query 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: static int generate_html( 55342eb9fb 2008-05-17 drh: void *pUser, /* Pointer to output state */ 62f37c9722 2007-11-26 drh: int nArg, /* Number of columns in this result row */ 62f37c9722 2007-11-26 drh: char **azArg, /* Text of data in all columns */ 62f37c9722 2007-11-26 drh: char **azName /* Names of the columns */ 62f37c9722 2007-11-26 drh: ){ 55342eb9fb 2008-05-17 drh: struct GenerateHTML *pState = (struct GenerateHTML*)pUser; 62f37c9722 2007-11-26 drh: int i; ce277cadda 2008-05-25 drh: const char *zTid; /* Ticket UUID. (value of column named '#') */ 86ed68ba34 2008-05-17 drh: int rn; /* Report number */ 62f37c9722 2007-11-26 drh: char *zBg = 0; /* Use this background color */ 62f37c9722 2007-11-26 drh: char zPage[30]; /* Text version of the ticket number */ 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* Get the report number 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: rn = pState->rn; 62f37c9722 2007-11-26 drh: 86ed68ba34 2008-05-17 drh: /* Do initialization 62f37c9722 2007-11-26 drh: */ 86ed68ba34 2008-05-17 drh: if( pState->nCount==0 ){ 58f15e92ae 2009-01-08 drh: /* Turn off the authorizer. It is no longer doing anything since the 58f15e92ae 2009-01-08 drh: ** query has already been prepared. 58f15e92ae 2009-01-08 drh: */ 58f15e92ae 2009-01-08 drh: sqlite3_set_authorizer(g.db, 0, 0); 58f15e92ae 2009-01-08 drh: 86ed68ba34 2008-05-17 drh: /* Figure out the number of columns, the column that determines background 86ed68ba34 2008-05-17 drh: ** color, and whether or not this row of data is represented by multiple 86ed68ba34 2008-05-17 drh: ** rows in the table. 86ed68ba34 2008-05-17 drh: */ 86ed68ba34 2008-05-17 drh: pState->nCol = 0; 86ed68ba34 2008-05-17 drh: pState->isMultirow = 0; 86ed68ba34 2008-05-17 drh: pState->iNewRow = -1; 86ed68ba34 2008-05-17 drh: pState->iBg = -1; 86ed68ba34 2008-05-17 drh: for(i=0; i<nArg; i++){ 86ed68ba34 2008-05-17 drh: if( azName[i][0]=='b' && strcmp(azName[i],"bgcolor")==0 ){ 86ed68ba34 2008-05-17 drh: pState->iBg = i; 86ed68ba34 2008-05-17 drh: continue; 86ed68ba34 2008-05-17 drh: } 86ed68ba34 2008-05-17 drh: if( g.okWrite && azName[i][0]=='#' ){ 86ed68ba34 2008-05-17 drh: pState->nCol++; 86ed68ba34 2008-05-17 drh: } 86ed68ba34 2008-05-17 drh: if( !pState->isMultirow ){ 86ed68ba34 2008-05-17 drh: if( azName[i][0]=='_' ){ 86ed68ba34 2008-05-17 drh: pState->isMultirow = 1; 86ed68ba34 2008-05-17 drh: pState->iNewRow = i; 86ed68ba34 2008-05-17 drh: }else{ 86ed68ba34 2008-05-17 drh: pState->nCol++; 86ed68ba34 2008-05-17 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 86ed68ba34 2008-05-17 drh: 86ed68ba34 2008-05-17 drh: /* The first time this routine is called, output a table header 86ed68ba34 2008-05-17 drh: */ 55342eb9fb 2008-05-17 drh: @ <tr> ce277cadda 2008-05-25 drh: zTid = 0; 55342eb9fb 2008-05-17 drh: for(i=0; i<nArg; i++){ 55342eb9fb 2008-05-17 drh: char *zName = azName[i]; 86ed68ba34 2008-05-17 drh: if( i==pState->iBg ) continue; 86ed68ba34 2008-05-17 drh: if( pState->iNewRow>=0 && i>=pState->iNewRow ){ ce277cadda 2008-05-25 drh: if( g.okWrite && zTid ){ 55342eb9fb 2008-05-17 drh: @ <th> </th> ce277cadda 2008-05-25 drh: zTid = 0; 55342eb9fb 2008-05-17 drh: } 55342eb9fb 2008-05-17 drh: if( zName[0]=='_' ) zName++; 86ed68ba34 2008-05-17 drh: @ </tr><tr><th colspan=%d(pState->nCol)>%h(zName)</th> 55342eb9fb 2008-05-17 drh: }else{ 55342eb9fb 2008-05-17 drh: if( zName[0]=='#' ){ ce277cadda 2008-05-25 drh: zTid = zName; 62f37c9722 2007-11-26 drh: } ce277cadda 2008-05-25 drh: @ <th>%h(zName)</th> 55342eb9fb 2008-05-17 drh: } 62f37c9722 2007-11-26 drh: } ce277cadda 2008-05-25 drh: if( g.okWrite && zTid ){ 62f37c9722 2007-11-26 drh: @ <th> </th> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ </tr> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( azArg==0 ){ 86ed68ba34 2008-05-17 drh: @ <tr><td colspan="%d(pState->nCol)"> 62f37c9722 2007-11-26 drh: @ <i>No records match the report criteria</i> 62f37c9722 2007-11-26 drh: @ </td></tr> 62f37c9722 2007-11-26 drh: return 0; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: ++pState->nCount; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* Output the separator above each entry in a table which has multiple lines 62f37c9722 2007-11-26 drh: ** per database entry. 62f37c9722 2007-11-26 drh: */ 86ed68ba34 2008-05-17 drh: if( pState->iNewRow>=0 ){ 86ed68ba34 2008-05-17 drh: @ <tr><td colspan=%d(pState->nCol)><font size=1> </font></td></tr> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* Output the data for this entry from the database 62f37c9722 2007-11-26 drh: */ 86ed68ba34 2008-05-17 drh: zBg = pState->iBg>=0 ? azArg[pState->iBg] : 0; 62f37c9722 2007-11-26 drh: if( zBg==0 ) zBg = "white"; 62f37c9722 2007-11-26 drh: @ <tr bgcolor="%h(zBg)"> ce277cadda 2008-05-25 drh: zTid = 0; 62f37c9722 2007-11-26 drh: zPage[0] = 0; 62f37c9722 2007-11-26 drh: for(i=0; i<nArg; i++){ 62f37c9722 2007-11-26 drh: char *zData; 86ed68ba34 2008-05-17 drh: if( i==pState->iBg ) continue; 62f37c9722 2007-11-26 drh: zData = azArg[i]; 62f37c9722 2007-11-26 drh: if( zData==0 ) zData = ""; 86ed68ba34 2008-05-17 drh: if( pState->iNewRow>=0 && i>=pState->iNewRow ){ ce277cadda 2008-05-25 drh: if( zTid && g.okWrite ){ ce277cadda 2008-05-25 drh: @ <td valign="top"><a href="tktedit/%h(zTid)">edit</a></td> ce277cadda 2008-05-25 drh: zTid = 0; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: if( zData[0] ){ ce277cadda 2008-05-25 drh: Blob content; 86ed68ba34 2008-05-17 drh: @ </tr><tr bgcolor="%h(zBg)"><td colspan=%d(pState->nCol)> ce277cadda 2008-05-25 drh: blob_init(&content, zData, -1); ce277cadda 2008-05-25 drh: wiki_convert(&content, 0, 0); ce277cadda 2008-05-25 drh: blob_reset(&content); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: }else if( azName[i][0]=='#' ){ ce277cadda 2008-05-25 drh: zTid = zData; ce277cadda 2008-05-25 drh: @ <td valign="top"><a href="tktview?name=%h(zData)">%h(zData)</a></td> 62f37c9722 2007-11-26 drh: }else if( zData[0]==0 ){ 62f37c9722 2007-11-26 drh: @ <td valign="top"> </td> 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: @ <td valign="top"> 55342eb9fb 2008-05-17 drh: @ %h(zData) 62f37c9722 2007-11-26 drh: @ </td> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } ce277cadda 2008-05-25 drh: if( zTid && g.okWrite ){ ce277cadda 2008-05-25 drh: @ <td valign="top"><a href="tktedit/%h(zTid)">edit</a></td> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ </tr> 62f37c9722 2007-11-26 drh: return 0; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Output the text given in the argument. Convert tabs and newlines into 62f37c9722 2007-11-26 drh: ** spaces. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: static void output_no_tabs(const char *z){ 62f37c9722 2007-11-26 drh: while( z && z[0] ){ 62f37c9722 2007-11-26 drh: int i, j; 62f37c9722 2007-11-26 drh: for(i=0; z[i] && (!isspace(z[i]) || z[i]==' '); i++){} 62f37c9722 2007-11-26 drh: if( i>0 ){ 62f37c9722 2007-11-26 drh: cgi_printf("%.*s", i, z); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: for(j=i; isspace(z[j]); j++){} 62f37c9722 2007-11-26 drh: if( j>i ){ 62f37c9722 2007-11-26 drh: cgi_printf("%*s", j-i, ""); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: z += j; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Output a row as a tab-separated line of text. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: static int output_tab_separated( 62f37c9722 2007-11-26 drh: void *pUser, /* Pointer to row-count integer */ 62f37c9722 2007-11-26 drh: int nArg, /* Number of columns in this result row */ 62f37c9722 2007-11-26 drh: char **azArg, /* Text of data in all columns */ 62f37c9722 2007-11-26 drh: char **azName /* Names of the columns */ 62f37c9722 2007-11-26 drh: ){ 62f37c9722 2007-11-26 drh: int *pCount = (int*)pUser; 62f37c9722 2007-11-26 drh: int i; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: if( *pCount==0 ){ 62f37c9722 2007-11-26 drh: for(i=0; i<nArg; i++){ 62f37c9722 2007-11-26 drh: output_no_tabs(azName[i]); 62f37c9722 2007-11-26 drh: cgi_printf("%c", i<nArg-1 ? '\t' : '\n'); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: ++*pCount; 62f37c9722 2007-11-26 drh: for(i=0; i<nArg; i++){ 62f37c9722 2007-11-26 drh: output_no_tabs(azArg[i]); 62f37c9722 2007-11-26 drh: cgi_printf("%c", i<nArg-1 ? '\t' : '\n'); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: return 0; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** Generate HTML that describes a color key. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: void output_color_key(const char *zClrKey, int horiz, char *zTabArgs){ 62f37c9722 2007-11-26 drh: int i, j, k; 62f37c9722 2007-11-26 drh: char *zSafeKey, *zToFree; 62f37c9722 2007-11-26 drh: while( isspace(*zClrKey) ) zClrKey++; 62f37c9722 2007-11-26 drh: if( zClrKey[0]==0 ) return; 62f37c9722 2007-11-26 drh: @ <table %s(zTabArgs)> 62f37c9722 2007-11-26 drh: if( horiz ){ 62f37c9722 2007-11-26 drh: @ <tr> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: zToFree = zSafeKey = mprintf("%h", zClrKey); 62f37c9722 2007-11-26 drh: while( zSafeKey[0] ){ 62f37c9722 2007-11-26 drh: while( isspace(*zSafeKey) ) zSafeKey++; 62f37c9722 2007-11-26 drh: for(i=0; zSafeKey[i] && !isspace(zSafeKey[i]); i++){} 62f37c9722 2007-11-26 drh: for(j=i; isspace(zSafeKey[j]); j++){} 62f37c9722 2007-11-26 drh: for(k=j; zSafeKey[k] && zSafeKey[k]!='\n' && zSafeKey[k]!='\r'; k++){} 62f37c9722 2007-11-26 drh: if( !horiz ){ 62f37c9722 2007-11-26 drh: cgi_printf("<tr bgcolor=\"%.*s\"><td>%.*s</td></tr>\n", 62f37c9722 2007-11-26 drh: i, zSafeKey, k-j, &zSafeKey[j]); 62f37c9722 2007-11-26 drh: }else{ 62f37c9722 2007-11-26 drh: cgi_printf("<td bgcolor=\"%.*s\">%.*s</td>\n", 62f37c9722 2007-11-26 drh: i, zSafeKey, k-j, &zSafeKey[j]); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: zSafeKey += k; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: free(zToFree); 62f37c9722 2007-11-26 drh: if( horiz ){ 62f37c9722 2007-11-26 drh: @ </tr> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: @ </table> 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** WEBPAGE: /rptview 62f37c9722 2007-11-26 drh: ** 62f37c9722 2007-11-26 drh: ** Generate a report. The rn query parameter is the report number 62f37c9722 2007-11-26 drh: ** corresponding to REPORTFMT.RN. If the tablist query parameter exists, 62f37c9722 2007-11-26 drh: ** then the output consists of lines of tab-separated fields instead of 62f37c9722 2007-11-26 drh: ** an HTML table. 62f37c9722 2007-11-26 drh: */ dff17229ff 2007-12-02 drh: void rptview_page(void){ 62f37c9722 2007-11-26 drh: int count = 0; 62f37c9722 2007-11-26 drh: int rn; 62f37c9722 2007-11-26 drh: char *zSql; 62f37c9722 2007-11-26 drh: char *zTitle; 62f37c9722 2007-11-26 drh: char *zOwner; 62f37c9722 2007-11-26 drh: char *zClrKey; 62f37c9722 2007-11-26 drh: int tabs; 62f37c9722 2007-11-26 drh: Stmt q; 21326fb6f7 2008-07-19 drh: char *zErr1 = 0; 21326fb6f7 2008-07-19 drh: char *zErr2 = 0; 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: login_check_credentials(); 62f37c9722 2007-11-26 drh: if( !g.okRead ){ login_needed(); return; } 62f37c9722 2007-11-26 drh: rn = atoi(PD("rn","0")); 62f37c9722 2007-11-26 drh: if( rn==0 ){ 62f37c9722 2007-11-26 drh: cgi_redirect("reportlist"); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: tabs = P("tablist")!=0; 55342eb9fb 2008-05-17 drh: /* view_add_functions(tabs); */ 62f37c9722 2007-11-26 drh: db_prepare(&q, 62f37c9722 2007-11-26 drh: "SELECT title, sqlcode, owner, cols FROM reportfmt WHERE rn=%d", rn); 62f37c9722 2007-11-26 drh: if( db_step(&q)!=SQLITE_ROW ){ 62f37c9722 2007-11-26 drh: cgi_redirect("reportlist"); 62f37c9722 2007-11-26 drh: return; 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: zTitle = db_column_malloc(&q, 0); 62f37c9722 2007-11-26 drh: zSql = db_column_malloc(&q, 1); 62f37c9722 2007-11-26 drh: zOwner = db_column_malloc(&q, 2); 62f37c9722 2007-11-26 drh: zClrKey = db_column_malloc(&q, 3); 62f37c9722 2007-11-26 drh: db_finalize(&q); 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: if( P("order_by") ){ 62f37c9722 2007-11-26 drh: /* 62f37c9722 2007-11-26 drh: ** If the user wants to do a column sort, wrap the query into a sub 62f37c9722 2007-11-26 drh: ** query and then sort the results. This is a whole lot easier than 62f37c9722 2007-11-26 drh: ** trying to insert an ORDER BY into the query itself, especially 62f37c9722 2007-11-26 drh: ** if the query is already ordered. 62f37c9722 2007-11-26 drh: */ 62f37c9722 2007-11-26 drh: int nField = atoi(P("order_by")); 62f37c9722 2007-11-26 drh: if( nField > 0 ){ 62f37c9722 2007-11-26 drh: const char* zDir = PD("order_dir",""); 62f37c9722 2007-11-26 drh: zDir = !strcmp("ASC",zDir) ? "ASC" : "DESC"; 62f37c9722 2007-11-26 drh: zSql = mprintf("SELECT * FROM (%s) ORDER BY %d %s", zSql, nField, zDir); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: 62f37c9722 2007-11-26 drh: count = 0; 62f37c9722 2007-11-26 drh: if( !tabs ){ 62f37c9722 2007-11-26 drh: struct GenerateHTML sState; 62f37c9722 2007-11-26 drh: 55342eb9fb 2008-05-17 drh: db_multi_exec("PRAGMA empty_result_callbacks=ON"); 62f37c9722 2007-11-26 drh: style_submenu_element("Raw", "Raw", 55342eb9fb 2008-05-17 drh: "rptview?tablist=1&%s", PD("QUERY_STRING","")); 62f37c9722 2007-11-26 drh: if( g.okAdmin d3e711fd2f 2008-07-15 drh: || (g.okTktFmt && g.zLogin && zOwner && strcmp(g.zLogin,zOwner)==0) ){ 55342eb9fb 2008-05-17 drh: style_submenu_element("Edit", "Edit", "rptedit?rn=%d", rn); 62f37c9722 2007-11-26 drh: } d3e711fd2f 2008-07-15 drh: if( g.okTktFmt ){ d3e711fd2f 2008-07-15 drh: style_submenu_element("SQL", "SQL", "rptsql?rn=%d",rn); d3e711fd2f 2008-07-15 drh: } 62f37c9722 2007-11-26 drh: style_header(zTitle); 62f37c9722 2007-11-26 drh: output_color_key(zClrKey, 1, 62f37c9722 2007-11-26 drh: "border=0 cellpadding=3 cellspacing=0 class=\"report\""); 62f37c9722 2007-11-26 drh: @ <table border=1 cellpadding=2 cellspacing=0 class="report"> 62f37c9722 2007-11-26 drh: sState.rn = rn; 62f37c9722 2007-11-26 drh: sState.nCount = 0; 21326fb6f7 2008-07-19 drh: sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr1); 21326fb6f7 2008-07-19 drh: sqlite3_exec(g.db, zSql, generate_html, &sState, &zErr2); 21326fb6f7 2008-07-19 drh: sqlite3_set_authorizer(g.db, 0, 0); 62f37c9722 2007-11-26 drh: @ </table> 21326fb6f7 2008-07-19 drh: if( zErr1 ){ 21326fb6f7 2008-07-19 drh: @ <p><font color="red"><b>Error: %h(zErr1)</b></font></p> 21326fb6f7 2008-07-19 drh: }else if( zErr2 ){ 21326fb6f7 2008-07-19 drh: @ <p><font color="red"><b>Error: %h(zErr2)</b></font></p> 21326fb6f7 2008-07-19 drh: } 62f37c9722 2007-11-26 drh: style_footer(); 62f37c9722 2007-11-26 drh: }else{ 21326fb6f7 2008-07-19 drh: sqlite3_set_authorizer(g.db, report_query_authorizer, (void*)&zErr1); 21326fb6f7 2008-07-19 drh: sqlite3_exec(g.db, zSql, output_tab_separated, &count, &zErr2); 21326fb6f7 2008-07-19 drh: sqlite3_set_authorizer(g.db, 0, 0); 62f37c9722 2007-11-26 drh: cgi_set_content_type("text/plain"); 62f37c9722 2007-11-26 drh: } 62f37c9722 2007-11-26 drh: }
File Annotation