# Before you can run this program, you must create the quotations
# table in the server.  To do this, run SQP*Plus and feed Oracle the following data model:

#  create table quotations (
#  	quotation_id	integer primary key,
#  	insertion_date	date,
#  	author_name	varchar(100) not null,
#  	category	varchar(100),
#  	quote		varchar(4000)
#  );


# This page is designed so that it might start up getting a variable
# "order_by".  Note the URL in the link assinged to "option", which
# will be generated below as part of the page.  The optional
# argument to set_the_usual_form_variables (here 0) prevents the
# function from signalling an error if no argument is passed.

set_the_usual_form_variables 0

# Begin generating the page by putting out the usual HTTP headers, a
# page header, and a title

ReturnHeaders 

ns_write "
[ad_header "Quotations"]

<h2>Quotations</h2>

<hr>

<ul>
"

# Now check "order_by" and create a link (in the variable "option")
# that the user can click on to choose the order in which the
# quotes will be listed.  The ordering will be determined by the SQL
# query (in the variable "sql_query"), which is used below to extract rows
# from the table.

if { [info exists order_by] && $order_by == "date" } {
    set sql_query "select * from quotations order by insertion_date"
    set option "<a href=\"quotations.tcl?order_by=category\">sort by category</a>"
} else {
    set sql_query "select * from quotations order by category"
    set option "<a href=\"quotations.tcl?order_by=date\">sort by insertion date</a>"
}

# Now get a database handle (i.e., a connection to the database) and
# use this with AOLserver's ns_db API to select from the
# table using the query sql_query.  

set db [ns_db gethandle]
set selection [ns_db select $db $sql_query] 

# The value ("selection") returned by the ns_db select API is a "set id"
# that specifies an AOLServer ns_set data structure of {key value}
# pairs.  The keys are the names of the selected columns from the table.
# The API is designed so that subsequents calls to ns_db getrow, using
# the database handle and set id, will modify this set structure. Each
# successive getrow will change the values associated with the keys to
# be values in each corresponding column in successive rows selected
# from the table.

# This lets us loop through the structure and write out the data from
# successive rows.  The toolkit command set_variables_after_query is a
# convenient utility, which binds names corresponding to the
# columns (the set keys) to the row elements (the corresponding set values).


set counter 0
while { [ns_db getrow $db $selection] } {
    # a proc from utilities.tcl that magically sets 
    # local vars to the column names that we got back from 
    # Oracle; note that this depends on the use of $selection
    set_variables_after_query
    incr counter
    ns_write "<li>$author_name: \"$quote\"\n"
}

if { $counter == 0 } {
    ns_write "there are no quotations in the database right now"
}


ns_write "
</ul>

$option

<h3>Add a Quotation</h3>

<form method=post action=\"quotation-add.tcl\">

<table>
<tr>
 <th>Author
 <td><input type=text size=30 name=author_name>
</tr>
<tr>
 <th>Category
 <td><input type=text size=20 name=category>
</tr>
<tr>
 <th>Quotation
 <td><textarea name=quote cols=50 rows=8></textarea>
</tr>

</table>

<center>
<input type=submit value=\"Add\">
</center>
</form>

[ad_footer]
"


