Index: openacs-4/packages/ecommerce/www/product-search-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search-oracle.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/ecommerce/www/product-search-oracle.xql	30 Nov 2002 17:31:29 -0000	1.3
+++ openacs-4/packages/ecommerce/www/product-search-oracle.xql	10 Aug 2008 12:47:31 -0000	1.4
@@ -8,35 +8,89 @@
 
   <partialquery name="search_category">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, 
-          p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+        select r.*
+        from (
+            select rownum as r_rownum, q.*
+            from (
+                select p.product_name, p.product_id, p.dirname, 
+                       p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+                from ec_products_searchable p, ec_category_product_map c
+                where c.category_id=:category_id
+                and p.product_id=c.product_id
+                and pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
+                order by score desc
+            ) q
+        ) r
+        where r_rownum > :start_row
+        and r_rownum <= :start_row + :rows_per_page
+    </querytext>
+  </partialquery>
+
+  <partialquery name="search_count_category">      
+    <querytext>
+      select count(*)
       from ec_products_searchable p, ec_category_product_map c
       where c.category_id=:category_id
       and p.product_id=c.product_id
       and pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
-      order by score desc
     </querytext>
   </partialquery>
 
+
   <partialquery name="search_subcategory">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, 
-          p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+        select r.*
+        from (
+            select rownum as r_rownum, q.*
+            from (
+                select p.product_name, p.product_id, p.dirname, 
+                       p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+                from ec_products_searchable p, ec_subcategory_product_map c
+                where c.subcategory_id=:subcategory_id
+                and p.product_id=c.product_id
+                and pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
+                order by score desc
+            ) q
+        ) r
+        where r_rownum > :start_row
+        and r_rownum <= :start_row + :rows_per_page
+    </querytext>
+  </partialquery>
+
+  <partialquery name="search_count_subcategory">      
+    <querytext>
+      select count(*)
       from ec_products_searchable p, ec_subcategory_product_map c
       where c.subcategory_id=:subcategory_id
       and p.product_id=c.product_id
       and pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
-      order by score desc
     </querytext>
   </partialquery>
 
+
   <partialquery name="search_all">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, 
-          p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+        select r.*
+        from (
+            select rownum as r_rownum, q.*
+            from (
+                select p.product_name, p.product_id, p.dirname, 
+                       p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+                from ec_products_searchable p
+                where pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
+                order by score desc
+            ) q
+        ) r
+        where r_rownum > :start_row
+        and r_rownum <= :start_row + :rows_per_page
+    </querytext>
+  </partialquery>
+
+  <partialquery name="search_count_all">      
+    <querytext>
+      select count(*)
       from ec_products_searchable p
       where pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
-      order by score desc
     </querytext>
   </partialquery>
 
Index: openacs-4/packages/ecommerce/www/product-search-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search-postgresql.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/ecommerce/www/product-search-postgresql.xql	30 Nov 2002 17:31:29 -0000	1.3
+++ openacs-4/packages/ecommerce/www/product-search-postgresql.xql	10 Aug 2008 12:47:31 -0000	1.4
@@ -8,36 +8,64 @@
 
   <partialquery name="search_category">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, p.one_line_description, 
-          pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) as score
+      select p.product_name, p.product_id, p.dirname, p.one_line_description, p.sku,
+          pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) as score
       from ec_products_searchable p, ec_category_product_map c
       where c.category_id=:category_id
       and p.product_id=c.product_id
-      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
-      order by score desc
+      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+      order by score desc limit :rows_per_page offset :start_row
     </querytext>
   </partialquery>
 
+  <partialquery name="search_count_category">      
+    <querytext>
+      select count(*) as search_count
+      from ec_products_searchable p, ec_category_product_map c
+      where c.category_id=:category_id
+      and p.product_id=c.product_id
+      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+    </querytext>
+  </partialquery>
+
   <partialquery name="search_subcategory">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, p.one_line_description, 
-	  pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) as score
+      select p.product_name, p.product_id, p.dirname, p.one_line_description, p.sku,
+	  pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) as score
       from ec_products_searchable p, ec_subcategory_product_map c
       where c.subcategory_id=:subcategory_id
       and p.product_id=c.product_id
-      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
-      order by score desc
+      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+      order by score desc limit :rows_per_page offset :start_row
     </querytext>
   </partialquery>
 
+  <partialquery name="search_count_subcategory">      
+    <querytext>
+      select count(*) as search_count
+      from ec_products_searchable p, ec_subcategory_product_map c
+      where c.subcategory_id=:subcategory_id
+      and p.product_id=c.product_id
+      and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+    </querytext>
+  </partialquery>
+
   <partialquery name="search_all">      
     <querytext>
