Attachment #167766 for bug #36027

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




# along with Koha; if not, see <http://www.gnu.org/licenses>.

use Modern::Perl;

use Koha::Script;
use Koha::AuthorisedValues;
use Koha::Authorities;

use Koha::ItemTypes;
use Koha::Patrons;
use C4::Biblio qw( GetMarcFromKohaField );
use Getopt::Long;
use Pod::Usage;

our %options = (
    'check-branch'    => 0,
    'check-auth'      => 0,
    'check-status'    => 0,
    'check-framework' => 0,
    'check-title'     => 0,
    'check-age'       => 0,
    'check-loop'      => 0,
    'skip-branch'     => 0,
    'skip-auth'       => 0,
    'skip-status'     => 0,
    'skip-framework'  => 0,
    'skip-title'      => 0,
    'skip-age'        => 0,
    'skip-loop'       => 0,
    'help'            => 0,
);

# Parse command line options
GetOptions(
    'check-branch'    => sub { $options{'check-branch'}    = 1 },
    'check-auth'      => sub { $options{'check-auth'}      = 1 },
    'check-status'    => sub { $options{'check-status'}    = 1 },
    'check-framework' => sub { $options{'check-framework'} = 1 },
    'check-title'     => sub { $options{'check-title'}     = 1 },
    'check-age'       => sub { $options{'check-age'}       = 1 },
    'check-loop'      => sub { $options{'check-loop'}      = 1 },
    'skip-branch'     => sub { $options{'skip-branch'}     = 1 },
    'skip-auth'       => sub { $options{'skip-auth'}       = 1 },
    'skip-status'     => sub { $options{'skip-status'}     = 1 },
    'skip-framework'  => sub { $options{'skip-framework'}  = 1 },
    'skip-title'      => sub { $options{'skip-title'}      = 1 },
    'skip-age'        => sub { $options{'skip-age'}        = 1 },
    'skip-loop'       => sub { $options{'skip-loop'}       = 1 },
    'help'            => sub { $options{'help'}            = 1; },
) or pod2usage(2);    # Print usage if invalid options are provided

# Call handle_invalid_options subroutine if invalid options are provided
Getopt::Long::Configure('pass_through');
GetOptions(
    '<>' => sub {
        my ($opt_name) = @_;
        handle_invalid_options($opt_name);
    }
);

# Print usage and exit if --help flag is provided
pod2usage(1) if $options{'help'};

# Set skip options based on check options
set_skip_options();
# Set all check options to 1 if none are provided, unless specified skip options
set_all_check_options_if_none_provided();
# Run checks based on options
CheckItemsBranch()       if $options{'check-branch'}    && !$options{'skip-branch'};
CheckItemsAuthHeader()   if $options{'check-auth'}      && !$options{'skip-auth'};
CheckItemsStatus()       if $options{'check-status'}    && !$options{'skip-status'};
CheckItemsFramework()    if $options{'check-framework'} && !$options{'skip-framework'};
CheckItemsTitle()        if $options{'check-title'}     && !$options{'skip-title'};
CheckAgeForCategory()    if $options{'check-age'}       && !$options{'skip-age'};
CheckRelationshipsLoop() if $options{'check-loop'}      && !$options{'skip-loop'};

# Handle invalid options
sub handle_invalid_options {
    my ($opt_name) = @_;
    print "Invalid option provided: $opt_name\n";
    pod2usage(2);
}

# Set skip options based on check options
sub set_skip_options {
    # If at least one check option is provided, set all skip options to 0
    my $has_check_option = grep { $options{$_} } grep { /^check-/ } keys %options;
    if ($has_check_option) {
        # if a least one skip option is provided, print a warning
        my $has_skip_option = grep { $options{$_} == 1 } grep { /^skip-/ } keys %options;
        if ($has_skip_option) {
            print("Warning : skip options are ignored when check options are provided\n")
        }
        # Set all skip options to 0
        foreach my $key (keys %options) {
            if ($key =~ /^skip-/) {
                $options{$key} = 0;
            }
        }
    }
    return %options;
}

