Changeset 325


Ignore:
Timestamp:
05/02/12 15:03:07 (12 years ago)
Author:
Kris Deugau
Message:

/trunk

Move SQL from user list code into DNSDB.pm. See #1

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r324 r325  
    4444        &getGroupCount &getGroupList
    4545        &addUser &updateUser &delUser &userFullName &userStatus &getUserData
     46        &getUserCount &getUserList
    4647        &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    4748        &addRec &updateRec &delRec
    … …  
    6869                &getGroupCount &getGroupList
    6970                &addUser &updateUser &delUser &userFullName &userStatus &getUserData
     71                &getUserCount &getUserList
    7072                &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    7173                &addRec &updateRec &delRec
    … …  
    23562358  return ('OK',"User $username ($fname $lname) added");
    23572359} # end addUser
     2360
     2361
     2362## DNSDB::getUserCount()
     2363# Get count of users in group
     2364# Takes a database handle and hash containing at least the current group, and optionally:
     2365# - a reference list of secondary groups
     2366# - a filter string
     2367# - a "Starts with" string
     2368sub getUserCount {
     2369  my $dbh = shift;
     2370
     2371  my %args = @_;
     2372
     2373  my @filterargs;
     2374
     2375  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2376  push @filterargs, "^$args{startwith}" if $args{startwith};
     2377  push @filterargs, $args{filter} if $args{filter};
     2378
     2379
     2380  my $sql = "SELECT count(*) FROM users ".
     2381        "WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2382        ($args{startwith} ? " AND username ~* ?" : '').
     2383        ($args{filter} ? " AND username ~* ?" : '');
     2384  my ($count) = $dbh->selectrow_array($sql, undef, (@filterargs) );
     2385  $errstr = $dbh->errstr if !$count;
     2386  return $count;
     2387} # end getUserCount()
     2388
     2389
     2390## DNSDB::getUserList()
     2391# Get list of users
     2392# Takes the same arguments as getUserCount() above, plus optional:
     2393# - sort field
     2394# - sort order
     2395# - offset/return-all-everything flag (defaults to $perpage records)
     2396sub getUserList {
     2397  my $dbh = shift;
     2398
     2399  my %args = @_;
     2400
     2401  my @filterargs;
     2402
     2403  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2404  push @filterargs, "^$args{startwith}" if $args{startwith};
     2405  push @filterargs, $args{filter} if $args{filter};
     2406
     2407  # better to request sorts on "simple" names, but it means we need to map it to real columns
     2408  my %sortmap = (user => 'u.username', type => 'u.type', group => 'g.group_name', status => 'u.status',
     2409        fname => 'fname');
     2410  $args{sortby} = $sortmap{$args{sortby}};
     2411
     2412  # protection against bad or missing arguments
     2413  $args{sortorder} = 'ASC' if !$args{sortorder};
     2414  $args{sortby} = 'u.username' if !$args{sortby};
     2415  $args{offset} = 0 if !$args{offset};
     2416
     2417  my $sql = "SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
     2418        "FROM users u ".
     2419        "INNER JOIN groups g ON u.group_id=g.group_id ".
     2420        "WHERE u.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2421        ($args{startwith} ? " AND u.username ~* ?" : '').
     2422        ($args{filter} ? " AND u.username ~* ?" : '').
     2423        " ORDER BY $args{sortby} $args{sortorder} ".
     2424        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
     2425  my $ulist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
     2426  $errstr = $dbh->errstr if !$ulist;
     2427  return $ulist;
     2428} # end getUserList()
    23582429
    23592430

  • trunk/dns.cgi

    r323 r325  
    20482048  my $childlist = join(',',@childgroups);
    20492049
    2050   my $sql = "SELECT count(*) FROM users WHERE group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    2051         ($startwith ? " AND username ~* ?" : '').
    2052         ($filter ? " AND username ~* ?" : '');
    2053   my $sth = $dbh->prepare($sql);
    2054   $sth->execute(@filterargs);
    2055   my ($count) = ($sth->fetchrow_array);
     2050  my $count = getUserCount($dbh, (childlist => $childlist, curgroup => $curgroup,
     2051        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef) ) );
    20562052
    20572053# fill page count and first-previous-next-last-all bits
    … …  
    20782074  $page->param(searchsubs => $searchsubs) if $searchsubs;
    20792075
    2080 # munge sortby for columns in database
    2081   $sortby = 'u.username' if $sortby eq 'user';
    2082   $sortby = 'u.type' if $sortby eq 'type';
    2083   $sortby = 'g.group_name' if $sortby eq 'group';
    2084   $sortby = 'u.status' if $sortby eq 'status';
    2085 
    2086   my @userlist;
    2087   $sql = "SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
    2088         "FROM users u ".
    2089         "INNER JOIN groups g ON u.group_id=g.group_id ".
    2090         "WHERE u.group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    2091         ($startwith ? " AND u.username ~* ?" : '').
    2092         ($filter ? " AND u.username ~* ?" : '').
    2093         " ORDER BY $sortby $sortorder ".
    2094         ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
    2095 
    2096   $sth = $dbh->prepare($sql);
    2097   $sth->execute(@filterargs);
    2098 
    2099   my $rownum = 0;
    2100   while (my @data = $sth->fetchrow_array) {
    2101     no warnings "uninitialized";        # Just In Case something stupid happens and a user gets no first or last name
    2102     my %row;
    2103     $row{userid} = $data[0];
    2104     $row{username} = $data[1];
    2105     $row{userfull} = $data[2];
    2106     $row{usertype} = ($data[3] eq 'S' ? 'superuser' : "user");
    2107     $row{usergroup} = $data[4];
    2108     $row{active} = $data[5];
    2109     $row{bg} = ($rownum++)%2;
    2110     $row{sid} = $sid;
    2111     $row{eduser} = ($permissions{admin} ||
    2112         ($permissions{user_edit} && $data[3] ne 'S') ||
    2113         ($permissions{self_edit} && $data[0] == $session->param('uid')) );
    2114     $row{deluser} = ($permissions{admin} || ($permissions{user_delete} && $data[3] ne 'S'));
    2115     push @userlist, \%row;
    2116   }
    2117   $page->param(usertable => \@userlist);
     2076  my $ulist = getUserList($dbh, (childlist => $childlist, curgroup => $curgroup,
     2077        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef),
     2078        offset => $webvar{offset}, sortby => $sortby, sortorder => $sortorder) );
     2079  # Some UI things need to be done to the list (unlike other lists)
     2080  foreach my $u (@{$ulist}) {
     2081    $u->{eduser} = ($permissions{admin} ||
     2082        ($permissions{user_edit} && $u->{type} ne 'S') ||
     2083        ($permissions{self_edit} && $u->{user_id} == $session->param('uid')) );
     2084    $u->{deluser} = ($permissions{admin} || ($permissions{user_delete} && $u->{type} ne 'S'));
     2085    $u->{type} = ($u->{type} eq 'S' ? 'superuser' : 'user');
     2086  }
     2087  $page->param(usertable => $ulist);
    21182088} # end list_users()
    21192089

  • trunk/templates/useradmin.tmpl

    r207 r325  
    3636<TMPL_IF name=usertable>
    3737<TMPL_LOOP name=usertable>
    38 <tr class="row<TMPL_VAR name=bg>">
    39         <td align="left"><TMPL_IF eduser><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=user&amp;useraction=edit&amp;user=<TMPL_VAR NAME=userid>"><TMPL_VAR NAME=username></a><TMPL_ELSE><TMPL_VAR NAME=username></TMPL_IF></td>
    40         <td class="data_nowrap"><TMPL_VAR name=userfull></td>
    41         <td><TMPL_VAR name=usertype></td>
    42         <td><TMPL_VAR name=usergroup></td>
     38<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
     39        <td align="left"><TMPL_IF eduser><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=user&amp;useraction=edit&amp;user=<TMPL_VAR NAME=user_id>"><TMPL_VAR NAME=username></a><TMPL_ELSE><TMPL_VAR NAME=username></TMPL_IF></td>
     40        <td class="data_nowrap"><TMPL_VAR name=fname></td>
     41        <td><TMPL_VAR name=type></td>
     42        <td><TMPL_VAR name=group_name></td>
    4343        <td align="center">
    4444<TMPL_IF eduser>
    45                 <a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=useradmin<TMPL_IF NAME=offset>&amp;offset=<TMPL_VAR NAME=offset></TMPL_IF>&amp;id=<TMPL_VAR NAME=userid>&amp;userstatus=<TMPL_IF active>useroff<TMPL_ELSE>useron</TMPL_IF>"><TMPL_IF active>enabled<TMPL_ELSE>disabled</TMPL_IF></a>
     45                <a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=useradmin<TMPL_IF NAME=offset>&amp;offset=<TMPL_VAR NAME=offset></TMPL_IF>&amp;id=<TMPL_VAR NAME=user_id>&amp;userstatus=<TMPL_IF status>useroff<TMPL_ELSE>useron</TMPL_IF>"><TMPL_IF status>enabled<TMPL_ELSE>disabled</TMPL_IF></a>
    4646<TMPL_ELSE>
    47                 <TMPL_IF active>enabled<TMPL_ELSE>disabled</TMPL_IF>
     47                <TMPL_IF status>enabled<TMPL_ELSE>disabled</TMPL_IF>
    4848</TMPL_IF>
    4949</td>
    5050<TMPL_IF deluser>
    51         <td align="center"><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=deluser&amp;id=<TMPL_VAR NAME=userid>"><img src="images/trash2.png" alt="[ Delete ]" /></a></td>
     51        <td align="center"><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=deluser&amp;id=<TMPL_VAR NAME=user_id>"><img src="images/trash2.png" alt="[ Delete ]" /></a></td>
    5252</TMPL_IF>
    5353</tr>
Note: See TracChangeset for help on using the changeset viewer.