Index: openacs-4/packages/recruiting/dotlrn-recruiting.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/Attic/dotlrn-recruiting.info,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/recruiting/dotlrn-recruiting.info	1 Aug 2002 00:45:49 -0000	1.3
+++ openacs-4/packages/recruiting/dotlrn-recruiting.info	1 Aug 2002 22:14:36 -0000	1.4
@@ -25,15 +25,18 @@
             <file type="query_file" db_type="postgresql" path="tcl/dotlrn-recruiting-procs-postgresql.xql"/>
             <file type="tcl_procs" path="tcl/dotlrn-recruiting-procs.tcl"/>
             <file type="tcl_procs" path="tcl/table-sorter-procs.tcl"/>
-            <file type="tcl_util" path="tcl/tableSorter.tcl"/>
             <file type="content_page" path="www/admin/assign-candidate-interview-2.tcl"/>
             <file type="content_page" path="www/admin/assign-candidate-interview.adp"/>
             <file type="content_page" path="www/admin/assign-candidate-interview.tcl"/>
             <file type="query_file" path="www/admin/assign-candidate-interview.xql"/>
+            <file type="content_page" path="www/admin/change-candidate-status.adp"/>
+            <file type="content_page" path="www/admin/change-candidate-status.tcl"/>
             <file type="content_page" path="www/admin/disable-criteria.tcl"/>
             <file type="query_file" path="www/admin/disable-criteria.xql"/>
             <file type="content_page" path="www/admin/disable-status-type.tcl"/>
             <file type="query_file" path="www/admin/disable-status-type.xql"/>
+            <file type="content_page" path="www/admin/edit-candidate.adp"/>
+            <file type="content_page" path="www/admin/edit-candidate.tcl"/>
             <file type="content_page" path="www/admin/edit-criteria.adp"/>
             <file type="content_page" path="www/admin/edit-criteria.tcl"/>
             <file type="content_page" path="www/admin/edit-status-type.adp"/>
@@ -65,11 +68,38 @@
             <file type="content_page" path="www/admin/status-help.adp"/>
             <file type="content_page" path="www/admin/status-help.tcl"/>
             <file type="query_file" path="www/admin/status-help.xql"/>
+            <file type="content_page" path="www/admin/view-average-ratings.adp"/>
+            <file type="content_page" path="www/admin/view-average-ratings.tcl"/>
+            <file type="query_file" path="www/admin/view-average-ratings.xql"/>
             <file type="content_page" path="www/admin/view-one-candidate.adp"/>
             <file type="content_page" path="www/admin/view-one-candidate.tcl"/>
             <file type="query_file" path="www/admin/view-one-candidate.xql"/>
-            <file type="content_page" path="www/list-candidates.tcl"/>
-            <file type="content_page" path="www/list-ratings.tcl"/>
+            <file type="content_page" path="www/admin/view-ratings-by-criteria.adp"/>
+            <file type="content_page" path="www/admin/view-ratings-by-criteria.tcl"/>
+            <file type="query_file" path="www/admin/view-ratings-by-criteria.xql"/>
+            <file type="query_file" db_type="postgresql" path="www/admin/view-ratings-postgresql.xql"/>
+            <file type="content_page" path="www/admin/view-ratings.adp"/>
+            <file type="content_page" path="www/admin/view-ratings.tcl"/>
+            <file type="query_file" path="www/admin/view-ratings.xql"/>
+            <file type="content_page" path="www/edit-ratings.adp"/>
+            <file type="content_page" path="www/edit-ratings.tcl"/>
+            <file type="query_file" path="www/edit-ratings.xql"/>
+            <file type="content_page" path="www/index.adp"/>
+            <file type="content_page" path="www/index.tcl"/>
+            <file type="query_file" path="www/index.xql"/>
+            <file type="content_page" path="www/my-completed-interviews.adp"/>
+            <file type="content_page" path="www/my-completed-interviews.tcl"/>
+            <file type="query_file" path="www/my-completed-interviews.xql"/>
+            <file type="content_page" path="www/my-new-interviews.adp"/>
+            <file type="content_page" path="www/my-new-interviews.tcl"/>
+            <file type="query_file" path="www/my-new-interviews.xql"/>
+            <file type="content_page" path="www/rate-candidate.adp"/>
+            <file type="content_page" path="www/rate-candidate.tcl"/>
+            <file type="query_file" path="www/rate-candidate.xql"/>
+            <file type="content_page" path="www/rating-sheet.adp"/>
+            <file type="content_page" path="www/rating-sheet.tcl"/>
+            <file type="query_file" path="www/rating-sheet.xql"/>
+            <file type="content_page" path="www/recruiting-master.adp"/>
         </files>
         <parameters>
         <!-- No version parameters -->
