View | Details | Raw Unified | Return to bug 12610
Collapse All | Expand All

(-)a/C4/Tags.pm (-185 / +166 lines)
Lines 40-46 BEGIN { Link Here
40
      &add_tags &add_tag
40
      &add_tags &add_tag
41
      &delete_tag_row_by_id
41
      &delete_tag_row_by_id
42
      &remove_tag
42
      &remove_tag
43
      &delete_tag_rows_by_ids
44
      &get_approval_rows
43
      &get_approval_rows
45
      &blacklist
44
      &blacklist
46
      &whitelist
45
      &whitelist
Lines 74-107 INIT { Link Here
74
    $debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n";
73
    $debug and print STDERR "\$Lingua::Ispell::path = $Lingua::Ispell::path\n";
75
}
74
}
76
75
77
sub get_filters {
78
	my $query = "SELECT * FROM tags_filters ";
79
	my ($sth);
80
	if (@_) {
81
		$sth = C4::Context->dbh->prepare($query . " WHERE filter_id = ? ");
82
		$sth->execute(shift);
83
	} else {
84
		$sth = C4::Context->dbh->prepare($query);
85
		$sth->execute;
86
	}
87
	return $sth->fetchall_arrayref({});
88
}
89
90
# 	(SELECT count(*) FROM tags_all     ) as tags_all,
91
# 	(SELECT count(*) FROM tags_index   ) as tags_index,
92
93
sub approval_counts {
76
sub approval_counts {
94
	my $query = "SELECT
77
    return Koha::Database->new()->schema()->resultset('TagsApproval')->count(
95
		(SELECT count(*) FROM tags_approval WHERE approved= 1) as approved_count,
78
        {
96
		(SELECT count(*) FROM tags_approval WHERE approved=-1) as rejected_count,
79
            approved => [ 1, -1, 0 ]
97
		(SELECT count(*) FROM tags_approval WHERE approved= 0) as unapproved_count
80
        }
98
	";
81
    );
99
	my $sth = C4::Context->dbh->prepare($query);
100
	$sth->execute;
101
	my $result = $sth->fetchrow_hashref();
102
	$result->{approved_total} = $result->{approved_count} + $result->{rejected_count} + $result->{unapproved_count};
103
	$debug and warn "counts returned: " . Dumper $result;
104
	return $result;
105
}
82
}
106
83
107
=head2 get_count_by_tag_status
84
=head2 get_count_by_tag_status
Lines 112-125 Takes a status and gets a count of tags with that status Link Here
112
89
113
=cut
90
=cut
114
91
115
sub get_count_by_tag_status  {
92
sub get_count_by_tag_status {
116
    my ($status) = @_;
93
    my ($status) = @_;
117
    my $dbh            = C4::Context->dbh;
94
    return Koha::Database->new()->schema()->resultset('TagsApproval')->count(
118
    my $query          =
95
        {
119
      "SELECT count(*) FROM tags_approval WHERE approved=?";
96
            approved => $status
120
    my $sth = $dbh->prepare($query);
97
        }
121
    $sth->execute( $status );
98
    );
122
  return $sth->fetchrow;
123
}
99
}
124
100
125
sub remove_tag {
101
sub remove_tag {
Lines 151-182 sub remove_tag { Link Here
151
}
127
}
152
128
153
sub delete_tag_index {
129
sub delete_tag_index {
154
	(@_) or return;
130
    my ( $term, $biblionumber ) = @_;
155
	my $sth = C4::Context->dbh->prepare("DELETE FROM tags_index WHERE term = ? AND biblionumber = ? LIMIT 1");
131
    return Koha::Database->new()->schema()->resultset('TagsIndex')->search(
156
	$sth->execute(@_);
132
        {
157
	return $sth->rows || 0;
133
            term         => $term,
134
            biblionumber => $biblionumber,
135
        }
136
    )->delete();
158
}
137
}
138
159
sub delete_tag_approval {
139
sub delete_tag_approval {
160
	(@_) or return;
140
    my ($term) = @_;
161
	my $sth = C4::Context->dbh->prepare("DELETE FROM tags_approval WHERE term = ? LIMIT 1");
141
    my $t = Koha::Database->new()->schema()->resultset('TagsApproval')->find( $term );
162
	$sth->execute(shift);
142
    return $t ? $t->delete() : 0;
163
	return $sth->rows || 0;
164
}
143
}
144
165
sub delete_tag_row_by_id {
145
sub delete_tag_row_by_id {
166
	(@_) or return;
146
    my ( $tag_id ) = @_;
167
	my $sth = C4::Context->dbh->prepare("DELETE FROM tags_all WHERE tag_id = ? LIMIT 1");
147
    my $t = Koha::Database->new()->schema()->resultset('TagsAll')->find( $tag_id );
168
	$sth->execute(shift);
148
    return $t ? $t->delete() : 0;
169
	return $sth->rows || 0;
170
}
171
sub delete_tag_rows_by_ids {
172
	(@_) or return;
173
	my $i=0;
174
	foreach(@_) {
175
		$i += delete_tag_row_by_id($_);
176
	}
177
	($i == scalar(@_)) or
178
		warn sprintf "delete_tag_rows_by_ids tried %s tag_ids, only succeeded on $i", scalar(@_);
179
	return $i;
180
}
149
}
181
150
182
sub get_tag_rows {
151
sub get_tag_rows {
Lines 365-391 sub get_approval_rows { # i.e., from tags_approval Link Here
365
}
334
}
366
335
367
sub is_approved {
336
sub is_approved {
368
	my $term = shift or return;
337
    my $term = shift or return;
369
	my $sth = C4::Context->dbh->prepare("SELECT approved FROM tags_approval WHERE term = ?");
370
	$sth->execute($term);
371
	unless ($sth->rows) {
372
		$ext_dict and return (spellcheck($term) ? 0 : 1);	# spellcheck returns empty on OK word
373
		return 0;
374
	}
375
	return $sth->fetchrow;
376
}
377
338
378
sub get_tag_index {
339
    my $tag = Koha::Database->new()->schema()->resultset('TagsIndex')->find(
379
	my $term = shift or return;
340
        { term => $term },
380
	my $sth;
341
        { result_class => 'DBIx::Class::ResultClass::HashRefInflator' } 
381
	if (@_) {
342
    );
382
		$sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?");
343
383
		$sth->execute($term,shift);
344
    unless ($tag) {
384
	} else {
345
        return $ext_dict ? spellcheck($term) ? 0 : 1 : 0;
385
		$sth = C4::Context->dbh->prepare("SELECT * FROM tags_index WHERE term = ?");
346
    }
386
		$sth->execute($term);
347
    
387
	}
348
    return $tag;
388
	return $sth->fetchrow_hashref;
389
}
349
}
390
350
391
sub whitelist {
351
sub whitelist {
Lines 407-412 sub whitelist { Link Here
407
	}
367
	}
408
	return scalar @_;
368
	return scalar @_;
409
}
369
}
370
410
# note: there is no "unwhitelist" operation because there is no remove for Ispell.
371
# note: there is no "unwhitelist" operation because there is no remove for Ispell.
411
# The blacklist regexps should operate "in front of" the whitelist, so if you approve
372
# The blacklist regexps should operate "in front of" the whitelist, so if you approve
412
# a term mistakenly, you can still reverse it. But there is no going back to "neutral".
373
# a term mistakenly, you can still reverse it. But there is no going back to "neutral".
Lines 423-576 sub blacklist { Link Here
423
	}
384
	}
