[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>
+			&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+			<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 ----- -->