Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.tcl	20 Apr 2001 20:51:25 -0000	1.1
+++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.tcl	12 Jun 2001 06:25:49 -0000	1.2
@@ -31,7 +31,7 @@
 # dimensional slider definition
 set dimensional {
     {submitby "Submitted by" any {
-        {me "me" {where "tt.creation_user = $user_id"}}
+        {me "me" {where "[db_map creation_user]"}}
         {any "anyone" {}}
     }}
     {assign "Assigned to" me {
@@ -40,17 +40,17 @@
         {noass "unassigned" {}}
     }}      
     {state "Status" any {
-        {created "created" {where "wc.state = 'created'"}}
-        {active "active" {where "wc.state = 'active'"}}
-        {suspended "suspended" {where "wc.state = 'suspended'"}}
-        {canceled "canceled" {where "wc.state = 'canceled'"}}
-        {finished "finished" {where "wc.state = 'finished'"}}
+        {created "created" {where "[db_map status_created]"}}
+        {active "active" {where "[db_map status_active]"}}
+        {suspended "suspended" {where "[db_map status_suspended]"}}
+        {canceled "canceled" {where "[db_map status_canceled]"}}
+        {finished "finished" {where "[db_map status_finished]"}}
         {any "all" {}}
     }}
     {created "Creation Time" any {
-        {1d "last 24hrs" {where "tt.creation_date + 1 > sysdate"}}
-        {1w "last week" {where "tt.creation_date + 7 > sysdate"}}
-        {1m "last month" {where "tt.creation_date + 30 > sysdate"}}
+        {1d "last 24hrs" {where "[db_map last_1_day]"}}
+        {1w "last week" {where "[db_map last_7_days]"}}
+        {1m "last month" {where "[db_map last_30_days]"}}
         {any "all" {}}
     }}
 }
@@ -73,7 +73,7 @@
 # dynamically generating the dimensional list for severity options
 set severity_slider [list severity "Severity" any]
 set severity_list [list]