424
	return scalar @_;
385
	return scalar @_;
425
}
386
}
426
sub add_filter {
427
	my $operator = shift;
428
	defined $operator or return; # have to test defined to allow =0 (kohaadmin)
429
	my $query = "INSERT INTO tags_blacklist (regexp,y,z) VALUES (?,?,?)";
430
	# my $sth = C4::Context->dbh->prepare($query);
431
	return scalar @_;
432
}
433
sub remove_filter {
434
	my $operator = shift;
435
	defined $operator or return; # have to test defined to allow =0 (kohaadmin)
436
	my $query = "REMOVE FROM tags_blacklist WHERE blacklist_id = ?";
437
	# my $sth = C4::Context->dbh->prepare($query);
438
	# $sth->execute($term);
439
	return scalar @_;
440
}
441
387
442
sub add_tag_approval {	# or disapproval
388
sub add_tag_approval {	# or disapproval
443
	$debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")";
389
	$debug and warn "add_tag_approval(" . join(", ",map {defined($_) ? $_ : 'UNDEF'} @_) . ")";
444
	my $term = shift or return;
390
    my $term = shift or return;
445
	my $query = "SELECT * FROM tags_approval WHERE term = ?";
391
    my $operator = shift || 0;
446
	my $sth = C4::Context->dbh->prepare($query);
392
    my $approval = ( @_ ? shift : 0 );    # default is unapproved
447
	$sth->execute($term);
393
448
	($sth->rows) and return increment_weight_total($term);
394
    my $rs = Koha::Database->new()->schema()->resultset('TagsApproval');
449
	my $operator = shift || 0;
395
450
	my $approval = (@_ ? shift : 0);	# default is unapproved
396
    my $count = $rs->search( { term => $term } );
451
	my @exe_args = ($term);		# all 3 queries will use this argument
397
    return increment_weight_total($term) if $count;
452
	if ($operator) {
398
453
		$query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,?,NOW())";
399
    return $rs->create(
454
		push @exe_args, $operator, $approval;
400
        {
455
	} elsif ($approval) {
401
            term          => $term,
456
		$query = "INSERT INTO tags_approval (term,approved,date_approved) VALUES (?,?,NOW())";
402
            approved      => $approval,
457
		push @exe_args, $approval;
403
            approved_by   => $operator,
458
	} else {
404
            date_approved => \'NOW()',
459
		$query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())";
405
        }
460
	}
406
    );