-      select p.product_name, p.product_id, p.dirname, p.one_line_description, 
-	  pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) as score
+      select p.product_name, p.product_id, p.dirname, p.one_line_description, p.sku,
+	  pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) as score
       from ec_products_searchable p
-      where pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
-      order by score desc
+      where pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+      order by score desc limit :rows_per_page offset :start_row
     </querytext>
   </partialquery>
 
+  <partialquery name="search_count_all">      
+    <querytext>
+      select count(*) as search_count
+      from ec_products_searchable p
+      where pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') ||  coalesce(p.detailed_description, '') || coalesce(p.sku, '') || coalesce(p.search_keywords,''), :search_text) > 0
+    </querytext>
+  </partialquery>
+
 </queryset>
Index: openacs-4/packages/ecommerce/www/product-search.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search.adp,v
diff -u -r1.6 -r1.7
--- openacs-4/packages/ecommerce/www/product-search.adp	13 Jan 2005 13:58:00 -0000	1.6
+++ openacs-4/packages/ecommerce/www/product-search.adp	10 Aug 2008 12:47:31 -0000	1.7
@@ -1,13 +1,16 @@
 <master>
-  <property name="title">@category_name@</property>
-  <property name="context_bar">@context_bar;noquote@</property>
-  <property name="signatory">@ec_system_owner;noquote@</property>
+  <property name="title">Product search @category_name@</property>
+  <property name="current_location">Product Search</property>
+  <property name="search_text">@search_text@</property>
+  <property name="category_id">@category_id@</property>
+  <property name="subcategory_id">@subcategory_id@</property>
+  <property name="combocategory_id">@combocategory_id@</property>
 
-  <property name="show_toolbar_p">t</property>
-
-  <blockquote>
+  <include src="/packages/ecommerce/lib/toolbar"/>
+  <include src="/packages/ecommerce/lib/searchbar" combocategory_id=@combocategory_id@ category_id=@category_id@ subcategory_id=@subcategory_id@ search_text=@search_text@ />
+  
     @search_results;noquote@
-  </blockquote>
+  
 
 
 
Index: openacs-4/packages/ecommerce/www/product-search.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search.tcl,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/ecommerce/www/product-search.tcl	1 Mar 2005 00:01:31 -0000	1.9
+++ openacs-4/packages/ecommerce/www/product-search.tcl	10 Aug 2008 12:47:31 -0000	1.10
@@ -1,6 +1,10 @@
 ad_page_contract {
     @param search_text
     @param combocategory_id:optional
+    @param category_id:optional
+    @param subcategory_id:optional
+    @param rows_per_page:optional How many products to display on the page
+    @param start_row:optional Where to begin from
     @param usca_p:optional
     
     This page searches for products either within a category (if specified) or
@@ -17,6 +21,8 @@
     {combocategory_id ""}
     {category_id ""}
     {subcategory_id ""}
+    {rows_per_page:naturalnum {[ad_parameter -package_id [ec_id] ProductsToDisplayPerPage ecommerce]}}
+    {start_row:naturalnum "0"}
     usca_p:optional
 }
 
@@ -48,8 +54,11 @@
 
 # filter overflow attempts from really long search strings
 set search_text "[string range $search_text 0 100 ]"
+# filter extra spaces
+regsub -all -- {\s+} $search_text { } search_text
+set search_text "[string trim $search_text]"
 
-ec_create_new_session_if_necessary [export_url_vars category_id search_text] cookies_are_not_required
+ec_create_new_session_if_necessary [export_url_vars category_id subcategory_id search_text] cookies_are_not_required
 if { [string compare $user_session_id "0"] != 0 } {
     db_dml insert_search_text_to_session_info "insert into ec_user_session_info (user_session_id, category_id, search_text) values (:user_session_id, :category_id, :search_text)"
 }
