/[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 1 by dpavlin, Sun Mar 7 18:22:26 2004 UTC revision 6 by dpavlin, Sun Mar 7 20:40:47 2004 UTC
# Line 5  use strict; Line 5  use strict;
5    
6  use Config::IniFiles;  use Config::IniFiles;
7  use DBI;  use DBI;
8    use Carp;
9    
10  use Data::Dumper;  use Data::Dumper;
11    
# Line 13  use lib '..'; Line 14  use lib '..';
14  my $dsn = 'Pg:dbname=libdata';  my $dsn = 'Pg:dbname=libdata';
15  my ($user,$passwd) = ('dpavlin','');  my ($user,$passwd) = ('dpavlin','');
16    
17  my @persistent_vars = qw(p);  my @persistent_vars = qw(p ms s);
18    
19  # read global.conf configuration  # read global.conf configuration
20  my $cfg_global = new Config::IniFiles( -file => '../global.conf' ) || die "can't open 'global.conf'";  my $cfg_global = new Config::IniFiles( -file => '../global.conf' ) || die "can't open 'global.conf'";
# Line 33  sub setup { Line 34  sub setup {
34          $self->tmpl_path($TEMPLATE_PATH);          $self->tmpl_path($TEMPLATE_PATH);
35          $self->run_modes(          $self->run_modes(
36                  'home' => 'show_home',                  'home' => 'show_home',
37                  'ms' => 'show_ms',                  'ms' => 'show_mastersubject',
38                  'it' => 'show_home',                  's' => 'show_subject',
39                  's' => 'show_home',                  'r' => 'search_resources',
40          );          );
41          $self->start_mode('home');          $self->start_mode('home');
42          $self->mode_param('p');          $self->mode_param('p');
# Line 44  sub setup { Line 45  sub setup {
45  }  }
46    
47    
48    # home page
49  sub show_home {  sub show_home {
50          my $self = shift;          my $self = shift;
51    
# Line 64  sub show_home { Line 66  sub show_home {
66    
67  }  }
68    
69  sub show_ms {  
70    # MasterSubject
71    sub show_mastersubject {
72          my $self = shift;          my $self = shift;
73    
74          my $q = $self->query();          my $q = $self->query();
# Line 73  sub show_ms { Line 77  sub show_ms {
77    
78          $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );          $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
79    
80          my $ms = $self->get_mastersubjects_by_id($q->param('ms'));          my $ms = $self->get_mastersubject_by_id($q->param('ms'));
81    
82          $tmpl->param('title' => uc($ms->{'mastersubject'}) );          $tmpl->param('title' => uc($ms->{'mastersubject'}) );
83          $tmpl->param('mastersubject_lc' => lc($ms->{'mastersubject'}) );          $tmpl->param('search_field' => lc($ms->{'mastersubject'}) );
84    
85          $tmpl->param('InfoTypes' => $self->get_infotypes() );          $tmpl->param('InfoTypes' => $self->get_infotypes() );
86    
# Line 86  sub show_ms { Line 90  sub show_ms {
90    
91  }  }
92    
93    
94    # Subject
95    sub show_subject {
96            my $self = shift;
97    
98            my $q = $self->query();
99    
100            my $tmpl = $self->use_template('s.html');
101    
102            $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
103    
104            my $s = $self->get_subject_by_id($q->param('s'));
105    
106            $tmpl->param('title' => uc($s->{'subject'}) );
107            $tmpl->param('search_field' => lc($s->{'subject'}) );
108    
109            $tmpl->param('InfoTypes' => $self->get_infotypes() );
110    
111            return $tmpl->output;
112    
113    }
114    
115    
116    # search for resources and display results
117    sub search_resources {
118            my $self = shift;
119    
120            my $q = $self->query();
121    
122            my $tmpl = $self->use_template('r.html');
123    
124            $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
125    
126            if ($q->param('s')) {
127                    my $s = $self->get_subject_by_id($q->param('s'));
128    
129                    $tmpl->param('title' => uc($s->{'subject'}) );
130                    $tmpl->param('search_field' => lc($s->{'subject'}) );
131            } elsif ($q->param('ms')) {
132                    my $s = $self->get_mastersubject_by_id($q->param('ms'));
133    
134                    $tmpl->param('title' => uc($s->{'mastersubject'}) );
135                    $tmpl->param('search_field' => lc($s->{'mastersubject'}) );
136            }
137    
138            my $res = $self->get_resources();
139            $tmpl->param('resource_results' => $res);
140            $tmpl->param('nr_results' => scalar @$res);
141    
142            $tmpl->param('limit_infotype' => $self->get_infotype_by_id($q->param('it'))->{'infotype'});
143    
144            return $tmpl->output;
145    
146    }
147    
148    #
149  # load template and generate permanent valirables in template  # load template and generate permanent valirables in template
150    #
151    
152  sub use_template {  sub use_template {
153          my $self = shift;          my $self = shift;
# Line 105  sub use_template { Line 166  sub use_template {
166          return $tmpl;          return $tmpl;
167  }  }
168    
169    
170    #
171  # get data from database  # get data from database
172    #
173    
174    # get all MasterSubjects
175  sub get_mastersubjects {  sub get_mastersubjects {
176          my $self = shift;          my $self = shift;
177    
# Line 125  sub get_mastersubjects { Line 190  sub get_mastersubjects {
190          return $sth->fetchall_arrayref({});          return $sth->fetchall_arrayref({});
191  }  }
192    
193  sub get_mastersubjects_by_id {  # get one MasterSubject by it's ID
194    sub get_mastersubject_by_id {
195          my $self = shift;          my $self = shift;
196    
197          my $id = shift || croak("need mastersubject id");          my $id = shift || croak("need mastersubject id");
# Line 142  sub get_mastersubjects_by_id { Line 208  sub get_mastersubjects_by_id {
208          return $sth->fetchrow_hashref();          return $sth->fetchrow_hashref();
209  }  }
210    
211    # get all InfoTypes
212  sub get_infotypes {  sub get_infotypes {
213          my $self = shift;          my $self = shift;
214    
# Line 156  sub get_infotypes { Line 223  sub get_infotypes {
223                  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
224          };          };
225    
226          if ($q->param('ms')) {  
227            # first check if subject is defined and limit by that, and only if it's not
228            # fallback to mastersubject
229            if ($q->param('s')) {
230                    $sql .= qq{
231                            and res_sub_infotype.subject_id = ?
232                    };
233                    push @args, $q->param('s');
234            } elsif ($q->param('ms')) {
235                  $sql .= qq{                  $sql .= qq{
236                          and res_sub_infotype.subject_id in                          and res_sub_infotype.subject_id in
237                                  (select subject_id from sub_mastersubject where mastersubject_id = ?)                                  (select subject_id from sub_mastersubject where mastersubject_id = ?)
# Line 179  sub get_infotypes { Line 254  sub get_infotypes {
254          return $arr;          return $arr;
255  }  }
256    
257    # get first letters for all Subjects
258  sub get_subjects_letters {  sub get_subjects_letters {
259          my $self = shift;          my $self = shift;
260    
# Line 195  sub get_subjects_letters { Line 271  sub get_subjects_letters {
271          return $sth->fetchall_arrayref({});          return $sth->fetchall_arrayref({});
272  }  }
273    
274    # get all Subjects
275  sub get_subjects {  sub get_subjects {
276          my $self = shift;          my $self = shift;
277    
# Line 232  sub get_subjects { Line 309  sub get_subjects {
309          return $sth->fetchall_arrayref({});          return $sth->fetchall_arrayref({});
310  }  }
311    
312    # get one Subject by it's ID
313    sub get_subject_by_id {
314            my $self = shift;
315    
316            my $id = shift || croak("need subject id");
317    
318            my $sql = qq{
319            select subject
320                    from subject
321                    where subject_id = ?
322            };
323    
324            my $sth = $dbh->prepare($sql);
325            $sth->execute($id);
326    
327            return $sth->fetchrow_hashref();
328    }
329    
330    # get one InfoType by it's ID
331    sub get_infotype_by_id {
332            my $self = shift;
333    
334            my $id = shift || croak("need infotype id");
335    
336            my $sql = qq{
337            select infotype
338                    from infotype
339                    where infotype_id = ?
340            };
341    
342            my $sth = $dbh->prepare($sql);
343            $sth->execute($id);
344    
345            return $sth->fetchrow_hashref();
346    }
347    
348    # get add resources for given criteria
349    sub get_resources {
350            my $self = shift;
351    
352            my $q = $self->query();
353            my @args;
354    
355            my $sql = qq{
356                    select distinct resource.resource_id, title, infotype.infotype, coverage_detail, url
357            };
358    
359            my $sql_from = qq{
360                            from resource,infotype
361            };
362    
363            my $sql_where = qq{
364                            where resource.infotype_id=infotype.infotype_id
365            };
366    
367            # limits
368            if ($q->param('s')) {
369                    $sql_from .= qq{ , res_sub_infotype };
370                    $sql_where .= qq{
371                            and res_sub_infotype.resource_id = resource.resource_id
372                            and res_sub_infotype.subject_id = ?
373                    };
374                    push @args, $q->param('s');
375            } elsif ($q->param('ms')) {
376                    $sql_from .= qq{ , res_sub_infotype };
377                    $sql_where .= qq{
378                            and res_sub_infotype.resource_id = resource.resource_id
379                            and res_sub_infotype.subject_id in
380                                    (select subject_id from sub_mastersubject where mastersubject_id = ?)
381                    };
382                    push @args, $q->param('ms');
383            }
384            if ($q->param('it')) {
385                    if ($sql_from !~ m/res_sub_infotype/) {
386                            $sql_from .= qq{ , res_sub_infotype };
387                            $sql_where .= qq{ and res_sub_infotype.resource_id = resource.resource_id };
388                    }
389                    $sql_where .= qq{ and res_sub_infotype.infotype_id = ? };
390                    push @args, $q->param('it');
391            }
392    
393            my $sth = $dbh->prepare($sql.$sql_from.$sql_where);
394            $sth->execute(@args);
395    
396            my $arr = $sth->fetchall_arrayref({});
397    
398            # now fill-in features
399            $sql = qq{
400                    select feature
401                            from res_feature,feature
402                            where res_feature.feature_id = feature.feature_id and res_feature.resource_id = ?
403            };
404            $sth = $dbh->prepare($sql);
405    
406            foreach my $i ( 0 .. (scalar @$arr)-1 ) {
407                    $sth->execute($arr->[$i]->{'resource_id'});
408                    $arr->[$i]->{'res_features'} = $sth->fetchall_arrayref({});
409            }
410    
411            return $arr;
412    }
413  1;  1;
Legend:
Removed from v.1  
changed lines
  Added in v.6
  ViewVC Help
Powered by ViewVC 1.1.26