Index: openacs-4/packages/recruiting/sql/postgresql/recruiting-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/sql/postgresql/recruiting-create.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/recruiting/sql/postgresql/recruiting-create.sql	30 Jul 2002 22:38:34 -0000	1.3
+++ openacs-4/packages/recruiting/sql/postgresql/recruiting-create.sql	1 Aug 2002 22:14:36 -0000	1.4
@@ -153,7 +153,8 @@
        interviewer_id              integer 
                                    references users(user_id),
        candidate_id                integer
-                                   references recruiting_candidates(candidate_id)
+                                   references recruiting_candidates(candidate_id),
+       should_hire_p               boolean
 );
 
 select acs_object_type__create_type (
@@ -190,7 +191,8 @@
                                    references recruiting_criteria(criteria_id),
        rating                      integer
                                    constraint recruiting_rating_range
-                                   check (rating between 1 and 5)
+                                   check (rating between 1 and 5),
+       unique (package_id,interview_id,criteria_id)
 );
 
 select acs_object_type__create_type (
Index: openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs-postgresql.xql,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs-postgresql.xql	1 Aug 2002 00:45:49 -0000	1.4
+++ openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs-postgresql.xql	1 Aug 2002 22:14:36 -0000	1.5
@@ -148,7 +148,8 @@
         <querytext>
             select package_id,
                    interviewer_id,
-                   candidate_id
+                   candidate_id,
+                   should_hire_p
               from recruiting_interviews
              where interview_id = :interview_id;
         </querytext>
Index: openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs.tcl,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs.tcl	1 Aug 2002 00:45:49 -0000	1.5
+++ openacs-4/packages/recruiting/tcl/dotlrn-recruiting-procs.tcl	1 Aug 2002 22:14:36 -0000	1.6
@@ -308,10 +308,11 @@
 
         set interview(interview_id) $interview_id
         set interview(package_id) $package_id
-        set interview(interviewere_id) $interviewer_id
+        set interview(interviewer_id) $interviewer_id
         set interview(candidate_id) $candidate_id
+        set interview(should_hire_p) $should_hire_p
 
-        return [ns_set get interview]
+        return [array get interview]
     }
 }
 
