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 |
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; |
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 |
}; |
}; |
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{ |
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 |
|
|
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); |
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/) { |