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

(-)a/C4/Tags.pm (-185 / +167 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')->search(
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
    )->count();
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')->search(
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
    )->count();
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 =
449
	my $operator = shift || 0;
395
      Koha::Database->new()->schema()->resultset('TagsApproval')->search(
450
	my $approval = (@_ ? shift : 0);	# default is unapproved
396
        {
451
	my @exe_args = ($term);		# all 3 queries will use this argument
397
            term => $term
452
	if ($operator) {
398
        }
453
		$query = "INSERT INTO tags_approval (term,approved_by,approved,date_approved) VALUES (?,?,?,NOW())";
399
      );
454
		push @exe_args, $operator, $approval;
400
    return increment_weight_total($term) if $rs->count();
455
	} elsif ($approval) {
401
456
		$query = "INSERT INTO tags_approval (term,approved,date_approved) VALUES (?,?,NOW())";
402
    return $rs->create(
457
		push @exe_args, $approval;
403
        {
458
	} else {
404
            term          => $term,
459
		$query = "INSERT INTO tags_approval (term,date_approved) VALUES (?,NOW())";
405
            approved      => $approval,
460
	}
406
            approved_by   => $operator,
461
	$debug and print STDERR "add_tag_approval query: $query\nadd_tag_approval args: (" . join(", ", @exe_args) . ")\n";
407
            date_approved => \'NOW()',
462
	$sth = C4::Context->dbh->prepare($query);
408
        }
463
	$sth->execute(@exe_args);
409
    );
464
	return $sth->rows;