Index: openacs-4/packages/recruiting/www/edit-ratings.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/edit-ratings.adp,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/edit-ratings.adp	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,6 @@
+<master src="recruiting-master">
+<property name="context_bar">@context_bar@</property>
+
+<h3>Edit Rate Sheet for @candidate_info.first_name@ @candidate_info.last_name@</h3>
+
+<formtemplate id="rate_sheet"></formtemplate>
Index: openacs-4/packages/recruiting/www/edit-ratings.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/edit-ratings.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/edit-ratings.tcl	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,77 @@
+ad_page_contract {
+    recruiting user interface
+    edit ratings
+
+    @author chak (chak@openforce.net)
+    @creation-date 2002-08-01
+    @version $Id: edit-ratings.tcl,v 1.1 2002/08/01 22:14:37 chak Exp $
+} {
+    interview_id
+    candidate_id
+}
+
+set package_id [ad_conn package_id]
+set user_id [ad_verify_and_get_user_id]
+
+array set interview_info [recruiting_interview::get -interview_id $interview_id]
+array set candidate_info [recruiting_candidate::get -candidate_id $candidate_id]
+
+form create rate_sheet
+
+set criteria_list [list]
+set criteria_ids [list]
+
+db_foreach get_criteria {} {
+    element create rate_sheet criteria_$criteria_id \
+        -label "<b>$criteria_name</b><br>$description" \
+        -datatype integer \
+        -widget radio \
+        -options [list [list "1 (low)" 1] [list 2 2] [list 3 3] [list 4 4] [list "5 (high)" 5] [list "Not Applicable / Don't Know" 0]] \
+        -value [db_string get_rating {} -default 0]
+
+    lappend criteria_ids $criteria_id
+    lappend criteria_list criteria_$criteria_id
+    set old_responses($criteria_id) [db_string get_rating {} -default 0]
+}
+
+element create rate_sheet candidate_id \
+        -widget hidden \
+        -value $candidate_id
+
+element create rate_sheet interview_id \
+        -widget hidden \
+        -value $interview_id
+
+element create rate_sheet should_hire_p \
+        -label "Should we hire $candidate_info(first_name) $candidate_info(last_name)?" \
+        -datatype text \
+        -widget radio \
+        -options [list [list Yes t] [list No f]] \
+        -value $interview_info(should_hire_p)
+
+
+if {[form is_valid rate_sheet]} {
+    form get_values rate_sheet \
+            should_hire_p
+
+
+    db_dml remove_old_ratings {}
+
+    foreach criteria_id $criteria_ids {
+        form get_values rate_sheet \
+                criteria_$criteria_id
+
+        recruiting_rating::new \
+                -package_id $package_id \
+                -interview_id $interview_id \
+                -criteria_id $criteria_id \
+                -rating [set criteria_$criteria_id]
+    }
+
+    db_dml set_should_hire {}
+
+    ad_returnredirect index
+    ad_script_abort
+}
+
+set context_bar [list [list "index" "Recruiting"] [list "my-completed-interviews" "My Completed Interviews"] "Edit Rate Sheet"]
Index: openacs-4/packages/recruiting/www/edit-ratings.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/edit-ratings.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/edit-ratings.xql	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,40 @@
+<?xml version="1.0"?>
+<queryset>
+
+    <fullquery name="get_criteria">
+        <querytext>
+                select criteria_id,
+                       criteria_name,
+                       description
+                  from recruiting_criteria
+                 where package_id = :package_id
+                   and enabled_p = 't'        
+        </querytext>
+    </fullquery>
+
+    <fullquery name="set_should_hire">
+        <querytext>
+             update recruiting_interviews
+                set should_hire_p = :should_hire_p
+              where interview_id = :interview_id
+        </querytext>
+    </fullquery>
+
+    <fullquery name="get_rating">
+        <querytext>
+            select r.rating
+              from recruiting_ratings r
+             where criteria_id = :criteria_id
+               and interview_id = :interview_id
+        </querytext>
+    </fullquery>
+
+    <fullquery name="remove_old_ratings">
+        <querytext>
+            delete 
+              from recruiting_ratings
+             where interview_id = :interview_id
+        </querytext>
+    </fullquery>
+
+</queryset>
Index: openacs-4/packages/recruiting/www/my-completed-interviews.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/my-completed-interviews.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/recruiting/www/my-completed-interviews.tcl	1 Aug 2002 21:18:09 -0000	1.1
+++ openacs-4/packages/recruiting/www/my-completed-interviews.tcl	1 Aug 2002 22:14:37 -0000	1.2
@@ -14,7 +14,7 @@
 table::setColumnHeadings interviews [list "Name" "Location" "Average Rating" "Hiring Recommendation"]
 table::setColumnAlignment interviews [list left left center center]
 db_foreach my_completed_interviews {} {
-    table::addSortableRow interviews [list "$last_name, $first_name" "$location" "$average_rating" "$should_hire_p" "(<a href=\"view-ratings?[export_vars interview_id]\">view my ratings</a>) (<a href=edit-ratings?[export_vars interview_id]>modify interview</a>)"]
+    table::addSortableRow interviews [list "$last_name, $first_name" "$location" "$average_rating" "$should_hire_p" "(<a href=\"rating-sheet?[export_vars interview_id]\">view my ratings</a>) (<a href=edit-ratings?[export_vars {candidate_id interview_id}]>modify interview</a>)"]
 } if_no_rows {
     table::addSortableRow interviews [list "You haven't interviewed any candidates."]
 }
