ad_user_class_query

one of the documented procedures in this installation of the ACS
Usage:
ad_user_class_query   selection
What it does:
Takes an ns_set of key/value pairs and produces a query for the class of users specified (one user per row returned).
Defined in: /web/philip/packages/acs-core/admin-procs.tcl

Source code:


    # we might need this 
    set where_clauses [list]
    set join_clauses [list]
    set group_clauses [list]
    set having_clauses [list]
    set tables [list users]
    # because we named our arg "selection", we can use this magic
    # utility procedure to set everything as a local var 
    set_variables_after_query

    # if we are using a user_class, just get the info

    if { [info exists count_only_p] && $count_only_p } {
	set select_list "count(users.user_id)"
    } else {
	# Get all the non-LOB columns.
	set user_columns [list]
	set db [ns_db gethandle subquery]
	foreach column [GetColumnNames $db "users"] {
	    if { $column != "portrait" && $column != "portrait_thumbnail" } {
		lappend user_columns "users.$column"
	    }
	}
	ns_db releasehandle $db
	set select_list [join $user_columns ",\n       "]
    }
    if { [info exists include_contact_p] && $include_contact_p} {
	append select_list ",\n       user_contact_summary(users.user_id) as contact_summary"
    }
    if { [info exists include_demographics_p] && $include_demographics_p} {
	append select_list ",\n       user_demographics_summary(users.user_id) as demographics_summary"
    }
    
    if { [info exists user_class_id] && ![empty_string_p $user_class_id] } {
	set db [ns_db gethandle subquery]
	set sql_post_select [database_to_tcl_string $db "select sql_post_select
	from user_classes where user_class_id = $user_class_id"]
	ns_db releasehandle $db
	return "select $select_list\n$sql_post_select"
    }
    
    if { [info exists sql_post_select] && ![empty_string_p $sql_post_select] } {
	return "select $select_list\n$sql_post_select"
    }

    foreach criteria [ad_user_class_parameters] {
	if { [info exists $criteria] && ![empty_string_p [set $criteria]] } {
	    switch $criteria {
		"category_id" {
		    if {[lsearch $tables "users_interests"] == -1 } {
		    lappend tables "users_interests"
			lappend join_clauses "users.user_id = users_interests.user_id"
		    }
		    lappend where_clauses "users_interests.category_id = $category_id"
		}
		"country_code" {
		    if {[lsearch $tables "users_contact"] == -1 } {
			lappend tables "users_contact"
			lappend join_clauses "users.user_id = users_contact.user_id"
		    }
		    lappend where_clauses "users_contact.ha_country_code = '$country_code'"
		}
		"usps_abbrev" {
		    if {[lsearch $tables "users_contact"] == -1 } {
			lappend tables "users_contact"
			lappend join_clauses "users.user_id = users_contact.user_id"
		    }
		    lappend where_clauses "(users_contact.ha_state = '$usps_abbrev' and (users_contact.ha_country_code is null or users_contact.ha_country_code = 'us'))"
		}
		"intranet_user_p" {
		    if {$intranet_user_p == "t" && [lsearch $tables "intranet_users"] == -1 } {
			lappend tables "intranet_users"
			lappend join_clauses "users.user_id = intranet_users.user_id"
		    }
		}
		"group_id" {
 		    #if {[lsearch $tables "users_group_map"] == -1 } {
 			#lappend tables "user_group_map"
 			#lappend join_clauses "users.user_id = user_group_map.user_id"
 		    #}
 		    #lappend where_clauses "user_group_map.group_id = $group_id"
		    lappend where_clauses "ad_group_member_p(users.user_id, $group_id) = 't'"
		}
		
		"last_name_starts_with" {
		    lappend where_clauses "upper(users.last_name) like upper('[DoubleApos $last_name_starts_with]%')"
		}
		"email_starts_with" {
		    lappend where_clauses "upper(users.email) like upper('[DoubleApos $email_starts_with]%')"
		}
		"expensive" {
		    if { [info exists count_only_p] && $count_only_p } {
			lappend where_clauses "[ad_parameter ExpensiveThreshold "member-value"] < (select sum(amount) from users_charges where users_charges.user_id = users.user_id)"
		    } else {
			if {[lsearch $tables "user_charges"] == -1 } {
			    lappend tables "users_charges"
			    lappend join_clauses "users.user_id = users_charges.user_id"
			}
			# we are going to be selecting users.* in general, so
			# we must group by all the columns in users (can't 
			# GROUP BY USERS.* in Oracle, sadly)
			set db [ns_db gethandle subquery]
			foreach column [GetColumnNames $db "users"] {
			    # can't group by a BLOB column.
			    if { $column != "portrait" && $column != "portrait_thumbnail" } {
				lappend group_clauses "users.$column"
			    }
			}
			ns_db releasehandle $db
			lappend having_clauses "sum(users_charges.amount) > [ad_parameter ExpensiveThreshold "member-value"]"
			# only the ones where they haven't paid
			lappend where_clauses "users_charges.order_id is null"
		    }
		}
		"user_state" {
		    lappend where_clauses "users.user_state = '$user_state'"
		}
		"sex" {
		    if {[lsearch $tables "users_demographics"] == -1 } {
			lappend tables "users_demographics"
			lappend join_clauses "users.user_id = users_demographics.user_id"
		    }
		    lappend where_clauses "users_demographics.sex = '$sex'"
		}
		"age_below_years" {
		    if {[lsearch $tables "users_demographics"] == -1 } {
			lappend tables "users_demographics"
			lappend join_clauses "users.user_id = users_demographics.user_id"
		    }
		    lappend where_clauses "users_demographics.birthdate > sysdate - ($age_below_years * 365.25)"
		}
		"age_above_years" {
		    if {[lsearch $tables "users_demographics"] == -1 } {
			lappend tables "users_demographics"
			lappend join_clauses "users.user_id = users_demographics.user_id"
		    }
		    lappend where_clauses "users_demographics.birthdate < sysdate - ($age_above_years * 365.25)"
		}
		"registration_during_month" {
		    lappend where_clauses "to_char(users.registration_date,'YYYYMM') = '$registration_during_month'"
		}
		"registration_before_days" {
		    lappend where_clauses "users.registration_date < sysdate - $registration_before_days"
		}
		"registration_after_days" {
		    lappend where_clauses "users.registration_date > sysdate - $registration_after_days"
		}
		"registration_after_date" {
		    lappend where_clauses "users.registration_date > '$registration_after_date'"
		}
		"last_login_before_days" {
		    lappend where_clauses "users.last_visit < sysdate - $last_login_before_days"
		}
		"last_login_after_days" {
		    lappend where_clauses "users.last_visit > sysdate - $last_login_after_days"
		}
		"last_login_equals_days" {
		    lappend where_clauses "round(sysdate-last_visit) = $last_login_equals_days"
		}
		"number_visits_below" {
		    lappend where_clauses "users.n_sessions < $number_visits_below"
		}
		"number_visits_above" {
		    lappend where_clauses "users.n_sessions > $number_visits_above"
		}
		"crm_state" {
		    lappend where_clauses "users.crm_state = '$crm_state'"
		}
		"curriculum_elements_completed" {
		    lappend where_clauses "$curriculum_elements_completed = (select count(*) from user_curriculum_map ucm where ucm.user_id = users.user_id and ucm.curriculum_element_id in (select curriculum_element_id from curriculum))"
		}
	    }
	}
    }
    #stuff related to the query itself
    
    if { [info exists combine_method] && $combine_method == "or" } {
	set complete_where [join $where_clauses " or "]
    } else {
	set complete_where [join $where_clauses " and "]
    }
    

    if { [info exists include_accumulated_charges_p] && $include_accumulated_charges_p && (![info exists count_only_p] || !$count_only_p) } {
	# we're looking for expensive users and not just counting them
	append select_list ", sum(users_charges.amount) as accumulated_charges"
    }
    if { [llength $join_clauses] == 0 } {
	set final_query "select $select_list
	from [join $tables ", "]"
	if ![empty_string_p $complete_where] {
	    append final_query "\nwhere $complete_where"
	}
    } else {
	# we're joining at 
	set final_query "select $select_list
	from [join $tables ", "]
	where [join $join_clauses "\nand "]"
	if ![empty_string_p $complete_where] {
	    append final_query "\n and ($complete_where)"
	}
    }
    if { [llength $group_clauses] > 0 } {
	append final_query "\ngroup by [join $group_clauses ", "]"
    }
    if { [llength $having_clauses] > 0 } {
	append final_query "\nhaving [join $having_clauses " and "]"
    }
    return $final_query


philg@mit.edu