mysql/trunk/Portal.pm

package Portal;

use base 'CGI::Application';
use strict;

use Config::IniFiles;
use DBI;
use Carp;

use Data::Dumper;

use lib '..';

my @persistent_vars = qw(p ms s);

# read global.conf configuration
my $cfg_global = new Config::IniFiles( -file => '../global.conf' ) || die "can't open 'global.conf'";

# configuration options from global.conf
my $TEMPLATE_PATH = $cfg_global->val('portal', 'template_html') || die "need template_html in global.conf, section portal";
my $CHARSET = $cfg_global->val('portal', 'charset') || 'ISO-8859-1';
my $dsn = $cfg_global->val('portal', 'dbi_dsn') || die "need dsn in global.conf, section portal";
my ($user,$passwd) = ($cfg_global->val('portal', 'dbi_user'), $cfg_global->val('portal', 'dbi_passwd'));
my $locale = $cfg_global->val('portal', 'locale') || 'C';


my $dbh = DBI->connect("DBI:".$dsn,$user,$passwd, { RaiseError => 1 });

use POSIX qw(locale_h);
setlocale(LC_CTYPE, $locale);
use locale;

sub setup {
        my $self = shift;
        $self->tmpl_path($TEMPLATE_PATH);
        $self->run_modes(
                'home' => 'show_home',
                'ms' => 'show_mastersubject',
                's' => 'show_subject',
                'r' => 'search_resources',
        );
        $self->start_mode('home');
        $self->mode_param('p');

        $self->header_props(-charset=>$CHARSET);
}


# home page
sub show_home {
        my $self = shift;

        # Get the CGI.pm query object
        my $q = $self->query();
        # template

        # read master template
        my $tmpl = $self->use_template('home.html');

        $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
        $tmpl->param('InfoTypes' => $self->get_infotypes() );

        $tmpl->param('Subjects_letters' => $self->get_subjects_letters() );
        $tmpl->param('Subjects' => $self->get_subjects() );

        return $tmpl->output;

}


# MasterSubject
sub show_mastersubject {
        my $self = shift;

        my $q = $self->query();

        my $tmpl = $self->use_template('ms.html');

        $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );

        my $ms = $self->get_mastersubject_by_id($q->param('ms'));

        $tmpl->param('title' => uc($ms->{'mastersubject'}) );
        $tmpl->param('search_field' => lc($ms->{'mastersubject'}) );

        $tmpl->param('InfoTypes' => $self->get_infotypes() );

        $tmpl->param('Subjects' => $self->get_subjects() );

        return $tmpl->output;

}


# Subject
sub show_subject {
        my $self = shift;

        my $q = $self->query();

        my $tmpl = $self->use_template('s.html');

        $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );

        my $s = $self->get_subject_by_id($q->param('s'));

        $tmpl->param('title' => uc($s->{'subject'}) );
        $tmpl->param('search_field' => lc($s->{'subject'}) );

        $tmpl->param('InfoTypes' => $self->get_infotypes() );

        return $tmpl->output;

}


# search for resources and display results
sub search_resources {
        my $self = shift;

        my $q = $self->query();

        my $tmpl = $self->use_template('r.html');

        $tmpl->param('MasterSubjects' => $self->get_mastersubjects() );

        if ($q->param('s')) {
                my $s = $self->get_subject_by_id($q->param('s'));

                $tmpl->param('title' => uc($s->{'subject'}) );
                $tmpl->param('search_field' => lc($s->{'subject'}) );
        } elsif ($q->param('ms')) {
                my $s = $self->get_mastersubject_by_id($q->param('ms'));

                $tmpl->param('title' => uc($s->{'mastersubject'}) );
                $tmpl->param('search_field' => lc($s->{'mastersubject'}) );
        }

        my $res = $self->get_resources();
        $tmpl->param('resource_results' => $res);
        $tmpl->param('nr_results' => scalar @$res);

        $tmpl->param('limit_infotype' => $self->get_infotype_by_id($q->param('it'))->{'infotype'});

        return $tmpl->output;

}

#
# load template and generate permanent valirables in template
#