461
	$debug and print STDERR "add_tag_approval query: $query\nadd_tag_approval args: (" . join(", ", @exe_args) . ")\n";
462
	$sth = C4::Context->dbh->prepare($query);
463
	$sth->execute(@exe_args);
464
	return $sth->rows;
465
}
407
}
466
408
467
sub mod_tag_approval {
409
sub mod_tag_approval {
468
	my $operator = shift;
410
	my $operator = shift;
469
	defined $operator or return; # have to test defined to allow =0 (kohaadmin)
411
	defined $operator or return; # have to test defined to allow = 0 (kohaadmin)
470
	my $term     = shift or return;
412
	my $term     = shift or return;
471
	my $approval = (scalar @_ ? shift : 1);	# default is to approve
413
	my $approval = (scalar @_ ? shift : 1);	# default is to approve
472
	my $query = "UPDATE tags_approval SET approved_by=?, approved=?, date_approved=NOW() WHERE term = ?";
414
473
	$debug and print STDERR "mod_tag_approval query: $query\nmod_tag_approval args: ($operator,$approval,$term)\n";
415
    return Koha::Database->new()->schema()->resultset('TagsApproval')
474
	my $sth = C4::Context->dbh->prepare($query);
416
      ->search( { term => $term } )->update(
475
	$sth->execute($operator,$approval,$term);
417
        {
418
            approved_be   => $operator,
419
            approved      => $approval,
420
            date_approved => \'NOW()',
421
        }
422
      );
476
}
423
}
477
424
478
sub add_tag_index {
425
sub add_tag_index {
479
	my $term         = shift or return;
426
    my $term         = shift or return;
480
	my $biblionumber = shift or return;
427
    my $biblionumber = shift or return;
481
	my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?";
428
482
	my $sth = C4::Context->dbh->prepare($query);
429
    my $rs =
483
	$sth->execute($term,$biblionumber);
430
      Koha::Database->new()->schema()->resultset('TagsApproval');
484
	($sth->rows) and return increment_weight($term,$biblionumber);
431
485
	$query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)";
432
    my $count = $rs->count(
486
	$debug and print STDERR "add_tag_index query: $query\nadd_tag_index args: ($term,$biblionumber)\n";
433
        {
487
	$sth = C4::Context->dbh->prepare($query);
434
            term         => $term,
488
	$sth->execute($term,$biblionumber);
435
            biblionumber => $biblionumber,
489
	return $sth->rows;
436
        }
437
    );
438
439
    return increment_weight( $term, $biblionumber ) if $count;
440
441
    return $rs->create(
442
        {
443
            term         => $term,
444
            biblionumber => $biblionumber
445
        }
446
    );
490
}
447
}
491
448
492
sub get_tag {		# by tag_id
449
sub get_tag {    # by tag_id
493
	(@_) or return;
450
    my ($tag_id) = @_;
494
    my $sth = C4::Context->dbh->prepare(TAG_SELECT . "WHERE tag_id = ?");
451
495
	$sth->execute(shift);
452
    return Koha::Database->new()->schema()->resultset('TagsAll')->find(
496
	return $sth->fetchrow_hashref;
453
        $tag_id,
454
        {
455
            result_class => 'DBIx::Class::ResultClass::HashRefInflator',
456
        }
457
    );
497
}
458
}
498
459
499
sub increment_weights {
460
sub increment_weights {
500
	increment_weight(@_);
461
	increment_weight(@_);
501
	increment_weight_total(shift);
462
	increment_weight_total(shift);
502
}
463
}
464
503
sub decrement_weights {
465
sub decrement_weights {
504
	decrement_weight(@_);
466
	decrement_weight(@_);
505
	decrement_weight_total(shift);
467
	decrement_weight_total(shift);
506
}
468
}
469
507
sub increment_weight_total {
470
sub increment_weight_total {
508
	_set_weight_total('weight_total+1',shift);
471
	_set_weight_total('weight_total+1',shift);
509
}
472
}
473
510
sub increment_weight {
474
sub increment_weight {
511
	_set_weight('weight+1',shift,shift);
475
	_set_weight('weight+1',shift,shift);
512
}
476
}
477
513
sub decrement_weight_total {
478
sub decrement_weight_total {
514
	_set_weight_total('weight_total-1',shift);
479
	_set_weight_total('weight_total-1',shift);
515
}
480
}
481
516
sub decrement_weight {
482
sub decrement_weight {
517
	_set_weight('weight-1',shift,shift);
483
	_set_weight('weight-1',shift,shift);
518
}
484
}
485
519
sub _set_weight_total {
486
sub _set_weight_total {
520
	my $sth = C4::Context->dbh->prepare("
487
    my ( $weight_total, $term ) = @_;
521
	UPDATE tags_approval
488
522
	SET    weight_total=" . (shift) . "
489
    $term =
523
	WHERE  term=?
490
      Koha::Database->new()->schema()->resultset('TagsApproval')->find($term);
524
	");						# note: CANNOT use "?" for weight_total (see the args above).
491
525
	$sth->execute(shift);	# just the term
492
    $term->update( { weight_total => $weight_total } ) if $term;
526
}
493
}
494
527
sub _set_weight {
495
sub _set_weight {
528
	my $dbh = C4::Context->dbh;
496
    my ( $weight, $term, $biblionumber ) = @_;
529
	my $sth = $dbh->prepare("
497
530
	UPDATE tags_index
498
    my $t =
531
	SET    weight=" . (shift) . "
499
      Koha::Database->new()->schema()->resultset('TagsIndex')
532
	WHERE  term=?
500
      ->find( { term => $term, biblionumber => $biblionumber } );
533
	AND    biblionumber=?
501
534
	");
502
    $term->update( { weight => $weight } ) if $t;
535
	$sth->execute(@_);
536
}
503
}
537
504
538
sub add_tag {	# biblionumber,term,[borrowernumber,approvernumber]
505
sub add_tag {    # biblionumber,term,[borrowernumber,approvernumber]
539
	my $biblionumber = shift or return;
506
    my $biblionumber = shift or return;
540
	my $term         = shift or return;
507
    my $term         = shift or return;
541
	my $borrowernumber = (@_) ? shift : 0;		# the user, default to kohaadmin
508
    my $borrowernumber = (@_) ? shift : 0;    # the user, default to kohaadmin
542
	$term =~ s/^\s+//;
509
543
	$term =~ s/\s+$//;
510
    $term =~ s/^\s+//;
544
	($term) or return;	# must be more than whitespace
511
    $term =~ s/\s+$//;
545
	my $rows = get_tag_rows({biblionumber=>$biblionumber, borrowernumber=>$borrowernumber, term=>$term, limit=>1});
512
    ($term) or return;                        # must be more than whitespace
546
	my $query = "INSERT INTO tags_all
513
547
	(borrowernumber,biblionumber,term,date_created)
514
    my $rows = get_tag_rows(
548
	VALUES (?,?,?,NOW())";
515
        {
549
	$debug and print STDERR "add_tag query: $query\n",
516
            biblionumber   => $biblionumber,
550
							"add_tag query args: ($borrowernumber,$biblionumber,$term)\n";
517
            borrowernumber => $borrowernumber,
551
	if (scalar @$rows) {
518
            term           => $term,
552
		$debug and carp "Duplicate tag detected.  Tag not added.";	
519
            limit          => 1
553
		return;
520
        }
554
	}
521
    );
555
	# add to tags_all regardless of approaval
522
556
	my $sth = C4::Context->dbh->prepare($query);
523
    if ( scalar @$rows ) {
557
	$sth->execute($borrowernumber,$biblionumber,$term);
524
        $debug and carp "Duplicate tag detected. Tag not added.";
558
525
        return;
559
	# then 
526
    }
560
	if (scalar @_) { 	# if arg remains, it is the borrowernumber of the approver: tag is pre-approved.
527
561
		my $approver = shift;
528
    my $t = Koha::Database->new()->schema()->resultset('TagsAll')->create(
562
		$debug and print STDERR "term '$term' pre-approved by borrower #$approver\n";
529
        {
563
		add_tag_approval($term,$approver,1);
530
            borrowernumber => $borrowernumber,
564
		add_tag_index($term,$biblionumber,$approver);
531
            biblionumber   => $biblionumber,
565
	} elsif (is_approved($term) >= 1) {
532
            term           => $term,
566
		$debug and print STDERR "term '$term' approved by whitelist\n";
533
            date_created   => \'NOW()',
567
		add_tag_approval($term,0,1);
534
        }
568
		add_tag_index($term,$biblionumber,1);
535
    );
569
	} else {
536
570
		$debug and print STDERR "term '$term' NOT approved (yet)\n";
537
571
		add_tag_approval($term);
538
    if ( scalar @_ )
572
		add_tag_index($term,$biblionumber);
539
    { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved.
573
	}
540
        my $approver = shift;
541
        $debug
542
          and print STDERR "term '$term' pre-approved by borrower #$approver\n";
543
        add_tag_approval( $term, $approver, 1 );
544
        add_tag_index( $term, $biblionumber, $approver );
545
    }
546
    elsif ( is_approved($term) >= 1 ) {
547
        $debug and print STDERR "term '$term' approved by whitelist\n";
548
        add_tag_approval( $term, 0, 1 );
549
        add_tag_index( $term, $biblionumber, 1 );
550
    }
551
    else {
552
        $debug and print STDERR "term '$term' NOT approved (yet)\n";
553
        add_tag_approval($term);
554
        add_tag_index( $term, $biblionumber );
555
    }
574
}
556
}
575
557
576
# This takes a set of tags, as returned by C<get_approval_rows> and divides
558
# This takes a set of tags, as returned by C<get_approval_rows> and divides
577
- 

Return to bug 12610