[LON-CAPA-cvs] cvs: loncom / lonsql

[raeburn]

raeburn		Wed Jul 25 18:40:00 2007 EDT

  Modified files:              
    /loncom	lonsql 
  Log:
  - Add support for query set to 'usersearch'
  - Searches an allusers MySQL table containing: username, domain, lastname, firstname, middlename, generation, id and permanentemail for all users in the domain.  
  - Can search by username, lastname or lastname,firstname
  - Seach can require exact match, or can look for cases where search term is part of a longer string.
  
  - Add support for query set to 'allusers'
  - Updates MySQL table with new information for a particular user.
  - Added subroutine: allusers_table_update() to access subroutines in LONCAPA::lonmetadata used to update the allusers table.
  
  
Index: loncom/lonsql
diff -u loncom/lonsql:1.81 loncom/lonsql:1.82
--- loncom/lonsql:1.81	Wed Apr 11 20:00:55 2007
+++ loncom/lonsql	Wed Jul 25 18:40:00 2007
@@ -3,7 +3,7 @@
 # The LearningOnline Network
 # lonsql - LON TCP-MySQL-Server Daemon for handling database requests.
 #
-# $Id: lonsql,v 1.81 2007/04/12 00:00:55 albertel Exp $
+# $Id: lonsql,v 1.82 2007/07/25 22:40:00 raeburn Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -441,6 +441,57 @@
                 if ($locresult) {
                     $result .= &escape(join(':',map{$_.'='.$replies{$_}} keys %replies));
                 }
+            } elsif ($query eq 'usersearch') {
+                my $srchdomain = &unescape($arg1);
+                my @items  = split(/%%/,$arg2);
+                my ($srchby,$srchtype) = map {&unescape($_)} @items; 
+                my $srchterm = &unescape($arg3);
+                my $quoted_dom = $dbh->quote( $srchdomain );
+                my ($query,$quoted_srchterm,@fields);
+                my ($table_columns,$table_indices) =
+                   &LONCAPA::lonmetadata::describe_metadata_storage('allusers');
+                foreach my $coldata (@{$table_columns}) {
+                    push(@fields,$coldata->{'name'});
+                }
+                my $fieldlist = join(',',@fields);
+                $query = "SELECT $fieldlist FROM allusers WHERE (domain = $quoted_dom AND ";
+                if ($srchby eq 'lastfirst') {
+                    my ($fraglast,$fragfirst) = split(/,/,$srchterm);
+                    if ($srchtype eq 'exact') {
+                        $query .= 'lastname = '.$dbh->quote($fraglast).
+                                  ' AND firstname = '.$dbh->quote($fragfirst);
+                    } else {
+                        $query .= 'lastname LIKE '.$dbh->quote('%'.$fraglast.'%').' AND firstname LIKE '.$dbh->quote('%'.$fragfirst.'%');
+                    }
+                } else {
+                    my %srchfield = (
+                                      uname    => 'username',
+                                      lastname => 'lastname',
+                                    );
+                    if ($srchtype eq 'exact') {
+                        $query .= $srchfield{$srchby}.' = '.$dbh->quote($srchterm);
+                    } else {
+                        $query .= $srchfield{$srchby}.' LIKE '.$dbh->quote('%'.$srchterm.'%');
+                    }
+                }
+                $query .= ") ORDER BY username ";
+                my $sth = $dbh->prepare($query);
+                if ($sth->execute()) {
+                    my @results;
+                    while (my @row = $sth->fetchrow_array) {
+                        my @items;
+                        for (my $i=0; $i<@row; $i++) {
+                            push(@items,&escape($fields[$i]).'='.&escape($row[$i]));
+                        }
+                        push(@results,join(":", @items));
+                    }
+                    $sth->finish;
+                    $result = &escape(join("&",@results));
+                } else {
+                    &logthis('<font color="blue">'.
+                             'WARNING: Could not retrieve from database:'.
+                             $sth->errstr().'</font>');
+               }
             } elsif ($query eq 'prepare activity log') {
                 my ($cid,$domain) = map {&unescape($_);} ($arg1,$arg2);
                 &logthis('preparing activity log tables for '.$cid);
@@ -459,6 +510,17 @@
                     ($query eq 'portfolio_access')) {
                 $result = &portfolio_table_update($query,$arg1,$arg2,
                                                   $arg3);
+            } elsif ($query eq 'allusers') {
+                my ($uname,$udom) = map {&unescape($_);} ($arg1,$arg2);
+                my %userdata;
+                my (@data) = split(/\%\%/,$arg3);
+                foreach my $item (@data) {
+                    my ($key,$value) = split(/=/,$item);
+                    $userdata{$key} = &unescape($value);
+                }
+                $userdata{'username'} = $uname;
+                $userdata{'domain'} = $udom;
+                $result = &allusers_table_update($query,$uname,$udom,\%userdata);
             } else {
                 # Do an sql query
                 $result = &do_sql_query($query,$arg1,$arg2,$searchdomain);
@@ -736,6 +798,34 @@
     return %access;
 }
 
+sub allusers_table_update {
+    my ($query,$uname,$udom,$userdata) = @_;
+    my %tablenames = (
+                       'allusers'   => 'allusers',
+                     );
+    my $result = 'ok';
+    my $tablechk = &check_table($query);
+    if ($tablechk == 0) {
+        my $request =
+   &LONCAPA::lonmetadata::create_metadata_storage($query,$query);
+        $dbh->do($request);
+        if ($dbh->err) {
+            &logthis("create $query".
+                     " ERROR: ".$dbh->errstr);
+                     $result = 'error';
+        }
+    }
+    if ($result eq 'ok') {
+        my %loghash = 
+            &LONCAPA::lonmetadata::process_allusers_data($dbh,undef,
+                \%tablenames,$uname,$udom,$userdata,'update');
+        foreach my $key (keys(%loghash)) {
+            &logthis($loghash{$key});
+        }
+    }
+    return $result;
+}
+
 ###########################################
 sub check_table {
     my ($table_id) = @_;