-db_foreach get_priorities {
+db_foreach get_severities {
     select name,
            value
       from ttracker_options
@@ -101,15 +101,18 @@
 lappend table_def [list ticket_id "ID#" {} \
         {<td align=center><a href="ticket-view?ticket_id=$ticket_id">$ticket_id</a></td>} ]
 lappend table_def [list category "$category_name" {} c ]
-lappend table_def [list pretty_priority "Priority" {priority $order} c ]
-lappend table_def [list pretty_severity "Severity" {severity $order} c ]
+lappend table_def [list pretty_priority "Priority" {pretty_priority $order} c ]
+lappend table_def [list pretty_severity "Severity" {pretty_severity $order} c ]
 lappend table_def [list state "Status" {} c ]
 lappend table_def [list subject "Subject" {} c ]
-lappend table_def [list creator_id "Submitted by" {upper(creator_name) $order} \
+# vinodk: kludge - since you can't order by alias names in postgres
+#         have to order by item #
+lappend table_def [list creator_id "Submitted by" {upper(9) $order} \
         {<td align=center><a href="/shared/community-member?user_id=$creator_id">$creator_name</a></td>} ]
-lappend table_def [list assignee_id "Assigned to" {upper(assignee_name) $order} \
+# vinodk: kludge again
+lappend table_def [list assignee_id "Assigned to" {upper(12) $order} \
         {<td align=center><a href="/shared/community-member?user_id=$assignee_id">$assignee_name</a></td>} ]
-lappend table_def [list created "Created" {creation_date $order} c ]
+lappend table_def [list created "Created" {created $order} c ]
 
 
 # sql to retrieve selected tickets
@@ -120,150 +123,43 @@
 if [string equal $view "full"] {
     set view_select ", cr.mime_type, cr.content"
     set view_from ", cr_revisions cr "
-    set view_where "and cr.revision_id = content_item.get_live_revision(tt.ticket_id) "
+    set view_where "[db_map live_revision]"
 }
 
 
 # this is the ugly part
 # 3 different queries for 3 different assignee types
+
+# openacs change - vinodk
+# old code conditionally assigned huge query to var 'sql'
+# and then ran the query sql (after the if statement)
+# 
+# I've moved those queries to .xql files with different statement names
+# I then conditionally assignthe var 'statement_name' to the 
+# appropriate statement name from the .xql file
+
+set sql ""
 set dimensional [lreplace $dimensional 1 1]
 if {[string equal $assign "me"]} {
+	set statement_name ticket_select_me
+
     # this query is specifically for tickets assigned to the user 
     # the "exists" clause asks if there's any 'resolved' tasks assigned to the user
-    set sql "select tt.ticket_id,
-                    ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity,
-                    ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority,
-                    tt.subject,
-                    to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created,
-                    tc.category_id,
-                    tc.name as category,
-                    tt.creation_user as creator_id,
-                    acs_object.name(tt.creation_user) as creator_name,
-                    wc.state,
-		    :user_id as assignee_id,
-		    'me' as assignee_name
-                    $view_select
-               from ttracker_categories tc,
-                    ttracker_tickets tt,
-	            wf_cases wc
-                    $view_from
-              where tc.package_id = :package_id and
-                    tt.category_id = tc.category_id and
-                    wc.object_id = tt.ticket_id and 
-                    exists (select wt.task_id 
-                              from wf_task_assignments wta,
-                                   wf_tasks wt,
-                                   party_approved_member_map m
-                             where wt.transition_key = 'resolve'
-                               and wt.task_id = wta.task_id
-                               and wta.party_id = m.party_id
-                               and m.member_id = :user_id			 
-                               and wt.case_id = wc.case_id
-                               and wt.task_id = (select max(wt1.task_id) 
-                                                   from wf_tasks wt1
-                                                  where wt1.case_id = wc.case_id
-                                                    and wt1.transition_key = 'resolve'))
-                    $view_where
-              [ad_dimensional_sql $dimensional] 
-              [ad_order_by_from_sort_spec $orderby $table_def]"
 } elseif {[string equal $assign "noass"]} {
+	set statement_name ticket_select_noass
+
     # the "not exists" clause specifies that there must be no assignees
     # assigned to the "resolve" task for this case
     # the reason to use "max" is that we want to find the most recent task
     # there might be many tasks associated with a case
-    set sql "select tt.ticket_id,
-                    ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity,
-                    ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority,
-                    tt.subject,
-                    to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created,
-                    tc.category_id,
-                    tc.name as category,
-                    tt.creation_user as creator_id,
-                    acs_object.name(tt.creation_user) as creator_name,
-                    wc.state,
-                    0 as assignee_id,
-                    '' as assignee_name
-                    $view_select
-               from ttracker_categories tc,
-                    ttracker_tickets tt,
-        	    wf_cases wc
-                    $view_from
-              where tc.package_id = :package_id and
-                    tt.category_id = tc.category_id and
-                    wc.object_id = tt.ticket_id and 
-	            not exists (select wta1.task_id 
-	                          from wf_task_assignments wta1 
-                                 where wta1.task_id = (select max(wt.task_id) 
-                                                         from wf_tasks wt
-                                                        where wt.case_id = wc.case_id
-                                                          and wt.transition_key = 'resolve'))
-              $view_where
-              [ad_dimensional_sql $dimensional] 
-              [ad_order_by_from_sort_spec $orderby $table_def]"
 } else {
+	set statement_name ticket_select_all
+
     # the first query is for tickets with assignees
     # the second one is for tickets with no assignees
     # the reason to use "union all" is for performance issue
-    # tried to compress them into one query using outer join with a view, but it's too slow
-    set sql "
-     select results.* from 
-            (select tt.ticket_id,
-                    ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity,
-                    ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority,
-                    tt.subject,
-                    to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created,
-                    tc.category_id,
-                    tc.name as category,
-                    tt.creation_user as creator_id,
-                    acs_object.name(tt.creation_user) as creator_name,
-                    wc.state,
-            	    wta.party_id as assignee_id, 
-	            acs_object.name(wta.party_id) as assignee_name
-                    $view_select
-               from ttracker_categories tc,
-                    ttracker_tickets tt,
-	            wf_cases wc,
-	            wf_task_assignments wta
-                    $view_from
-              where tc.package_id = :package_id and
-                    tt.category_id = tc.category_id and
-                    wc.object_id = tt.ticket_id and
-	            wta.task_id = (select max(wt.task_id) 
-                                     from wf_tasks wt
-                                    where wt.case_id = wc.case_id
-                                      and wt.transition_key = 'resolve')
-                    $view_where
-                    [ad_dimensional_sql $dimensional]                    
-          union all
-             select tt.ticket_id,
-                    ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity,
-                    ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority,
-                    tt.subject,
-                    to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created,
-                    tc.category_id,
-                    tc.name as category,
-                    tt.creation_user as creator_id,
-                    acs_object.name(tt.creation_user) as creator_name,
-                    wc.state,
-	            0 as assignee_id,
-	            '' as assignee_name
-                    $view_select
-               from ttracker_categories tc,
-                    ttracker_tickets tt,
-	            wf_cases wc
-                    $view_from
-              where tc.package_id = :package_id and
-                    tt.category_id = tc.category_id and
-                    wc.object_id = tt.ticket_id and
-                    not exists (select wta1.task_id 
-                                  from wf_task_assignments wta1 
-                                 where wta1.task_id = (select max(wt.task_id) 
-                                                         from wf_tasks wt
-                                                        where wt.case_id = wc.case_id
-                                                          and wt.transition_key = 'resolve'))
-                    $view_where
-                    [ad_dimensional_sql $dimensional]) results
-          [ad_order_by_from_sort_spec $orderby $table_def]"                        
+    # tried to compress them into one query using outer join 
+	# with a view, but it's too slow
 }
 
 # ad_table manipulating code
@@ -276,7 +172,7 @@
             [if {![ad_table_same creator_id]} {
                 ad_table_span [subst {Submitted by: <a href="/shared/community-member?user_id=$creator_id">$creator_name</a>}] {bgcolor=cccccc}
             }]
-        }
+			}
     }
     assignee_id {
         lappend remove_column assignee_id
@@ -300,7 +196,8 @@
 }
 
 # set which columns we really want to be displayed
-set all_columns {line_number ticket_id category pretty_priority pretty_severity state subject creator_id assignee_id created}
+set all_columns {line_number ticket_id category pretty_priority pretty_severity \
+		state subject creator_id assignee_id created}
 set col [list]
 foreach acol $all_columns { 
     if {[lsearch -exact $remove_column $acol] < 0} { 
@@ -320,7 +217,7 @@
                                    -Tmissing_text {<i>No tickets available.</i>} \
                                    -Ttable_extra_html "width=100%" \
                                    -bind $bind_ns_set \
-                                   ticket_select $sql $table_def]
+                                   $statement_name $sql $table_def]
     } else {
         set ticket_table [ad_table -Torderby $orderby \
                                    -Taudit {ticket_id} \
@@ -329,7 +226,7 @@
                                    -Tmissing_text {<i>No tickets available.</i>} \
                                    -Ttable_extra_html "width=100%" \
                                    -bind $bind_ns_set \
-                                   ticket_select $sql $table_def]
+                                   $statement_name $sql $table_def]
     }
 }
 