@@ -66,6 +75,7 @@
 
 if { ![empty_string_p $subcategory_id] && $subcategory_id > 0} {
     set query_string [db_map search_subcategory] 
+    set query_count_string [db_map search_count_subcategory] 
     # select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
     # from ec_products_searchable p, ec_subcategory_product_map c
     # where c.subcategory_id=:subcategory_id
@@ -75,6 +85,7 @@
 } else {
     if { ![empty_string_p $category_id] } {
 	set query_string [db_map search_category] 
+	set query_count_string [db_map search_count_category] 
 	# select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
 	# from ec_products_searchable p, ec_category_product_map c
 	# where c.category_id=:category_id
@@ -83,6 +94,7 @@
 	# order by score desc
     } else {
 	set query_string [db_map search_all]
+	set query_count_string [db_map search_count_all]
 	# select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
 	# from ec_products_searchable p
 	# where pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || p.search_keywords, :search_text) > 0
@@ -91,27 +103,86 @@
 }
 
 set search_string ""
-set search_count 0
+set page_count 0
+
+# set search_count equal to count(*) of query
+
+# getting the count of search results requires a second db hit, 
+# which becomes magnitudes faster than the tcl filtering that used to be in this loop
+db_1row get_search_count $query_count_string 
+
+set have_how_many_more_p "f"
+set end_row_of_next_page [expr $start_row + (2 * $rows_per_page)]
 db_foreach get_product_listing_from_search $query_string {
-    incr search_count
-    append search_string "
-      <table width=90%>
-        <tr>
-          <td valign=center>
-            <table>
-              <tr><td><a href=\"product?[export_url_vars product_id]\">$product_name</a></td></tr>
-              <tr><td>$one_line_description</td></tr>
-              <tr><td>[ec_price_line $product_id $user_id ""]</td></tr>
-            </table>
-          </td>
-          <td align=right valign=top>[ec_linked_thumbnail_if_it_exists $dirname "t" "t"]</td>
-        </tr>
-      </table>"
+
+    if {[f::even_p $page_count]} {
+        append search_string "<table width=\"90%\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\" align=\"center\">"
+    } else {
+        append search_string "<table width=\"90%\" bgcolor=\"#cccccc\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\" align=\"center\">"
+    }
+    append search_string "<tr>
+          <td><a href=\"product?[export_url_vars product_id]\">$product_name</a></td>
+          <td align=\"right\" valign=\"top\" rowspan=\"3\">[ec_linked_thumbnail_if_it_exists $dirname "t" "t"]</td>
+        </tr><tr>
+          <td>$one_line_description</td>
+        </tr><tr>
+          <td>[ec_price_line $product_id $user_id ""]</td>
+        </tr></table>"
+
+    incr page_count
 }
+set last_row_this_page [expr $page_count + $start_row ]
+if { $search_count > $end_row_of_next_page } {
+	# we know there are at least how_many more items to display next time
+	set have_how_many_more_p "t"
+} 
 
+if { $start_row >= $rows_per_page } {
+    set prev_link "<a href=[ad_conn url]?[export_url_vars category_id subcategory_id subsubcategory_id rows_per_page search_text]&start_row=[expr $start_row - $rows_per_page]>Previous $rows_per_page</a>"
+} else {
+    set prev_link ""
+}
+
+if { [string equal $have_how_many_more_p "t"] } {
+    set next_link "<a href=[ad_conn url]?[export_url_vars category_id subcategory_id subsubcategory_id rows_per_page search_text]&start_row=[expr $start_row + $rows_per_page]>Next $rows_per_page</a>"
+} else {
+    set number_of_remaining_products [expr $search_count - $start_row - $rows_per_page ]
+    if { $number_of_remaining_products > 0 } {
+	set next_link "<a href=[ad_conn url]?[export_url_vars category_id subcategory_id subsubcategory_id rows_per_page search_text]&start_row=[expr $start_row + $rows_per_page]>Next $number_of_remaining_products</a>"
+    } else {
+	set next_link ""
+    }
+}
+
+if { [empty_string_p $next_link] || [empty_string_p $prev_link] } {
+    set separator ""
+} else {
+    set separator "&nbsp;|&nbsp;"
+}
+
 if { $search_count == 0 } {
     set search_results "No products found."
 } else {
+    set search_results "<p> $search_count [ec_decode $search_count "1" "item found." "items found, most relevant first."]</p>"
+    if { $start_row != 0 || $search_count > $rows_per_page } {
+        if { [info exists last_row_this_page] } {
+            append search_results "<p>Showing items [expr $start_row + 1] to $last_row_this_page.</p>"
+        } else {
+            append search_results "<p>Display scope out of range of search results.</p>"
+        }
+    }
+    append search_results "${search_string}${prev_link}${separator}${next_link}"
+}
+set context_bar [template::adp_parse [acs_root_dir]/packages/[ad_conn package_key]/www/contextbar [list context_addition [list "[ec_system_name] search results"]]]
+set ec_system_owner [ec_system_owner]
+
+db_release_unused_handles
+ad_return_template
+
+
+if { $search_count == 0 } {
+    set search_results "No products found."
+} else {
     set search_results "<p> $search_count [ec_decode $search_count "1" "item found." "items found, most relevant first."]</p>$search_string"
 }
 set context_bar [template::adp_parse [acs_root_dir]/packages/[ad_conn package_key]/www/contextbar [list context_addition [list "[ec_system_name] search results"]]]