Changeset 52 for trunk/dns.cgi

Timestamp:

12/22/09 16:47:28 (14 years ago)

Author:

Kris Deugau

Message:

/trunk

Make subgroup y/n checkbox, search filter more or less work on user, domain, and group lists

• still need to figure out how to retain the letter-filter, subgroup, and filter data when they're needed but discard them when they should be discarded

File:

• trunk/dns.cgi (modified) (9 diffs)

trunk/dns.cgi

@@ -726 +726 @@
 # le sigh.  and we need to strip any previous action
   $tmp_ruri =~ s/\&action=[^&]+//g;
+
+##fixme:  need to sort out how to propagate these without dragging them along too far, and <n>plicating them
 # and any bits from the "starts with" letter block - (ab)using this widget
 # to fill the bulk of the URI so various templates don't grow to insanity
   $tmp_ruri =~ s/\&amp;startwith=[a-z09-]+//g;
+# search box webvars
+  $tmp_ruri =~ s/\&amp;searchsubs=[a-z09-]+//g;
+  $tmp_ruri =~ s/\&amp;filter=[a-z09-]+//g;
+
 #  $page->param(whereami => $ENV{REQUEST_URI});
   $page->param(whereami => $tmp_ruri);
@@ -968 +974 @@
 sub listdomains {
 
-##fixme:  account for searches and starts-with filters
-  my $sth = $dbh->prepare("SELECT count(*) FROM domains WHERE group_id=?");
-  $sth->execute($curgroup);
+##fixme:  $logingroup or$curgroup?
+  my @childgroups;
+  getChildren($dbh, $logingroup, \@childgroups, 'all');
+  my $childlist = join(',',@childgroups);
+
+  my $sql = "SELECT count(*) FROM domains WHERE group_id IN ($logingroup".($childlist ? ",$childlist" : '').")".
+        (defined($webvar{startwith}) ? " AND domain ~* '^[$webvar{startwith}]'" : '').
+        (defined($webvar{filter}) ? " AND domain ~* '$webvar{filter}'" : '');
+  my $sth = $dbh->prepare($sql);
+  $sth->execute;
   my ($count) = $sth->fetchrow_array;
 
@@ -1006 +1019 @@
   $page->param(group => $curgroup);
   my @domlist;
-  my $sql = "SELECT domain_id,domain,status,groups.group_name AS group FROM domains".
+  $sql = "SELECT domain_id,domain,status,groups.group_name AS group FROM domains".
         " INNER JOIN groups ON domains.group_id=groups.group_id".
-        " WHERE domains.group_id=?".
+        " WHERE domains.group_id IN ($logingroup".($childlist ? ",$childlist" : '').")".
 ##fixme:  don't do variable subs in SQL, use placeholders and params in ->execute()
         (defined($webvar{startwith}) ? " AND domain ~* '^[$webvar{startwith}]'" : '').
@@ -1015 +1028 @@
         " $sortorder ".($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
   $sth = $dbh->prepare($sql);
-  $sth->execute($curgroup);
+  $sth->execute;
   my $rownum = 0;
   while (my @data = $sth->fetchrow_array) {
@@ -1040 +1053 @@
   my $childlist = join(',',@childgroups);
 
-  my $sql = "SELECT count(*) FROM groups WHERE group_id IN ($logingroup".($childlist ? ",$childlist" : '').")";
+  my $sql = "SELECT count(*) FROM groups WHERE group_id IN ($logingroup".($childlist ? ",$childlist" : '').")".
+        (defined($webvar{startwith}) ? " AND group_name ~* '^[$webvar{startwith}]'" : '').
+        (defined($webvar{filter}) ? " AND group_name ~* '$webvar{filter}'" : '');
   my $sth = $dbh->prepare($sql);
 
@@ -1088 +1103 @@
   $sth->execute;
 
-push @debugbits, $sth->errstr;
   my $rownum = 0;
   while (my @data = $sth->fetchrow_array) {
@@ -1135 +1149 @@
 
 sub list_users {
-  my $sth = $dbh->prepare("select count(*) from users where group_id=?");
-  $sth->execute($curgroup);
+
+  my @childgroups;
+  getChildren($dbh, $curgroup, \@childgroups, 'all') if $webvar{searchsubs};
+  my $childlist = join(',',@childgroups);
+
+  my $sql = "SELECT count(*) FROM users WHERE group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
+        (defined($webvar{startwith}) ? " AND username ~* '^[$webvar{startwith}]'" : '').
+        (defined($webvar{filter}) ? " AND username ~* '$webvar{filter}'" : '');
+  my $sth = $dbh->prepare($sql);
+  $sth->execute;
   my ($count) = ($sth->fetchrow_array);
 
@@ -1161 +1183 @@
 
   $page->param(filter => $webvar{filter}) if $webvar{filter};
+  $page->param(searchsubs => $webvar{searchsubs}) if $webvar{searchsubs};
 
 # munge sortby for columns in database
@@ -1169 +1192 @@
 
   my @userlist;
-  $sth = $dbh->prepare("SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
+  $sql = "SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
         "FROM users u ".
         "INNER JOIN groups g ON u.group_id=g.group_id ".
-        "WHERE u.group_id=?".
+        "WHERE u.group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
 ##fixme:  don't do variable subs in SQL, use placeholders and params in ->execute()
         (defined($webvar{startwith}) ? " AND u.username ~* '^[$webvar{startwith}]'" : '').
         (defined($webvar{filter}) ? " AND u.username ~* '$webvar{filter}'" : '').
         " ORDER BY $sortby $sortorder ".
-        ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage));
+        ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
 
 #       " GROUP BY g.group_id, g.group_name, g2.group_name ".
 #       ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage));
 
-  $sth->execute($curgroup);
+  $sth = $dbh->prepare($sql);
+  $sth->execute;
 
   my $rownum = 0;