# Set all check options to 1 if none are provided, considering skip options
sub set_all_check_options_if_none_provided {
    my $any_check_option_provided = grep { $options{$_} } grep { /^check-/ } keys %options;
    if (!$any_check_option_provided) {
        foreach my $key (keys %options) {
            if ($key =~ /^check-/) {
                my $skip_key = $key;
                $skip_key =~ s/^check-/skip-/;
                # Set check option to 1 unless the corresponding skip option indicated
                $options{$key} = 1 unless $options{$skip_key};
            }
        }
    }
}

sub CheckItemsBranch {
    my $items = Koha::Items->search({ -or => { homebranch => undef, holdingbranch => undef }});
    if ( $items->count ) { new_section("Not defined items.homebranch and/or items.holdingbranch")}
    while ( my $item = $items->next ) {
        if ( not $item->homebranch and not $item->holdingbranch ) {
            new_item(sprintf "Item with itemnumber=%s and biblionumber=%s does not have homebranch and holdingbranch defined",  $item->itemnumber, $item->biblionumber);
            } elsif ( not $item->homebranch ) {
            new_item(sprintf "Item with itemnumber=%s and biblionumber=%s does not have homebranch defined", $item->itemnumber, $item->biblionumber);
            } else {
            new_item(sprintf "Item with itemnumber=%s and biblionumber=%s does not have holdingbranch defined", $item->itemnumber, $item->biblionumber);
        }
    }
    if ( $items->count ) { new_hint("Edit these items and set valid homebranch and/or holdingbranch")}
}