465
}
410
}
466
411
467
sub mod_tag_approval {
412
sub mod_tag_approval {
468
	my $operator = shift;
413
	my $operator = shift;
469
	defined $operator or return; # have to test defined to allow =0 (kohaadmin)
414
	defined $operator or return; # have to test defined to allow = 0 (kohaadmin)
470
	my $term     = shift or return;
415
	my $term     = shift or return;
471
	my $approval = (scalar @_ ? shift : 1);	# default is to approve
416
	my $approval = (scalar @_ ? shift : 1);	# default is to approve
472
	my $query = "UPDATE tags_approval SET approved_by=?, approved=?, date_approved=NOW() WHERE term = ?";
417
473
	$debug and print STDERR "mod_tag_approval query: $query\nmod_tag_approval args: ($operator,$approval,$term)\n";
418
    return Koha::Database->new()->schema()->resultset('TagsApproval')
474
	my $sth = C4::Context->dbh->prepare($query);
419
      ->search( { term => $term } )->update(
475
	$sth->execute($operator,$approval,$term);
420
        {
421
            approved_be   => $operator,
422
            approved      => $approval,
423
            date_approved => \'NOW()',
424
        }
425
      );
476
}
426
}
477
427
478
sub add_tag_index {
428
sub add_tag_index {
479
	my $term         = shift or return;
429
    my $term         = shift or return;
480
	my $biblionumber = shift or return;
430
    my $biblionumber = shift or return;
481
	my $query = "SELECT * FROM tags_index WHERE term = ? AND biblionumber = ?";
431
482
	my $sth = C4::Context->dbh->prepare($query);
432
    my $rs =
483
	$sth->execute($term,$biblionumber);
433
      Koha::Database->new()->schema()->resultset('TagsApproval')->search(
484
	($sth->rows) and return increment_weight($term,$biblionumber);
434
        {
485
	$query = "INSERT INTO tags_index (term,biblionumber) VALUES (?,?)";
435
            term         => $term,
486
	$debug and print STDERR "add_tag_index query: $query\nadd_tag_index args: ($term,$biblionumber)\n";
436
            biblionumber => $biblionumber,
487
	$sth = C4::Context->dbh->prepare($query);
437
        }
488
	$sth->execute($term,$biblionumber);
438
      );
489
	return $sth->rows;
439
440
    return increment_weight( $term, $biblionumber ) if $rs->count();
441
442
    return $rs->create(
443
        {
444
            term         => $term,
445
            biblionumber => $biblionumber
446
        }
447
    );
490
}
448
}
491
449
492
sub get_tag {		# by tag_id
450
sub get_tag {    # by tag_id
493
	(@_) or return;
451
    my ($tag_id) = @_;
494
    my $sth = C4::Context->dbh->prepare(TAG_SELECT . "WHERE tag_id = ?");
452
495
	$sth->execute(shift);
453
    return Koha::Database->new()->schema()->resultset('TagsAll')->find(
496
	return $sth->fetchrow_hashref;
454
        $tag_id,
455
        {
456
            result_class => 'DBIx::Class::ResultClass::HashRefInflator',
457
        }
458
    );
497
}
459
}
498
460
499
sub increment_weights {
461
sub increment_weights {
500
	increment_weight(@_);
462
	increment_weight(@_);
501
	increment_weight_total(shift);
463
	increment_weight_total(shift);
502
}
464
}
465
503
sub decrement_weights {
466
sub decrement_weights {
504
	decrement_weight(@_);
467
	decrement_weight(@_);
505
	decrement_weight_total(shift);
468
	decrement_weight_total(shift);
506
}
469
}
470
507
sub increment_weight_total {
471
sub increment_weight_total {
508
	_set_weight_total('weight_total+1',shift);
472
	_set_weight_total('weight_total+1',shift);
509
}
473
}
474
510
sub increment_weight {
475
sub increment_weight {
511
	_set_weight('weight+1',shift,shift);
476
	_set_weight('weight+1',shift,shift);
512
}
477
}
478
513
sub decrement_weight_total {
479
sub decrement_weight_total {
514
	_set_weight_total('weight_total-1',shift);
480
	_set_weight_total('weight_total-1',shift);
515
}
481
}
482
516
sub decrement_weight {
483
sub decrement_weight {
517
	_set_weight('weight-1',shift,shift);
484
	_set_weight('weight-1',shift,shift);
518
}
485
}
486
519
sub _set_weight_total {
487
sub _set_weight_total {
520
	my $sth = C4::Context->dbh->prepare("
488
    my ( $weight_total, $term ) = @_;
521
	UPDATE tags_approval
489
522
	SET    weight_total=" . (shift) . "
490
    $term =
523
	WHERE  term=?
491
      Koha::Database->new()->schema()->resultset('TagsApproval')->find($term);
524
	");						# note: CANNOT use "?" for weight_total (see the args above).
492
525
	$sth->execute(shift);	# just the term
493
    $term->update( { weight_total => $weight_total } ) if $term;
526
}
494
}
495
527
sub _set_weight {
496
sub _set_weight {
528
	my $dbh = C4::Context->dbh;
497
    my ( $weight, $term, $biblionumber ) = @_;
529
	my $sth = $dbh->prepare("
498
530
	UPDATE tags_index
499
    my $t =
531
	SET    weight=" . (shift) . "
500
      Koha::Database->new()->schema()->resultset('TagsIndex')
532
	WHERE  term=?
501
      ->find( { term => $term, biblionumber => $biblionumber } );
533
	AND    biblionumber=?
502
534
	");
503
    $term->update( { weight => $weight } ) if $t;
535
	$sth->execute(@_);
536
}
504
}
537
505
538
sub add_tag {	# biblionumber,term,[borrowernumber,approvernumber]
506
sub add_tag {    # biblionumber,term,[borrowernumber,approvernumber]
539
	my $biblionumber = shift or return;
507
    my $biblionumber = shift or return;
540
	my $term         = shift or return;
508
    my $term         = shift or return;
541
	my $borrowernumber = (@_) ? shift : 0;		# the user, default to kohaadmin
509
    my $borrowernumber = (@_) ? shift : 0;    # the user, default to kohaadmin
542
	$term =~ s/^\s+//;
510
543
	$term =~ s/\s+$//;
511
    $term =~ s/^\s+//;
544
	($term) or return;	# must be more than whitespace
512
    $term =~ s/\s+$//;
545
	my $rows = get_tag_rows({biblionumber=>$biblionumber, borrowernumber=>$borrowernumber, term=>$term, limit=>1});
513
    ($term) or return;                        # must be more than whitespace
546
	my $query = "INSERT INTO tags_all
514
547
	(borrowernumber,biblionumber,term,date_created)
515
    my $rows = get_tag_rows(
548
	VALUES (?,?,?,NOW())";
516
        {
549
	$debug and print STDERR "add_tag query: $query\n",
517
            biblionumber   => $biblionumber,
550
							"add_tag query args: ($borrowernumber,$biblionumber,$term)\n";
518
            borrowernumber => $borrowernumber,
551
	if (scalar @$rows) {
519
            term           => $term,
552
		$debug and carp "Duplicate tag detected.  Tag not added.";	
520
            limit          => 1
553
		return;
521
        }
554
	}
522
    );
555
	# add to tags_all regardless of approaval
523
556
	my $sth = C4::Context->dbh->prepare($query);
524
    if ( scalar @$rows ) {
557
	$sth->execute($borrowernumber,$biblionumber,$term);
525
        $debug and carp "Duplicate tag detected. Tag not added.";
558
526
        return;
559
	# then 
527
    }
560
	if (scalar @_) { 	# if arg remains, it is the borrowernumber of the approver: tag is pre-approved.
528
561
		my $approver = shift;
529
    my $t = Koha::Database->new()->schema()->resultset('TagsAll')->create(
562
		$debug and print STDERR "term '$term' pre-approved by borrower #$approver\n";
530
        {
563
		add_tag_approval($term,$approver,1);
531
            borrowernumber => $borrowernumber,
564
		add_tag_index($term,$biblionumber,$approver);
532
            biblionumber   => $biblionumber,
565
	} elsif (is_approved($term) >= 1) {
533
            term           => $term,
566
		$debug and print STDERR "term '$term' approved by whitelist\n";
534
            date_created   => \'NOW()',
567
		add_tag_approval($term,0,1);
535
        }
568
		add_tag_index($term,$biblionumber,1);
536
    );
569
	} else {
537
570
		$debug and print STDERR "term '$term' NOT approved (yet)\n";
538
571
		add_tag_approval($term);
539
    if ( scalar @_ )
572
		add_tag_index($term,$biblionumber);
540
    { # if arg remains, it is the borrowernumber of the approver: tag is pre-approved.
573
	}
541
        my $approver = shift;
542
        $debug
543
          and print STDERR "term '$term' pre-approved by borrower #$approver\n";
544
        add_tag_approval( $term, $approver, 1 );
545
        add_tag_index( $term, $biblionumber, $approver );
546
    }
547
    elsif ( is_approved($term) >= 1 ) {
548
        $debug and print STDERR "term '$term' approved by whitelist\n";
549
        add_tag_approval( $term, 0, 1 );
550
        add_tag_index( $term, $biblionumber, 1 );
551
    }
552
    else {
553
        $debug and print STDERR "term '$term' NOT approved (yet)\n";
554
        add_tag_approval($term);
555
        add_tag_index( $term, $biblionumber );
556
    }
574
}
557
}
575
558
576
# This takes a set of tags, as returned by C<get_approval_rows> and divides
559
# This takes a set of tags, as returned by C<get_approval_rows> and divides
577
- 

Return to bug 12610