sub use_template {
        my $self = shift;
        my $q = $self->query();
        
        my $tmpl_file = shift || croak("perm_vars need tempate file");
        my $tmpl = $self->load_tmpl($tmpl_file, global_vars => 1, die_on_bad_params => 0);

        $tmpl->param('self_url_full', $q->url(-relative=>1,-query=>1));
        $tmpl->param('self_url', $q->url(-relative=>1));

        foreach my $var (@persistent_vars) {
                $tmpl->param($var, $q->param($var));
        }

        return $tmpl;
}


#
# get data from database
#

# get all MasterSubjects
sub get_mastersubjects {
        my $self = shift;

        my $q = $self->query();

        my $sql = qq{
        select mastersubject_id,upper(mastersubject) as mastersubject
                from mastersubject
                where mastersubject_id > 2
                order by mastersubject
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute();
        my $arr = $sth->fetchall_arrayref({});

        return if (! $arr);
        return $arr if (! $q->param('ms'));

        # add selected column
        foreach my $i ( 0 .. (scalar @$arr)-1 ) {
                $arr->[$i]->{'selected'} = ($arr->[$i]->{'mastersubject_id'} == $q->param('ms'));
        }
        return $arr;
}

# get one MasterSubject by it's ID
sub get_mastersubject_by_id {
        my $self = shift;

        my $id = shift || croak("need mastersubject id");

        my $sql = qq{
        select mastersubject
                from mastersubject
                where mastersubject_id = ?
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute($id);

        return $sth->fetchrow_hashref();
}

# WARNING: Cludge ahead!
#
# stupid MySQL doesn't support subselects.
#
sub select_subject_id_where_mastersubject_id {
        my $id = shift || croak("sub select emulation needs mastersubject_id");
        my $sql = qq{ select subject_id from sub_mastersubject where mastersubject_id = ? };

        my $sth = $dbh->prepare($sql);
        $sth->execute($id);

        my $arr = $sth->fetchall_hashref('subject_id');

        $sql = join(",",keys %$arr);
        $sql = "null" if (! $sql);
        return $sql;
}


# get all InfoTypes
sub get_infotypes {
        my $self = shift;

        my $q = $self->query();
        my @args;
        
        my $sql = qq{
                select distinct infotype.infotype_id,infotype.infotype, 0 as half
                from res_sub_infotype,infotype
                where res_sub_infotype.infotype_id=infotype.infotype_id and infotype.infotype_id > 1
        };


        # first check if subject is defined and limit by that, and only if it's not
        # fallback to mastersubject
        if ($q->param('s')) {
                $sql .= qq{
                        and res_sub_infotype.subject_id = ?
                };
                push @args, $q->param('s');
        } elsif ($q->param('ms')) {
                my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
                $sql .= qq{ and res_sub_infotype.subject_id in ($ss_sql) };
        }

        $sql .= qq{
                order by infotype
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute(@args);

        my $arr = $sth->fetchall_arrayref({});

        # find element which is on half of list
        my $half = int(scalar @$arr / 2) - 1;
        $arr->[$half]->{half} = 1 if ($half > 0);

        return $arr if (! $q->param('it'));

        # add selected column
        foreach my $i ( 0 .. (scalar @$arr)-1 ) {
                $arr->[$i]->{'selected'} = ($arr->[$i]->{'infotype_id'} == $q->param('it'));
        }
        return $arr;
}

# get first letters for all Subjects
sub get_subjects_letters {
        my $self = shift;

        my $sql = qq{
        select distinct substring(subject from 1 for 1) as letter
                from subject
                where subject_id > 1
                order by substring(subject from 1 for 1)
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute();

        return $sth->fetchall_arrayref({});
}

# get all Subjects
sub get_subjects {
        my $self = shift;

        my $q = $self->query();
        my @args;

        my $sql = qq{
        select subject.subject_id,subject.subject,sub_mastersubject.mastersubject_id
                from subject,sub_mastersubject
                where subject.subject_id=sub_mastersubject.subject_id
                        and subject.subject_id > 1
        };

        if ($q->param('s_letter')) {
                push @args,$q->param('s_letter') . '%';
                $sql .= qq{
                        and upper(subject.subject) like upper(?)
                };
        }

        if ($q->param('ms')) {
                push @args,$q->param('ms');
                $sql .= qq{
                        and sub_mastersubject.mastersubject_id = ?
                };
        }

        $sql .= qq{
                order by subject.subject
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute(@args);

        return $sth->fetchall_arrayref({});
}

# get one Subject by it's ID
sub get_subject_by_id {
        my $self = shift;

        my $id = shift || croak("need subject id");

        my $sql = qq{
        select subject
                from subject
                where subject_id = ?
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute($id);

        return $sth->fetchrow_hashref();
}

# get one InfoType by it's ID
sub get_infotype_by_id {
        my $self = shift;

        my $id = shift || croak("need infotype id");

        my $sql = qq{
        select infotype
                from infotype
                where infotype_id = ?
        };

        my $sth = $dbh->prepare($sql);
        $sth->execute($id);

        return $sth->fetchrow_hashref();
}

# get add resources for given criteria
sub get_resources {
        my $self = shift;

        my $q = $self->query();
        my @args;

        my $sql = qq{
                select distinct resource.resource_id, title, infotype.infotype, coverage_detail, url
        };

        my $sql_from = qq{
                        from resource,infotype
        };

        my $sql_where = qq{
                        where resource.infotype_id=infotype.infotype_id
        };

        # limits
        if ($q->param('s')) {
                $sql_from .= qq{ , res_sub_infotype };
                $sql_where .= qq{
                        and res_sub_infotype.resource_id = resource.resource_id
                        and res_sub_infotype.subject_id = ?
                };
                push @args, $q->param('s');
        } elsif ($q->param('ms')) {
                $sql_from .= qq{ , res_sub_infotype };
                my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
                $sql_where .= qq{
                        and res_sub_infotype.resource_id = resource.resource_id
                        and res_sub_infotype.subject_id in ($ss_sql)
                };
        }
        if ($q->param('it')) {
                if ($sql_from !~ m/res_sub_infotype/) {
                        $sql_from .= qq{ , res_sub_infotype };
                        $sql_where .= qq{ and res_sub_infotype.resource_id = resource.resource_id };
                }
                $sql_where .= qq{ and res_sub_infotype.infotype_id = ? };
                push @args, $q->param('it');
        }

        my $sth = $dbh->prepare($sql.$sql_from.$sql_where);
        $sth->execute(@args);

        my $arr = $sth->fetchall_arrayref({});

        # now fill-in features
        $sql = qq{
                select feature
                        from res_feature,feature
                        where res_feature.feature_id = feature.feature_id and res_feature.resource_id = ?
        };
        $sth = $dbh->prepare($sql);

        foreach my $i ( 0 .. (scalar @$arr)-1 ) {
                $sth->execute($arr->[$i]->{'resource_id'});
                $arr->[$i]->{'res_features'} = $sth->fetchall_arrayref({});
        }

        return $arr;
}
1;
1	dpavlin	1	package Portal;
2
3			use base 'CGI::Application';
4			use strict;
5
6			use Config::IniFiles;
7			use DBI;
8	dpavlin	3	use Carp;
9	dpavlin	1
10			use Data::Dumper;
11
12			use lib '..';
13
14	dpavlin	6	my @persistent_vars = qw(p ms s);
15	dpavlin	1
16			# read global.conf configuration
17			my $cfg_global = new Config::IniFiles( -file => '../global.conf' ) \|\| die "can't open 'global.conf'";
18
19			# configuration options from global.conf
20	dpavlin	8	my $TEMPLATE_PATH = $cfg_global->val('portal', 'template_html') \|\| die "need template_html in global.conf, section portal";
21			my $CHARSET = $cfg_global->val('portal', 'charset') \|\| 'ISO-8859-1';
22			my $dsn = $cfg_global->val('portal', 'dbi_dsn') \|\| die "need dsn in global.conf, section portal";
23			my ($user,$passwd) = ($cfg_global->val('portal', 'dbi_user'), $cfg_global->val('portal', 'dbi_passwd'));
24			my $locale = $cfg_global->val('portal', 'locale') \|\| 'C';
25	dpavlin	1
26
27	dpavlin	8	my $dbh = DBI->connect("DBI:".$dsn,$user,$passwd, { RaiseError => 1 });
28
29	dpavlin	1	use POSIX qw(locale_h);
30	dpavlin	8	setlocale(LC_CTYPE, $locale);
31	dpavlin	1	use locale;
32
33			sub setup {
34			my $self = shift;
35			$self->tmpl_path($TEMPLATE_PATH);
36			$self->run_modes(
37			'home' => 'show_home',
38	dpavlin	6	'ms' => 'show_mastersubject',
39			's' => 'show_subject',
40			'r' => 'search_resources',
41	dpavlin	1	);
42			$self->start_mode('home');
43			$self->mode_param('p');
44
45			$self->header_props(-charset=>$CHARSET);
46			}
47
48
49	dpavlin	3	# home page
50	dpavlin	1	sub show_home {
51			my $self = shift;
52
53			# Get the CGI.pm query object
54			my $q = $self->query();
55			# template
56
57			# read master template
58			my $tmpl = $self->use_template('home.html');
59
60			$tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
61			$tmpl->param('InfoTypes' => $self->get_infotypes() );
62
63			$tmpl->param('Subjects_letters' => $self->get_subjects_letters() );
64			$tmpl->param('Subjects' => $self->get_subjects() );
65
66			return $tmpl->output;
67
68			}
69
70	dpavlin	3
71			# MasterSubject
72	dpavlin	6	sub show_mastersubject {
73	dpavlin	1	my $self = shift;
74
75			my $q = $self->query();
76
77			my $tmpl = $self->use_template('ms.html');
78
79			$tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
80
81	dpavlin	6	my $ms = $self->get_mastersubject_by_id($q->param('ms'));
82	dpavlin	1
83			$tmpl->param('title' => uc($ms->{'mastersubject'}) );
84	dpavlin	3	$tmpl->param('search_field' => lc($ms->{'mastersubject'}) );
85	dpavlin	1
86			$tmpl->param('InfoTypes' => $self->get_infotypes() );
87
88			$tmpl->param('Subjects' => $self->get_subjects() );
89
90			return $tmpl->output;
91
92			}
93
94	dpavlin	3
95			# Subject
96	dpavlin	6	sub show_subject {
97	dpavlin	3	my $self = shift;
98
99			my $q = $self->query();
100
101			my $tmpl = $self->use_template('s.html');
102
103			$tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
104
105	dpavlin	6	my $s = $self->get_subject_by_id($q->param('s'));
106	dpavlin	3
107			$tmpl->param('title' => uc($s->{'subject'}) );
108			$tmpl->param('search_field' => lc($s->{'subject'}) );
109
110			$tmpl->param('InfoTypes' => $self->get_infotypes() );
111
112			return $tmpl->output;
113
114			}
115
116
117	dpavlin	6	# search for resources and display results
118			sub search_resources {
119			my $self = shift;
120
121			my $q = $self->query();
122
123			my $tmpl = $self->use_template('r.html');
124
125			$tmpl->param('MasterSubjects' => $self->get_mastersubjects() );
126
127			if ($q->param('s')) {
128			my $s = $self->get_subject_by_id($q->param('s'));
129
130			$tmpl->param('title' => uc($s->{'subject'}) );
131			$tmpl->param('search_field' => lc($s->{'subject'}) );
132			} elsif ($q->param('ms')) {
133			my $s = $self->get_mastersubject_by_id($q->param('ms'));
134
135			$tmpl->param('title' => uc($s->{'mastersubject'}) );
136			$tmpl->param('search_field' => lc($s->{'mastersubject'}) );
137			}
138
139			my $res = $self->get_resources();
140			$tmpl->param('resource_results' => $res);
141			$tmpl->param('nr_results' => scalar @$res);
142
143			$tmpl->param('limit_infotype' => $self->get_infotype_by_id($q->param('it'))->{'infotype'});
144
145			return $tmpl->output;
146
147			}
148
149	dpavlin	3	#
150	dpavlin	1	# load template and generate permanent valirables in template
151	dpavlin	3	#
152	dpavlin	1
153			sub use_template {
154			my $self = shift;
155			my $q = $self->query();
156
157			my $tmpl_file = shift \|\| croak("perm_vars need tempate file");
158			my $tmpl = $self->load_tmpl($tmpl_file, global_vars => 1, die_on_bad_params => 0);
159
160			$tmpl->param('self_url_full', $q->url(-relative=>1,-query=>1));
161			$tmpl->param('self_url', $q->url(-relative=>1));
162
163			foreach my $var (@persistent_vars) {
164			$tmpl->param($var, $q->param($var));
165			}
166
167			return $tmpl;
168			}
169
170	dpavlin	3
171			#
172	dpavlin	1	# get data from database
173	dpavlin	3	#
174	dpavlin	1
175	dpavlin	3	# get all MasterSubjects
176	dpavlin	1	sub get_mastersubjects {
177			my $self = shift;
178
179			my $q = $self->query();
180
181			my $sql = qq{
182	dpavlin	12	select mastersubject_id,upper(mastersubject) as mastersubject
183	dpavlin	1	from mastersubject
184			where mastersubject_id > 2
185			order by mastersubject
186			};
187
188			my $sth = $dbh->prepare($sql);
189	dpavlin	12	$sth->execute();
190			my $arr = $sth->fetchall_arrayref({});
191	dpavlin	1
192	dpavlin	12	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	dpavlin	1	}
201
202	dpavlin	3	# get one MasterSubject by it's ID
203	dpavlin	6	sub get_mastersubject_by_id {
204	dpavlin	1	my $self = shift;
205
206			my $id = shift \|\| croak("need mastersubject id");
207
208			my $sql = qq{
209			select mastersubject
210			from mastersubject
211			where mastersubject_id = ?
212			};
213
214			my $sth = $dbh->prepare($sql);
215			$sth->execute($id);
216
217			return $sth->fetchrow_hashref();
218			}
219
220	dpavlin	12	# 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	dpavlin	3	# get all InfoTypes
240	dpavlin	1	sub get_infotypes {
241			my $self = shift;
242
243			my $q = $self->query();
244			my @args;
245
246			my $sql = qq{
247	dpavlin	12	select distinct infotype.infotype_id,infotype.infotype, 0 as half
248	dpavlin	1	from res_sub_infotype,infotype
249			where res_sub_infotype.infotype_id=infotype.infotype_id and infotype.infotype_id > 1
250			};
251
252	dpavlin	3
253			# first check if subject is defined and limit by that, and only if it's not
254			# fallback to mastersubject
255			if ($q->param('s')) {
256	dpavlin	1	$sql .= qq{
257	dpavlin	3	and res_sub_infotype.subject_id = ?
258			};
259			push @args, $q->param('s');
260			} elsif ($q->param('ms')) {
261	dpavlin	12	my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
262			$sql .= qq{ and res_sub_infotype.subject_id in ($ss_sql) };
263	dpavlin	1	}
264
265			$sql .= qq{
266			order by infotype
267			};
268
269			my $sth = $dbh->prepare($sql);
270			$sth->execute(@args);
271
272			my $arr = $sth->fetchall_arrayref({});
273
274			# find element which is on half of list
275			my $half = int(scalar @$arr / 2) - 1;
276			$arr->[$half]->{half} = 1 if ($half > 0);
277	dpavlin	12
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	dpavlin	1	return $arr;
285			}
286
287	dpavlin	3	# get first letters for all Subjects
288	dpavlin	1	sub get_subjects_letters {
289			my $self = shift;
290
291			my $sql = qq{
292	dpavlin	12	select distinct substring(subject from 1 for 1) as letter
293	dpavlin	1	from subject
294			where subject_id > 1
295	dpavlin	12	order by substring(subject from 1 for 1)
296	dpavlin	1	};
297
298			my $sth = $dbh->prepare($sql);
299			$sth->execute();
300
301			return $sth->fetchall_arrayref({});
302			}
303
304	dpavlin	3	# get all Subjects
305	dpavlin	1	sub get_subjects {
306			my $self = shift;
307
308			my $q = $self->query();
309			my @args;
310
311			my $sql = qq{
312			select subject.subject_id,subject.subject,sub_mastersubject.mastersubject_id
313			from subject,sub_mastersubject
314			where subject.subject_id=sub_mastersubject.subject_id
315			and subject.subject_id > 1
316			};
317
318			if ($q->param('s_letter')) {
319			push @args,$q->param('s_letter') . '%';
320			$sql .= qq{
321			and upper(subject.subject) like upper(?)
322			};
323			}
324
325			if ($q->param('ms')) {
326			push @args,$q->param('ms');
327			$sql .= qq{
328			and sub_mastersubject.mastersubject_id = ?
329			};
330			}
331
332			$sql .= qq{
333			order by subject.subject
334			};
335
336			my $sth = $dbh->prepare($sql);
337			$sth->execute(@args);
338
339			return $sth->fetchall_arrayref({});
340			}
341
342	dpavlin	3	# get one Subject by it's ID
343	dpavlin	6	sub get_subject_by_id {
344	dpavlin	3	my $self = shift;
345
346			my $id = shift \|\| croak("need subject id");
347
348			my $sql = qq{
349			select subject
350			from subject
351			where subject_id = ?
352			};
353
354			my $sth = $dbh->prepare($sql);
355			$sth->execute($id);
356
357			return $sth->fetchrow_hashref();
358			}
359
360	dpavlin	6	# get one InfoType by it's ID
361			sub get_infotype_by_id {
362			my $self = shift;
363
364			my $id = shift \|\| croak("need infotype id");
365
366			my $sql = qq{
367			select infotype
368			from infotype
369			where infotype_id = ?
370			};
371
372			my $sth = $dbh->prepare($sql);
373			$sth->execute($id);
374
375			return $sth->fetchrow_hashref();
376			}
377
378			# get add resources for given criteria
379			sub get_resources {
380			my $self = shift;
381
382			my $q = $self->query();
383			my @args;
384
385			my $sql = qq{
386			select distinct resource.resource_id, title, infotype.infotype, coverage_detail, url
387			};
388
389			my $sql_from = qq{
390			from resource,infotype
391			};
392
393			my $sql_where = qq{
394			where resource.infotype_id=infotype.infotype_id
395			};
396
397			# limits
398			if ($q->param('s')) {
399			$sql_from .= qq{ , res_sub_infotype };
400			$sql_where .= qq{
401			and res_sub_infotype.resource_id = resource.resource_id
402			and res_sub_infotype.subject_id = ?
403			};
404			push @args, $q->param('s');
405			} elsif ($q->param('ms')) {
406			$sql_from .= qq{ , res_sub_infotype };
407	dpavlin	12	my $ss_sql = select_subject_id_where_mastersubject_id($q->param('ms'));
408	dpavlin	6	$sql_where .= qq{
409			and res_sub_infotype.resource_id = resource.resource_id
410	dpavlin	12	and res_sub_infotype.subject_id in ($ss_sql)
411	dpavlin	6	};
412			}
413			if ($q->param('it')) {
414			if ($sql_from !~ m/res_sub_infotype/) {
415			$sql_from .= qq{ , res_sub_infotype };
416			$sql_where .= qq{ and res_sub_infotype.resource_id = resource.resource_id };
417			}
418			$sql_where .= qq{ and res_sub_infotype.infotype_id = ? };
419			push @args, $q->param('it');
420			}
421
422			my $sth = $dbh->prepare($sql.$sql_from.$sql_where);
423			$sth->execute(@args);
424
425			my $arr = $sth->fetchall_arrayref({});
426
427			# now fill-in features
428			$sql = qq{
429			select feature
430			from res_feature,feature
431			where res_feature.feature_id = feature.feature_id and res_feature.resource_id = ?
432			};
433			$sth = $dbh->prepare($sql);
434
435			foreach my $i ( 0 .. (scalar @$arr)-1 ) {
436			$sth->execute($arr->[$i]->{'resource_id'});
437			$arr->[$i]->{'res_features'} = $sth->fetchall_arrayref({});
438			}
439
440			return $arr;
441			}
442	dpavlin	1	1;