[interchange-cvs] interchange - heins modified 2 files
interchange-core@icdevgroup.org
interchange-core@icdevgroup.org
Tue May 13 11:05:00 2003
User: heins
Date: 2003-05-13 15:04:48 GMT
Modified: dist/lib/UI/pages/admin direct_sql.html export_table.html
Log:
* Add "Download slice" functionality to table export page. Allows you to
select arbitrary columns to directly download.
* Add ability to download TAB-delimited results of an arbitrary SQL
query. You can do:
SELECT sku, count(code) as times_ordered, sum(quantity) as qty
FROM orderline
GROUP BY sku
and get downloaded to a file:
sku times_ordered total
0738417912 1 2
0972355901 1 1
0972355936 1 4
V4081300077 1 1
V4081300116 1 1
V4081300218 4 5
V4081300218cd 3 3
V4081300222cd 1 1
V4081300233 11 17
V4081300376 3 3
V4081300469 1 1
V4081300493 1 1
V4081300498 1 1
Revision Changes Path
1.3 +40 -2 interchange/dist/lib/UI/pages/admin/direct_sql.html
rev 1.3, prev_rev 1.2
Index: direct_sql.html
===================================================================
RCS file: /var/cvs/interchange/dist/lib/UI/pages/admin/direct_sql.html,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- direct_sql.html 4 Feb 2002 08:25:54 -0000 1.2
+++ direct_sql.html 13 May 2003 15:04:48 -0000 1.3
@@ -30,6 +30,9 @@
$CGI->{mv_data_table} = $1;
delete $CGI->{rc};
delete $CGI->{list};
+ if($CGI->{output_action} eq 'download') {
+ $CGI->{download} = 1;
+ }
$CGI->{html} = 1;
# $Scratch->{message} = qq{
# Rows selected by query:
@@ -83,10 +86,19 @@
]Return to [cgi mv_data_table] edit</a>
[/if]
-<FORM ACTION="[area @@MV_PAGE@@]" name=query>
+<FORM ACTION="[area @@MV_PAGE@@]" name=query method=POST>
+<input type=hidden name=mv_nextpage value="@@MV_PAGE@@">
+<input type=hidden name=mv_session_id value="[data session id]">
<table>
<tr>
+ <td>
+ <input type=radio value=display name="output_action" onClick="this.form.action='[area href="@@MV_PAGE@@"]'" CHECKED> Display
+ <input type=radio value=download name="output_action" onClick="this.form.action='[area href="process/output.txt" add_dot_html=0]'"> Download
+ </td>
+
+</tr>
+<tr>
<td>
<textarea name=sql rows=4 cols=80>[cgi sql]</textarea>
</td>
@@ -132,7 +144,32 @@
[scratchd message]
</blockquote>
<table border=1>
-[if cgi html]
+[if cgi download]
+
+ [deliver interpolate=1][query
+ table="[cgi mv_data_table]"
+ st=db
+ list=1
+ sql=`$sql`
+ ml="[cgi limit]"
+ ][sql-sub line_proc]
+ shift;
+ my $row = shift;
+ my $out = '';
+ if(! $t_header_done++) {
+ my $o = shift;
+ $out .= join "\t", @{$o->{mv_return_fields}};
+ $out .= "\n";
+ }
+ for(@$row) {
+ s/\r?\n/\r/g;
+ s/\t/ /g;
+ }
+ $out .= join "\t", @$row;
+ $out .= "\n";
+ [/sql-sub][sql-exec line_proc][/sql-exec][/query][/deliver]
+
+[elsif cgi html]
[query
table="[cgi mv_data_table]"
st=db
@@ -142,6 +179,7 @@
ml="[cgi limit]"
]
[/query]
+[/elsif]
[elsif cgi list]
[query
table="[cgi mv_data_table]"
2.7 +71 -1 interchange/dist/lib/UI/pages/admin/export_table.html
rev 2.7, prev_rev 2.6
Index: export_table.html
===================================================================
RCS file: /var/cvs/interchange/dist/lib/UI/pages/admin/export_table.html,v
retrieving revision 2.6
retrieving revision 2.7
diff -u -r2.6 -r2.7
--- export_table.html 18 Aug 2002 15:38:26 -0000 2.6
+++ export_table.html 13 May 2003 15:04:48 -0000 2.7
@@ -185,7 +185,9 @@
<tr>
<td colspan=2 class=rborder><img src="bg.gif" width=__UI_MAIN_WIDTH__ height=1></td>
</tr>
-
+<tr class=titlebox>
+<td colspan=2>[L]Export complete table to file[/L]</td>
+</tr>
<tr>
<td class=rnorm>
[L]Table to export[/L]
@@ -283,6 +285,74 @@
</table>
</form>
+
+[set joincol]
+ [calc]
+ my $cols = $CGI->{cols};
+ $cols =~ s/[\s,\0]+/,/g;
+ $cols =~ s/^,+//;
+ $cols =~ s/,+$//;
+ $CGI->{sql} = "select $cols from $CGI->{mv_data_table}";
+ return;
+ [/calc]
+[/set]
+<form action="[area href="process/[cgi mv_data_table]_slice.txt" add_dot_html=0] method="POST" name=dl>
+<input type=hidden name=mv_session_id value="[data session id]">
+<INPUT TYPE=hidden NAME=mv_action VALUE=return>
+<INPUT TYPE=hidden NAME=mv_data_table VALUE="[cgi mv_data_table]">
+<INPUT TYPE=hidden NAME=mv_click VALUE=joincol>
+<INPUT TYPE=hidden NAME=mv_nextpage VALUE="admin/direct_sql">
+<INPUT TYPE=hidden NAME=output_action VALUE=download>
+
+<table __UI_T_PROPERTIES__>
+<tr>
+<td colspan=2 class=rborder><img src="bg.gif" width=__UI_MAIN_WIDTH__ height=1></td>
+</tr>
+<tr class=titlebox>
+<td colspan=2>Download table slice</td>
+</tr>
+<tr>
+<td class=rnorm valign=top>
+[L]Fields from[/L] <i>[cgi mv_data_table]</i> [L]to download[/L]
+<br>
+<br>
+<br>
+ <A HREF="javascript:checkAll(document.dl,'cols')">Check all</A>
+
+ <A HREF="javascript:checkAll(document.dl,'cols',1)">Uncheck all</A>
+</td>
+<td class=rnorm>
+[display name=cols type=checkbox_left_4 passed="[db-columns table='[cgi mv_data_table]' joiner=',']"]
+</td>
+</tr>
+
+<tr>
+<td class=rnorm>
+[L]Output File[/L]
+</td>
+<td class=rnorm>
+ <input name=output_file value="" type=text onChange="this.form.action='[area href=process no_session_id=1 no_count=1 add_dot_html=0]/' + this.value">
+</td>
+</tr>
+
+<tr>
+<td class=rnorm>
+ [L]Export to file[/L]<br>
+ <small><I>([L]leave empty for default file[/L])</I></small>
+</td>
+<td class=rnorm>
+ <INPUT type=submit value="Download">
+</td>
+</tr>
+
+<tr>
+<td colspan=2 class=rborder><img src="bg.gif" width=__UI_MAIN_WIDTH__ height=1></td>
+</tr>
+
+</table>
+
+</form>
+
<!-- ----- END REAL STUFF ----- -->