Index: openacs-4/packages/recruiting/www/rating-sheet.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/rating-sheet.adp,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/rating-sheet.adp	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,10 @@
+<master src="recruiting-master">
+<property name="context_bar">@context_bar@</property>
+
+<table width=100%>
+<tr><td valign=top>
+<%= [table::outputTable ratings] %>
+</td><td valign=top>
+<%= [table::outputTable summary] %>
+</td></tr>
+</table>
Index: openacs-4/packages/recruiting/www/rating-sheet.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/rating-sheet.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/rating-sheet.tcl	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,46 @@
+ad_page_contract {
+    recruiting user interface
+
+    @author chak (chak@openforce.net)
+    @creation-date 2002-08-01
+    @version $Id: rating-sheet.tcl,v 1.1 2002/08/01 22:14:37 chak Exp $
+} {
+    interview_id
+}
+
+set package_id [ad_conn package_id]
+set user_id [ad_verify_and_get_user_id]
+
+array set interview_info [recruiting_interview::get -interview_id $interview_id]
+
+if {$user_id != $interview_info(interviewer_id)} {
+    
+    ad_script_abort
+}
+
+array set candidate_info [recruiting_candidate::get -candidate_id $interview_info(candidate_id)]
+
+set interviewer_id $interview_info(interviewer_id)
+set candidate_id $interview_info(candidate_id)
+
+table::setTitle ratings "Your ratings for $candidate_info(first_name) $candidate_info(last_name)"
+table::setColumnHeadings ratings [list "Criteria" "Rating"]
+table::setColumnAlignment ratings [list left center]
+table::setExportVars ratings [export_vars interview_id]
+
+db_foreach get_ratings {} {
+    table::addSortableRow ratings [list "<b>$criteria_name</b><br>$description" "<b>$rating</b>"]
+} if_no_rows {
+    table::addSortableRow ratings [list "You has not made any ratings for $candidate_info(first_name) $candidate_info(last_name)"]
+}
+
+table::setTitle summary "Summary Info"
+table::addUnsortedRow summary [list "Average rating:" [db_string get_average_rating {}]]
+table::addUnsortedRow summary [list "Hiring suggestion:" [db_string get_should_hire_p {}]]
+
+set context_bar [list \
+        [list "index" "Recruiting"] \
+        [list "my-completed-interviews" "My Completed Interviews"] \
+        "Interview Ratings"]
+
+ad_return_template
Index: openacs-4/packages/recruiting/www/rating-sheet.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/rating-sheet.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/rating-sheet.xql	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,36 @@
+<?xml version="1.0"?>
+<queryset>
+
+   <fullquery name="get_ratings">
+       <querytext>
+            select cr.criteria_id,
+                   cr.criteria_name, cr.description,
+                   r.rating
+              from recruiting_criteria cr,
+                   recruiting_ratings r
+             where r.interview_id = :interview_id
+               and r.criteria_id = cr.criteria_id
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_average_rating">
+       <querytext>
+            select round(avg(r.rating),1)
+              from recruiting_ratings r
+             where r.interview_id = :interview_id
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_should_hire_p">
+       <querytext>
+             select (CASE
+                      WHEN i.should_hire_p = 't'
+                      THEN 'Yes'
+                      WHEN i.should_hire_p = 'f'
+                      THEN 'No'
+                     END) as should_hire_p                  
+              from recruiting_interviews i
+             where i.interview_id = :interview_id
+       </querytext>
+    </fullquery>
+</queryset>
Index: openacs-4/packages/recruiting/www/admin/list-candidates.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/list-candidates.tcl,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/recruiting/www/admin/list-candidates.tcl	1 Aug 2002 21:18:09 -0000	1.3
+++ openacs-4/packages/recruiting/www/admin/list-candidates.tcl	1 Aug 2002 22:14:37 -0000	1.4
@@ -15,7 +15,11 @@
 table::setColumnHeadings candidates [list "Name" "Location" "Status" "Interviews"]
 table::setColumnAlignment candidates [list left left left center left]
 db_foreach get_candidates {} {
-    table::addSortableRow candidates [list "$first_name $last_name<br>(<a href=\"mailto:$email\">$email</a>)" "$city, $state" "$status" "$num_completed_interviews out of $num_interviews<br>completed" "(<a href=\"view-one-candidate?[export_vars candidate_id]\">details</a>)"]
+    table::addSortableRow candidates [list "$first_name $last_name<br>(<a href=\"mailto:$email\">$email</a>)" \
+            "$city, $state" \
+            "$status" \
+            "$num_completed_interviews out of $num_interviews<br>completed" \
+            "(<a href=\"view-one-candidate?[export_vars candidate_id]\">details</a>)"]
 } if_no_rows {
     table::addSortableRow candidates [list "No Candidates"]
 }