sub CheckItemsAuthHeader {
    # No join possible, FK is missing at DB level
    my @auth_types = Koha::Authority::Types->search->get_column('authtypecode');
    my $authorities = Koha::Authorities->search({authtypecode => { 'not in' => \@auth_types } });

    while ( my $authority = $authorities->next ) {
        new_item(sprintf "Authority with authid=%s does not have a code defined (%s)", $authority->authid, $authority->authtypecode);
    }
    if ( $authorities->count ) {new_hint("Go to 'Home -> Administration -> Authority types' to define them")};
    };

    sub CheckItemsStatus {
        if ( C4::Context->preference('item-level_itypes') ) {
            my $items_without_itype = Koha::Items->search( { -or => [itype => undef,itype => ''] } );
            if ( $items_without_itype->count ) {
                new_section("Items do not have itype defined");
                while ( my $item = $items_without_itype->next ) {
                    if (defined $item->biblioitem->itemtype && $item->biblioitem->itemtype ne '' ) {
                        new_item(
                        sprintf "Item with itemnumber=%s and biblionumber = %s does not have a itype value, biblio's item type will be used (%s)",
                        $item->itemnumber, $item->biblioitem->biblionumber, $item->biblioitem->itemtype
                        );
                        } else {
                        new_item(
                        sprintf "Item with itemnumber=%s and biblionumber = %s does not have a itype value, additionally no item type defined for biblionumber=%s",
                        $item->itemnumber, $item->biblioitem->biblionumber, $item->biblioitem->biblionumber
                        );
                    }
                }
                new_hint("The system preference item-level_itypes expects item types to be defined at item level");
            }
        }else{
            my $biblioitems_without_itemtype = Koha::Biblioitems->search( { itemtype => undef } );
            if ( $biblioitems_without_itemtype->count ) {
                new_section("Biblioitems do not have itemtype defined");
                while ( my $biblioitem = $biblioitems_without_itemtype->next ) {
                    new_item(
            new_section("Biblioitems do not have itemtype defined");
            while ( my $biblioitem = $biblioitems_without_itemtype->next ) {
                new_item(
                    sprintf "Biblioitem with biblioitemnumber=%s does not have a itemtype value",
                    $biblioitem->biblioitemnumber
                    );
                }
                new_hint("The system preference item-level_itypes expects item types to be defined at biblio level");
            }
            new_hint("The system preference item-level_itypes expects item types to be defined at biblio level");
        }
    }

        my @itemtypes = Koha::ItemTypes->search->get_column('itemtype');
        if ( C4::Context->preference('item-level_itypes') ) {
            my $items_with_invalid_itype = Koha::Items->search( { -and => [itype => { not_in => \@itemtypes }, itype => { '!=' => '' }] } );
            if ( $items_with_invalid_itype->count ) {
                new_section("Items have invalid itype defined");
                while ( my $item = $items_with_invalid_itype->next ) {
                    new_item(
                    sprintf "Item with itemnumber=%s, biblionumber=%s does not have a valid itype value (%s)",
                    $item->itemnumber, $item->biblionumber, $item->itype
                    );
                }
                new_hint("The items must have a itype value that is defined in the item types of Koha (Home › Administration › Item types administration)");
            }
        }else{
            my $biblioitems_with_invalid_itemtype = Koha::Biblioitems->search(
    }
    else {
        my $biblioitems_with_invalid_itemtype = Koha::Biblioitems->search(
            { itemtype => { not_in => \@itemtypes } }
            );
            if ( $biblioitems_with_invalid_itemtype->count ) {
                new_section("Biblioitems do not have itemtype defined");
                while ( my $biblioitem = $biblioitems_with_invalid_itemtype->next ) {
                    new_item(
                    sprintf "Biblioitem with biblioitemnumber=%s does not have a valid itemtype value",
                    $biblioitem->biblioitemnumber
                    );
                }
                new_hint("The biblioitems must have a itemtype value that is defined in the item types of Koha (Home › Administration › Item types administration)");
            }
            new_hint("The biblioitems must have a itemtype value that is defined in the item types of Koha (Home › Administration › Item types administration)");
        }
    }

        my ( @decoding_errors, @ids_not_in_marc );
        my $biblios = Koha::Biblios->search;
        my ( $biblio_tag,     $biblio_subfield )     = C4::Biblio::GetMarcFromKohaField( "biblio.biblionumber" );
        my ( $biblioitem_tag, $biblioitem_subfield ) = C4::Biblio::GetMarcFromKohaField( "biblioitems.biblioitemnumber" );
        while ( my $biblio = $biblios->next ) {
            my $record = eval{$biblio->metadata->record;};
            if ($@) {
                push @decoding_errors, $@;
                next;
            }
            my ( $biblionumber, $biblioitemnumber );
            if ( $biblio_tag < 10 ) {
                my $biblio_control_field = $record->field($biblio_tag);
                $biblionumber = $biblio_control_field->data if $biblio_control_field;
                } else {
                $biblionumber = $record->subfield( $biblio_tag, $biblio_subfield );
            }
            if ( $biblioitem_tag < 10 ) {
                my $biblioitem_control_field = $record->field($biblioitem_tag);
                $biblioitemnumber = $biblioitem_control_field->data if $biblioitem_control_field;
                } else {
                $biblioitemnumber = $record->subfield( $biblioitem_tag, $biblioitem_subfield );
            }
            if ( $biblionumber != $biblio->biblionumber ) {
                push @ids_not_in_marc,
                {
                biblionumber         => $biblio->biblionumber,
                biblionumber_in_marc => $biblionumber,
                };
            }
            if ( $biblioitemnumber != $biblio->biblioitem->biblioitemnumber ) {
                push @ids_not_in_marc,
                {
                biblionumber     => $biblio->biblionumber,
                biblioitemnumber => $biblio->biblioitem->biblioitemnumber,
                biblioitemnumber_in_marc => $biblionumber,
                };
            }
        }
        if ( @decoding_errors ) {
            new_section("Bibliographic records have invalid MARCXML");
            new_item($_) for @decoding_errors;
            new_hint("The bibliographic records must have a valid MARCXML or you will face encoding issues or wrong displays");
        }
        if (@ids_not_in_marc) {
            new_section("Bibliographic records have MARCXML without biblionumber or biblioitemnumber");
            for my $id (@ids_not_in_marc) {
                if ( exists $id->{biblioitemnumber} ) {
                    new_item(
                new_item(
                    sprintf(q{Biblionumber %s has biblioitemnumber '%s' but should be '%s' in %s$%s},
                    $id->{biblionumber},
                    $id->{biblioitemnumber},
                    $id->{biblioitemnumber_in_marc},
                    $biblioitem_tag,
                    $biblioitem_subfield,
                    )
                    );
                }else{
                    new_item(
                new_item(
                    sprintf(q{Biblionumber %s has '%s' in %s$%s},
                    $id->{biblionumber},
                    $id->{biblionumber_in_marc},
                    $biblio_tag,
                    $biblio_subfield,
                    )
                    );
                }
            }
            new_hint("The bibliographic records must have the biblionumber and biblioitemnumber in MARCXML");
        }
        new_hint("The bibliographic records must have the biblionumber and biblioitemnumber in MARCXML");
    }
}

    sub CheckItemsFramework {
        my @framework_codes = Koha::BiblioFrameworks->search()->get_column('frameworkcode');
        push @framework_codes,""; # The default is not stored in frameworks, we need to force it

        my $invalid_av_per_framework = {};
        foreach my $frameworkcode ( @framework_codes ) {
            # We are only checking fields that are mapped to DB fields
            my $msss = Koha::MarcSubfieldStructures->search({
            frameworkcode => $frameworkcode,
            authorised_value => {
            '!=' => [ -and => ( undef, '' ) ]
            },
            kohafield => {
            '!=' => [ -and => ( undef, '' ) ]
        }
        });
        while ( my $mss = $msss->next ) {
            my $kohafield = $mss->kohafield;
            my $av = $mss->authorised_value;
            next if grep {$_ eq $av} qw( branches itemtypes cn_source ); # internal categories
            
            my $avs = Koha::AuthorisedValues->search_by_koha_field(
            {
            frameworkcode => $frameworkcode,
            kohafield     => $kohafield,
        }
        );
        my $tmp_kohafield = $kohafield;
        if ( $tmp_kohafield =~ /^biblioitems/ ) {
            $tmp_kohafield =~ s|biblioitems|biblioitem|;
            } else {
            $tmp_kohafield =~ s|items|me|;
        }
        # replace items.attr with me.attr

        # We are only checking biblios with items
        my $items = Koha::Items->search(
        {
        $tmp_kohafield =>
        {
        -not_in => [ $avs->get_column('authorised_value'), '' ],
        '!='    => undef,
        },
        'biblio.frameworkcode' => $frameworkcode
        },
        { join => [ 'biblioitem', 'biblio' ] }
        );
        if ( $items->count ) {
            $invalid_av_per_framework->{ $frameworkcode }->{$av} =
            { items => $items, kohafield => $kohafield };
            }
        }
    }
}
if (%$invalid_av_per_framework) {
    new_section('Wrong values linked to authorised values');
    for my $frameworkcode ( keys %$invalid_av_per_framework ) {
        while ( my ( $av_category, $v ) = each %{$invalid_av_per_framework->{$frameworkcode}} ) {
            my $items     = $v->{items};
            my $kohafield = $v->{kohafield};
            my ( $table, $column ) = split '\.', $kohafield;
            my $output;
            while ( my $i = $items->next ) {
                my $value = $table eq 'items'
                ? $i->$column
                : $table eq 'biblio'
                ? $i->biblio->$column
                : $i->biblioitem->$column;
                $output .= " {" . $i->itemnumber . " and " . $i->biblioitem->biblionumber. " => " . $value . "}\n";
                new_item(
                    sprintf(
                        "The Framework *%s* is using the authorised value's category *%s*, "
                        . "but the following %s do not have a value defined ({itemnumber => value }):\n%s",
                        $frameworkcode, $av_category, $kohafield, $output
                    )
                );
            }
            new_item(
            sprintf(
            "The Framework *%s* is using the authorised value's category *%s*, "
            . "but the following %s do not have a value defined ({itemnumber and biblionumber => value }):\n%s",
            $frameworkcode, $av_category, $kohafield, $output
            )
            );
        }
    }
}
}

sub CheckItemsTitle {
    my $biblios = Koha::Biblios->search({
        -or => [
            title => '',

    }
}

sub CheckAgeForCategory {
    my $aging_patrons = Koha::Patrons->search(
        {
            -not => {

    }
}

sub CheckRelationshipsLoop {
    my @loop_borrowers_relationships;
    my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
    my @guarantee_ids = Koha::Patron::Relationships->_resultset->get_column('guarantee_id')->all();

    foreach my $guarantor_id (@guarantor_ids) {
        foreach my $guarantee_id (@guarantee_ids) {
            if ( $guarantor_id == $guarantee_id ) {

                my $relation_guarantor_id;
                my $relation_guarantee_id;
                my $size_list;
                my $tmp_garantor_id = $guarantor_id;
                my @guarantor_ids;

                do {
                    my @relationship_for_go = Koha::Patron::Relationships->search(
                        {
                            -or => [
                                'guarantor_id' => { '=', $tmp_garantor_id },
                            ]
                        },
                    )->as_list;
                    $size_list = scalar @relationship_for_go;

                    foreach my $relation (@relationship_for_go) {
                        $relation_guarantor_id = $relation->guarantor_id;
                        unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
                            push @guarantor_ids, $relation_guarantor_id;
                        }
                        $relation_guarantee_id = $relation->guarantee_id;

                        my @relationship_for_go = Koha::Patron::Relationships->search(
                            {
                                -or => [
                                    'guarantor_id' => { '=', $relation_guarantee_id },
                                ]
                            },
                        )->as_list;
                        $size_list = scalar @relationship_for_go;

                        if ( $guarantor_id == $relation_guarantee_id ) {
                            last;
                        }

                        foreach my $relation (@relationship_for_go) {
                            $relation_guarantor_id = $relation->guarantor_id;
                            unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
                                push @guarantor_ids, $relation_guarantor_id;
                            }
                            $relation_guarantee_id = $relation->guarantee_id;

                            if ( $guarantor_id == $relation_guarantee_id ) {
                                last;
                            }
                        }
                        if ( $guarantor_id == $relation_guarantee_id ) {
                            last;
                        }
                    }

                    $tmp_garantor_id = $relation_guarantee_id;
                } while ( $guarantor_id != $relation_guarantee_id && $size_list > 0 );

                if ( $guarantor_id == $relation_guarantee_id ) {
                    unless ( grep { join( "", sort @$_ ) eq join( "", sort @guarantor_ids ) }
                        @loop_borrowers_relationships )
                    {
                        push @loop_borrowers_relationships, \@guarantor_ids;
                    }
                }
            }
        }
    }

    if ( scalar @loop_borrowers_relationships > 0 ) {
        new_section("The list of guarantors who are also guarantee.");
        my $count = 0;
        foreach my $table (@loop_borrowers_relationships) {
            $count++;
            print "Loop $count, borrowers id  : ";
            foreach my $borrower_id (@$table) {
                print "$borrower_id , ";
            }
            print "\n";
        }
        new_hint("Relationships that form guarantor loops must be deleted");
    }
}

sub new_section {
    my ( $name ) = @_;
    say "\n== $name ==";

    say "=> $name";
}

=head1 SYNOPSIS

search_for_data_inconsistencies.pl [options]

=head2 DESCRIPTION

Catch data inconsistencies in Koha database:

    * Items with undefined homebranch and/or holdingbranch
    * Authorities with undefined authtypecodes/authority types
    * Item types:
        * if item types are defined at item level (item-level_itypes=specific item),
        then items.itype must be set else biblioitems.itemtype must be set
        * Item types defined in items or biblioitems must be defined in the itemtypes table
    * Bibliographic records without a title
    * Invalid MARCXML in bibliographic records
    * Patrons with invalid category types due to lower and upper age limits
    * Relationships that form guarantor loops

=head2 OPTIONS

  --check-branch     Check for items without home or holding library
  --check-auth       Check for authority records with invalid authority type
  --check-status     Check for bibliographic records and items without an item type or with an invalid item type
  --check-framework  Check for invalid values in fields where the framework limits to an authorized value category
  --check-title      Check for bibliographic records without a title
  --check-age        Check for patrons with invalid age for category
  --check-loop       Check for relationships that form guarantor loops
  --skip-branch      Skip checking for items without home or holding library
  --skip-auth        Skip checking for authority records with invalid authority type
  --skip-status      Skip checking for bibliographic records and items without an item type or with an invalid item type
  --skip-framework   Skip checking for invalid values in fields where the framework limits to an authorized value category
  --skip-title       Skip checking for bibliographic records without a title
  --skip-age         Skip checking for patrons with invalid age for category
  --skip-loop        Skip checking for relationships that form guarantor loops
  --help             Print usage information

Note: If no options are provided, all tests will be run.
* Authorities with undefined authtypecodes/authority types
* Item types:
  * if item types are defined at item level (item-level_itypes=specific item),
    then items.itype must be set else biblioitems.itemtype must be set
  * Item types defined in items or biblioitems must be defined in the itemtypes table
* Invalid MARCXML in bibliographic records
* Patrons with invalid category types due to lower and upper age limits

=cut
- 

Return to bug 36027