/[libdata-portal]/branches/mysql/trunk/Portal.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /branches/mysql/trunk/Portal.pm

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 11 by dpavlin, Sun Mar 7 21:32:53 2004 UTC revision 12 by dpavlin, Sun Mar 7 22:07:47 2004 UTC
# Line 179  sub get_mastersubjects { Line 179  sub get_mastersubjects {
179          my $q = $self->query();          my $q = $self->query();
180    
181          my $sql = qq{          my $sql = qq{
182          select mastersubject_id,upper(mastersubject) as mastersubject,(mastersubject_id = ?) as selected          select mastersubject_id,upper(mastersubject) as mastersubject
183                  from mastersubject                  from mastersubject
184                  where mastersubject_id > 2                  where mastersubject_id > 2
185                  order by mastersubject                  order by mastersubject
186          };          };
187    
188          my $sth = $dbh->prepare($sql);          my $sth = $dbh->prepare($sql);
189          $sth->execute($q->param('ms') || undef);          $sth->execute();
190            my $arr = $sth->fetchall_arrayref({});
191    
192          return $sth->fetchall_arrayref({});          return if (! $arr);
193            return $arr if (! $q->param('ms'));
194    
195            # add selected column
196            foreach my $i ( 0 .. (scalar @$arr)-1 ) {
197                    $arr->[$i]->{'selected'} = ($arr->[$i]->{'mastersubject_id'} == $q->param('ms'));
198            }
199            return $arr;
200  }  }
201    
202  # get one MasterSubject by it's ID  # get one MasterSubject by it's ID
# Line 209  sub get_mastersubject_by_id { Line 217  sub get_mastersubject_by_id {
217          return $sth->fetchrow_hashref();          return $sth->fetchrow_hashref();
218  }  }
219    
220    # WARNING: Cludge ahead!
221    #
222    # stupid MySQL doesn't support subselects.
223    #
224    sub select_subject_id_where_mastersubject_id {
225            my $id = shift || croak("sub select emulation needs mastersubject_id");
226            my $sql = qq{ select subject_id from sub_mastersubject where mastersubject_id = ? };
227    
228            my $sth = $dbh->prepare($sql);
229            $sth->execute($id);
230    
231            my $arr = $sth->fetchall_hashref('subject_id');
232    
233            $sql = join(",",keys %$arr);
234            $sql = "null" if (! $sql);
235            return $sql;
236    }
237    
238    
239  # get all InfoTypes  # get all InfoTypes
240  sub get_infotypes {  sub get_infotypes {
241          my $self = shift;          my $self = shift;
# Line 216  sub get_infotypes { Line 243  sub get_infotypes {
243          my $q = $self->query();          my $q = $self->query();
244          my @args;          my @args;
245                    
         push @args,$q->param('it') || undef;    # for selected  
           
246          my $sql = qq{          my $sql = qq{
247                  select distinct infotype.infotype_id,infotype.infotype, 0 as half, (infotype.infotype_id = ?) as selected                  select distinct infotype.infotype_id,infotype.infotype, 0 as half
248                  from res_sub_infotype,infotype                  from res_sub_infotype,infotype
249                  where res_sub_infotype.infotype_id=infotype.infotype_id and infotype.infotype_id > 1                  where res_sub_infotype.infotype_id=infotype.infotype_id and infotype.infotype_id > 1
250          };          };
# Line 233  sub get_infotypes { Line 258  sub get_infotypes {
258                  };                  };
259                  push @args, $q->param('s');                  push @args, $q->param('s');
260          } elsif ($q->param('ms')) {          } elsif ($q->param('ms')) {
261                  $sql .= qq{                  my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
262                          and res_sub_infotype.subject_id in                  $sql .= qq{ and res_sub_infotype.subject_id in ($ss_sql) };
                                 (select subject_id from sub_mastersubject where mastersubject_id = ?)  
                 };  
                 push @args, $q->param('ms');  
263          }          }
264    
265          $sql .= qq{          $sql .= qq{
# Line 252  sub get_infotypes { Line 274  sub get_infotypes {
274          # find element which is on half of list          # find element which is on half of list
275          my $half = int(scalar @$arr / 2) - 1;          my $half = int(scalar @$arr / 2) - 1;
276          $arr->[$half]->{half} = 1 if ($half > 0);          $arr->[$half]->{half} = 1 if ($half > 0);
277    
278            return $arr if (! $q->param('it'));
279    
280            # add selected column
281            foreach my $i ( 0 .. (scalar @$arr)-1 ) {
282                    $arr->[$i]->{'selected'} = ($arr->[$i]->{'infotype_id'} == $q->param('it'));
283            }
284          return $arr;          return $arr;
285  }  }
286    
# Line 260  sub get_subjects_letters { Line 289  sub get_subjects_letters {
289          my $self = shift;          my $self = shift;
290    
291          my $sql = qq{          my $sql = qq{
292          select distinct substr(subject,1,1) as letter          select distinct substring(subject from 1 for 1) as letter
293                  from subject                  from subject
294                  where subject_id > 1                  where subject_id > 1
295                  order by substr(subject,1,1)                  order by substring(subject from 1 for 1)
296          };          };
297    
298          my $sth = $dbh->prepare($sql);          my $sth = $dbh->prepare($sql);
# Line 375  sub get_resources { Line 404  sub get_resources {
404                  push @args, $q->param('s');                  push @args, $q->param('s');
405          } elsif ($q->param('ms')) {          } elsif ($q->param('ms')) {
406                  $sql_from .= qq{ , res_sub_infotype };                  $sql_from .= qq{ , res_sub_infotype };
407                    my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
408                  $sql_where .= qq{                  $sql_where .= qq{
409                          and res_sub_infotype.resource_id = resource.resource_id                          and res_sub_infotype.resource_id = resource.resource_id
410                          and res_sub_infotype.subject_id in                          and res_sub_infotype.subject_id in ($ss_sql)
                                 (select subject_id from sub_mastersubject where mastersubject_id = ?)  
411                  };                  };
                 push @args, $q->param('ms');  
412          }          }
413          if ($q->param('it')) {          if ($q->param('it')) {
414                  if ($sql_from !~ m/res_sub_infotype/) {                  if ($sql_from !~ m/res_sub_infotype/) {
Legend:
Removed from v.11  
changed lines
  Added in v.12
  ViewVC Help
Powered by ViewVC 1.1.26