@@ -339,11 +236,11 @@
     set assignees ""
     set i 1
     # create a custom datasource for easier processing in the template page
-    template::multirow create tickets ticket_id pretty_severity pretty_priority subject created \
-	    category_id  category creator_id creator_name state assignee_id assignee_name content \
-	    assignee_list
+    template::multirow create tickets ticket_id pretty_severity pretty_priority \
+			subject created category_id  category creator_id creator_name state \
+			assignee_id assignee_name content assignee_list comments
 
-    db_foreach ticket $sql {
+    db_foreach $statement_name $sql {
         # add a row in the datasource for new tickets
         # if we sorted by assignee_id, then always create a new row 
         if { $old_ticket_id != $ticket_id || [ad_sort_primary_key $orderby] == "assignee_id"} {
@@ -355,26 +252,33 @@
                 incr i
             }	    
 
-	    template::multirow append tickets $ticket_id $pretty_severity $pretty_priority $subject \
-		    $created $category_id $category $creator_id $creator_name $state $assignee_id \
-	            $assignee_name
+			template::multirow append tickets $ticket_id $pretty_severity \
+					$pretty_priority $subject $created $category_id $category \
+					$creator_id $creator_name $state $assignee_id $assignee_name
 
-	    # quote the content if it's plain text
-	    if [string equal $view "full"] {
-		if [string equal $mime_type "text/plain"] {
-		    template::multirow set tickets $i content [ad_quotehtml $content] 
-		} else {
-		    template::multirow set tickets $i content $content
-		}
-	    }
+			# quote the content if it's plain text
+			if [string equal $view "full"] {
+				if [string equal $mime_type "text/plain"] {
+					template::multirow set tickets $i content \
+							[ad_quotehtml $content] 
+				} else {
+					template::multirow set tickets $i content $content
+				}
+
+				# full view also shows comments
+				# FIXME requires general-comments
+				#template::multirow set tickets $i comments \
+				#		[general_comments_get_comments -print_content_p 1 $ticket_id ""]
+				template::multirow set tickets $i comments "FIXME - general-comments"
+			}
 		    
             if { ![empty_string_p $assignee_name] } {
                 set assignees "<strong><a href=\"/shared/community-member?user_id=$assignee_id\">$assignee_name</a></strong>"
             } else {
                 set assignees ""
             }
          
-        # accumulate the assignee_name otherwise     
+			# accumulate the assignee_name otherwise     
         } else {
             append assignees ", <strong><a href=\"/shared/community-member?user_id=$assignee_id\">$assignee_name</a></strong>"
         }