Index: openacs-4/packages/recruiting/www/admin/view-average-ratings.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/view-average-ratings.adp,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/admin/view-average-ratings.adp	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,10 @@
+<master src="recruiting-admin-master">
+<property name="context_bar">@context_bar@</property>
+
+<table width=100%>
+<tr><td valign=top>
+<%= [table::outputTable ratings] %>
+</td><td valign=top>
+<%= [table::outputTable summary] %>
+</td></tr>
+</table>
Index: openacs-4/packages/recruiting/www/admin/view-average-ratings.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/view-average-ratings.tcl,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/admin/view-average-ratings.tcl	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,35 @@
+ad_page_contract {
+    recruiting admin interface
+    access admin function such as: criteria specification 
+                               and interviewee status types
+
+    @author chak (chak@openforce.net)
+    @creation-date 2002-08-01
+    @version $Id: view-average-ratings.tcl,v 1.1 2002/08/01 22:14:37 chak Exp $
+} {
+    candidate_id:notnull
+}
+
+array set candidate_info [recruiting_candidate::get -candidate_id $candidate_id]
+
+table::setTitle ratings "Average ratings for $candidate_info(first_name) $candidate_info(last_name)"
+table::setColumnHeadings ratings [list "Criteria" "Average Rating"]
+table::setColumnAlignment ratings [list left center]
+db_foreach get_ratings_by_candidate {} {
+    table::addSortableRow ratings [list "$criteria_name (<a href=view-ratings-by-criteria?[export_vars {criteria_id candidate_id}]>view all $criteria_name ratings</a>)" "$rating"]
+} if_no_rows {
+    table::addSortableRow ratings [list "$candidate_info(first_name) $candidate_info(last_name) has no ratings."]
+}
+
+table::setTitle summary "Summary Information"
+table::addUnsortedRow summary [list "Overall rating:" [db_string get_average_rating {}]]
+table::addUnsortedRow summary [list "Votes to hire:" [db_string get_hire_votes {}]]
+table::addUnsortedRow summary [list "Votes against hiring:" [db_string get_no_hire_votes {}]]
+set context_bar [list \
+        [list "../" "Recruiting"] \
+        [list "index" "Admin"] \
+        [list "list-candidates" "Candidates"] \
+        [list "view-one-candidate?[export_vars candidate_id]" "One Candidate"] \
+        "Average Ratings"]
+
+ad_return_template
Index: openacs-4/packages/recruiting/www/admin/view-average-ratings.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/view-average-ratings.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/recruiting/www/admin/view-average-ratings.xql	1 Aug 2002 22:14:37 -0000	1.1
@@ -0,0 +1,46 @@
+<?xml version="1.0"?>
+<queryset>
+
+   <fullquery name="get_ratings_by_candidate">
+       <querytext>
+            select cr.criteria_id,
+                   cr.criteria_name,
+                   round(avg(r.rating),1) as rating
+              from recruiting_ratings r,
+                   recruiting_criteria cr
+             where cr.criteria_id = r.criteria_id
+               and r.interview_id = recruiting_interviews.interview_id
+               and recruiting_interviews.candidate_id = :candidate_id
+          group by cr.criteria_id, cr.criteria_name
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_average_rating">
+       <querytext>
+            select round(avg(r.rating),1)
+              from recruiting_ratings r,
+                   recruiting_interviews i
+             where r.interview_id = i.interview_id
+               and i.candidate_id = :candidate_id
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_hire_votes">
+       <querytext>
+            select count(*)
+              from recruiting_interviews i
+             where i.candidate_id = :candidate_id
+               and i.should_hire_p = 't'
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_no_hire_votes"> 
+       <querytext>
+            select count(*)
+              from recruiting_interviews i
+             where i.candidate_id = :candidate_id
+               and i.should_hire_p = 'f'
+       </querytext>
+   </fullquery>
+
+</queryset>
Index: openacs-4/packages/recruiting/www/admin/view-one-candidate.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/view-one-candidate.tcl,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/recruiting/www/admin/view-one-candidate.tcl	1 Aug 2002 21:18:09 -0000	1.3
+++ openacs-4/packages/recruiting/www/admin/view-one-candidate.tcl	1 Aug 2002 22:14:37 -0000	1.4
@@ -35,14 +35,28 @@
 set possible_ratings [db_string get_num_criteria {}]
 
 table::setTitle interviews "Interviews for this candidate"
-table::setColumnHeadings interviews [list "Interviewer" "Ratings"]
+table::setColumnHeadings interviews [list \
+        "Interviewer" \
+        "Ratings" \
+        "Average Rating" \
+        "Recommends Hiring"
+]
+
+table::setColumnAlignment interviews [list left left center center]
+
 table::setExportVars interviews [export_vars candidate_id]
 
 db_foreach get_interviews {} {
-    table::addUnsortedRow interviews [list "$last_name, $first_names" "$num_ratings out of $possible_ratings" "(<a href=view-ratings?[export_vars interview_id]>view ratings</a>)"]
+    table::addUnsortedRow interviews [list \
+            "$last_name, $first_names" \
+            "$num_ratings out of $possible_ratings" \
+            "[db_string get_average_rating {}]" \
+            "[db_string get_should_hire_p {}]" \
+            "(<a href=view-ratings?[export_vars interview_id]>view ratings</a>)"]
 } if_no_rows {
     table::addUnsortedRow interviews [list "No Interviews assigned. (<a href=assign-candidate-interview?[export_vars candidate_id]>assign one</a>)"]
 }
+table::addUnsortedRow interviews [list "" "" "" "" "(<a href=view-average-ratings?[export_vars candidate_id]>view average ratings</a>)"]
 
 table::setTitle options "Options"
 table::addUnsortedRow options [list "<a href=edit-candidate?[export_vars candidate_id]>Edit Information</a>"]
Index: openacs-4/packages/recruiting/www/admin/view-one-candidate.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/recruiting/www/admin/view-one-candidate.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/recruiting/www/admin/view-one-candidate.xql	31 Jul 2002 23:54:00 -0000	1.1
+++ openacs-4/packages/recruiting/www/admin/view-one-candidate.xql	1 Aug 2002 22:14:37 -0000	1.2
@@ -25,4 +25,26 @@
        </querytext>
    </fullquery>
 
+   <fullquery name="get_should_hire_p">
+       <querytext>
+             select (CASE
+                      WHEN i.should_hire_p = 't'
+                      THEN 'Yes'
+                      WHEN i.should_hire_p = 'f'
+                      THEN 'No'
+                     END) as should_hire_p                  
+              from recruiting_interviews i
+             where i.interview_id = :interview_id
+       </querytext>
+   </fullquery>
+
+   <fullquery name="get_average_rating">
+       <querytext>
+             select round(avg(r.rating),1) as rating
+               from recruiting_ratings r
+              where r.interview_id = recruiting_interviews.interview_id
+                and recruiting_interviews.interview_id = :interview_id
+       </querytext>
+   </fullquery>
+
 </queryset>