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

(-)a/misc/maintenance/search_for_data_inconsistencies.pl (-35 / +289 lines)
Lines 36-47 our %options = ( Link Here
36
    'check-framework' => 0,
36
    'check-framework' => 0,
37
    'check-title'     => 0,
37
    'check-title'     => 0,
38
    'check-age'       => 0,
38
    'check-age'       => 0,
39
    'skip-branch'     => 1,
39
    'check-loop'      => 0,
40
    'skip-auth'       => 1,
40
    'skip-branch'     => 0,
41
    'skip-status'     => 1,
41
    'skip-auth'       => 0,
42
    'skip-framework'  => 1,
42
    'skip-status'     => 0,
43
    'skip-title'      => 1,
43
    'skip-framework'  => 0,
44
    'skip-age'        => 1,
44
    'skip-title'      => 0,
45
    'skip-age'        => 0,
46
    'skip-loop'       => 0,
45
    'help'            => 0,
47
    'help'            => 0,
46
);
48
);
47
49
Lines 53-64 GetOptions( Link Here
53
    'check-framework' => sub { $options{'check-framework'} = 1 },
55
    'check-framework' => sub { $options{'check-framework'} = 1 },
54
    'check-title'     => sub { $options{'check-title'}     = 1 },
56
    'check-title'     => sub { $options{'check-title'}     = 1 },
55
    'check-age'       => sub { $options{'check-age'}       = 1 },
57
    'check-age'       => sub { $options{'check-age'}       = 1 },
56
    'skip-branch'     => sub { $options{'skip-branch'}     = 0 },
58
    'check-loop'      => sub { $options{'check-loop'}      = 1 },
57
    'skip-auth'       => sub { $options{'skip-auth'}       = 0 },
59
    'skip-branch'     => sub { $options{'skip-branch'}     = 1 },
58
    'skip-status'     => sub { $options{'skip-status'}     = 0 },
60
    'skip-auth'       => sub { $options{'skip-auth'}       = 1 },
59
    'skip-framework'  => sub { $options{'skip-framework'}  = 0 },
61
    'skip-status'     => sub { $options{'skip-status'}     = 1 },
60
    'skip-title'      => sub { $options{'skip-title'}      = 0 },
62
    'skip-framework'  => sub { $options{'skip-framework'}  = 1 },
61
    'skip-age'        => sub { $options{'skip-age'}        = 0 },
63
    'skip-title'      => sub { $options{'skip-title'}      = 1 },
64
    'skip-age'        => sub { $options{'skip-age'}        = 1 },
65
    'skip-loop'       => sub { $options{'skip-loop'}       = 1 },
62
    'help'            => sub { $options{'help'}            = 1; },
66
    'help'            => sub { $options{'help'}            = 1; },
63
) or pod2usage(2);    # Print usage if invalid options are provided
67
) or pod2usage(2);    # Print usage if invalid options are provided
64
68
Lines 74-87 GetOptions( Link Here
74
# Print usage and exit if --help flag is provided
78
# Print usage and exit if --help flag is provided
75
pod2usage(1) if $options{'help'};
79
pod2usage(1) if $options{'help'};
76
80
77
# Run tests based on options
81
# Set skip options based on check options
78
set_skip_options();
82
set_skip_options();
79
CheckItemsBranch()     if $options{'check-branch'}    || $options{'skip-branch'};
83
80
CheckItemsAuthHeader() if $options{'check-auth'}      || $options{'skip-auth'};
84
# Set all check options to 1 if none are provided, unless specified skip options
81
CheckItemsStatus()     if $options{'check-status'}    || $options{'skip-status'};
85
set_all_check_options_if_none_provided();
82
CheckItemsFramework()  if $options{'check-framework'} || $options{'skip-framework'};
86
83
CheckItemsTitle()      if $options{'check-title'}     || $options{'skip-title'};
87
# Run checks based on options
84
CheckAgeForCategory()  if $options{'check-age'}       || $options{'skip-age'};
88
CheckItemsBranch()       if $options{'check-branch'}    && !$options{'skip-branch'};
89
CheckItemsAuthHeader()   if $options{'check-auth'}      && !$options{'skip-auth'};
90
CheckItemsStatus()       if $options{'check-status'}    && !$options{'skip-status'};
91
CheckItemsFramework()    if $options{'check-framework'} && !$options{'skip-framework'};
92
CheckItemsTitle()        if $options{'check-title'}     && !$options{'skip-title'};
93
CheckAgeForCategory()    if $options{'check-age'}       && !$options{'skip-age'};
94
CheckRelationshipsLoop() if $options{'check-loop'}      && !$options{'skip-loop'};
85
95
86
# Handle invalid options
96
# Handle invalid options
87
sub handle_invalid_options {
97
sub handle_invalid_options {
Lines 92-106 sub handle_invalid_options { Link Here
92
102
93
# Set skip options based on check options
103
# Set skip options based on check options
94
sub set_skip_options {
104
sub set_skip_options {
95
    my $has_check_option = grep { $options{$_} == 1 } grep { /^check-/ } keys %options;
105
106
    # If at least one check option is provided, set all skip options to 0
107
    my $has_check_option = grep { $options{$_} } grep { /^check-/ } keys %options;
96
    if ($has_check_option) {
108
    if ($has_check_option) {
109
110
        # if a least one skip option is provided, print a warning
111
        my $has_skip_option = grep { $options{$_} == 1 } grep { /^skip-/ } keys %options;
112
        if ($has_skip_option) {
113
            print("Warning : skip options are ignored when check options are provided\n");
114
        }
115
116
        # Set all skip options to 0
97
        foreach my $key ( keys %options ) {
117
        foreach my $key ( keys %options ) {
98
            $options{$key} = 0 if $key =~ /^skip-/;
118
            if ( $key =~ /^skip-/ ) {
119
                $options{$key} = 0;
120
            }
99
        }
121
        }
100
    }
122
    }
101
    return %options;
123
    return %options;
102
}
124
}
103
125
126
# Set all check options to 1 if none are provided, considering skip options
127
sub set_all_check_options_if_none_provided {
128
    my $any_check_option_provided = grep { $options{$_} } grep { /^check-/ } keys %options;
129
    if ( !$any_check_option_provided ) {
130
        foreach my $key ( keys %options ) {
131
            if ( $key =~ /^check-/ ) {
132
                my $skip_key = $key;
133
                $skip_key =~ s/^check-/skip-/;
134
135
                # Set check option to 1 unless the corresponding skip option indicated
136
                $options{$key} = 1 unless $options{$skip_key};
137
            }
138
        }
139
    }
140
}
141
104
sub CheckItemsBranch {
142
sub CheckItemsBranch {
105
    my $items = Koha::Items->search( { -or => { homebranch => undef, holdingbranch => undef } } );
143
    my $items = Koha::Items->search( { -or => { homebranch => undef, holdingbranch => undef } } );
106
    if ( $items->count ) { new_section("Not defined items.homebranch and/or items.holdingbranch") }
144
    if ( $items->count ) { new_section("Not defined items.homebranch and/or items.holdingbranch") }
Lines 479-484 sub CheckAgeForCategory { Link Here
479
}
517
}
480
518
481
{
519
{
520
    use Koha::Database;
521
    my $schema = Koha::Database->new->schema;
522
523
    # Loop over all the DBIx::Class classes
524
    for my $class ( sort values %{ $schema->{class_mappings} } ) {
525
526
        # Retrieve the resultset so we can access the columns info
527
        my $rs      = $schema->resultset($class);
528
        my $columns = $rs->result_source->columns_info;
529
530
        # Loop over the columns
531
        while ( my ( $column, $info ) = each %$columns ) {
532
533
            # Next if data type is not date/datetime/timestamp
534
            my $data_type = $info->{data_type};
535
            next unless grep { $data_type =~ m{^$_$} } qw( timestamp datetime date );
536
537
            # Count the invalid dates
538
            my $invalid_dates = $rs->search( { $column => '0000-00-00' } )->count;
539
540
            next unless $invalid_dates;
541
542
            new_section(
543
                "Column " . $rs->result_source->name . "." . $column . " contains $invalid_dates invalid dates" );
544
545
            if ( $invalid_dates > 0 ) {
546
                new_hint("You may change the dates with script: misc/cronjobs/fix_invalid_dates.pl (-c -v)");
547
            }
548
549
        }
550
    }
551
}
552
553
{
554
    my @loop_borrowers_relationships;
555
    my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
556
    my @guarantee_ids = Koha::Patron::Relationships->_resultset->get_column('guarantee_id')->all();
557
558
    foreach my $guarantor_id (@guarantor_ids) {
559
        foreach my $guarantee_id (@guarantee_ids) {
560
            if ( $guarantor_id == $guarantee_id ) {
561
562
                my $relation_guarantor_id;
563
                my $relation_guarantee_id;
564
                my $size_list;
565
                my $tmp_garantor_id = $guarantor_id;
566
                my @guarantor_ids;
567
568
                do {
569
                    my @relationship_for_go = Koha::Patron::Relationships->search(
570
                        {
571
                            -or => [
572
                                'guarantor_id' => { '=', $tmp_garantor_id },
573
                            ]
574
                        },
575
                    )->as_list;
576
                    $size_list = scalar @relationship_for_go;
577
578
                    foreach my $relation (@relationship_for_go) {
579
                        $relation_guarantor_id = $relation->guarantor_id;
580
                        unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
581
                            push @guarantor_ids, $relation_guarantor_id;
582
                        }
583
                        $relation_guarantee_id = $relation->guarantee_id;
584
585
                        my @relationship_for_go = Koha::Patron::Relationships->search(
586
                            {
587
                                -or => [
588
                                    'guarantor_id' => { '=', $relation_guarantee_id },
589
                                ]
590
                            },
591
                        )->as_list;
592
                        $size_list = scalar @relationship_for_go;
593
594
                        if ( $guarantor_id == $relation_guarantee_id ) {
595
                            last;
596
                        }
597
598
                        foreach my $relation (@relationship_for_go) {
599
                            $relation_guarantor_id = $relation->guarantor_id;
600
                            unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
601
                                push @guarantor_ids, $relation_guarantor_id;
602
                            }
603
                            $relation_guarantee_id = $relation->guarantee_id;
604
605
                            if ( $guarantor_id == $relation_guarantee_id ) {
606
                                last;
607
                            }
608
                        }
609
                        if ( $guarantor_id == $relation_guarantee_id ) {
610
                            last;
611
                        }
612
                    }
613
614
                    $tmp_garantor_id = $relation_guarantee_id;
615
                } while ( $guarantor_id != $relation_guarantee_id && $size_list > 0 );
616
617
                if ( $guarantor_id == $relation_guarantee_id ) {
618
                    unless ( grep { join( "", sort @$_ ) eq join( "", sort @guarantor_ids ) }
619
                        @loop_borrowers_relationships )
620
                    {
621
                        push @loop_borrowers_relationships, \@guarantor_ids;
622
                    }
623
                }
624
            }
625
        }
626
    }
627
628
    if ( scalar @loop_borrowers_relationships > 0 ) {
629
        new_section("The list of guarantors who are also guarantee.");
630
        my $count = 0;
631
        foreach my $table (@loop_borrowers_relationships) {
632
            $count++;
633
            print "Loop $count, borrowers id  : ";
634
            foreach my $borrower_id (@$table) {
635
                print "$borrower_id , ";
636
            }
637
            print "\n";
638
        }
639
        new_hint("Relationships that form guarantor loops must be deleted");
640
    }
641
}
642
643
sub CheckRelationshipsLoop {
644
    my @loop_borrowers_relationships;
645
    my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
646
    my @guarantee_ids = Koha::Patron::Relationships->_resultset->get_column('guarantee_id')->all();
647
648
    foreach my $guarantor_id (@guarantor_ids) {
649
        foreach my $guarantee_id (@guarantee_ids) {
650
            if ( $guarantor_id == $guarantee_id ) {
651
652
                my $relation_guarantor_id;
653
                my $relation_guarantee_id;
654
                my $size_list;
655
                my $tmp_garantor_id = $guarantor_id;
656
                my @guarantor_ids;
657
658
                do {
659
                    my @relationship_for_go = Koha::Patron::Relationships->search(
660
                        {
661
                            -or => [
662
                                'guarantor_id' => { '=', $tmp_garantor_id },
663
                            ]
664
                        },
665
                    )->as_list;
666
                    $size_list = scalar @relationship_for_go;
667
668
                    foreach my $relation (@relationship_for_go) {
669
                        $relation_guarantor_id = $relation->guarantor_id;
670
                        unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
671
                            push @guarantor_ids, $relation_guarantor_id;
672
                        }
673
                        $relation_guarantee_id = $relation->guarantee_id;
674
675
                        my @relationship_for_go = Koha::Patron::Relationships->search(
676
                            {
677
                                -or => [
678
                                    'guarantor_id' => { '=', $relation_guarantee_id },
679
                                ]
680
                            },
681
                        )->as_list;
682
                        $size_list = scalar @relationship_for_go;
683
684
                        if ( $guarantor_id == $relation_guarantee_id ) {
685
                            last;
686
                        }
687
688
                        foreach my $relation (@relationship_for_go) {
689
                            $relation_guarantor_id = $relation->guarantor_id;
690
                            unless ( grep { $_ == $relation_guarantor_id } @guarantor_ids ) {
691
                                push @guarantor_ids, $relation_guarantor_id;
692
                            }
693
                            $relation_guarantee_id = $relation->guarantee_id;
694
695
                            if ( $guarantor_id == $relation_guarantee_id ) {
696
                                last;
697
                            }
698
                        }
699
                        if ( $guarantor_id == $relation_guarantee_id ) {
700
                            last;
701
                        }
702
                    }
703
704
                    $tmp_garantor_id = $relation_guarantee_id;
705
                } while ( $guarantor_id != $relation_guarantee_id && $size_list > 0 );
706
707
                if ( $guarantor_id == $relation_guarantee_id ) {
708
                    unless ( grep { join( "", sort @$_ ) eq join( "", sort @guarantor_ids ) }
709
                        @loop_borrowers_relationships )
710
                    {
711
                        push @loop_borrowers_relationships, \@guarantor_ids;
712
                    }
713
                }
714
            }
715
        }
716
    }
717
718
    if ( scalar @loop_borrowers_relationships > 0 ) {
719
        new_section("The list of guarantors who are also guarantee.");
720
        my $count = 0;
721
        foreach my $table (@loop_borrowers_relationships) {
722
            $count++;
723
            print "Loop $count, borrowers id  : ";
724
            foreach my $borrower_id (@$table) {
725
                print "$borrower_id , ";
726
            }
727
            print "\n";
728
        }
729
        new_hint("Relationships that form guarantor loops must be deleted");
730
    }
731
}
732
733
sub CheckRelationshipsLoop {
482
    my @loop_borrowers_relationships;
734
    my @loop_borrowers_relationships;
483
    my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
735
    my @guarantor_ids = Koha::Patron::Relationships->_resultset->get_column('guarantor_id')->all();
484
    my @guarantee_ids = Koha::Patron::Relationships->_resultset->get_column('guarantee_id')->all();
736
    my @guarantee_ids = Koha::Patron::Relationships->_resultset->get_column('guarantee_id')->all();
Lines 600-622 Catch data inconsistencies in Koha database: Link Here
600
    * Bibliographic records without a title
852
    * Bibliographic records without a title
601
    * Invalid MARCXML in bibliographic records
853
    * Invalid MARCXML in bibliographic records
602
    * Patrons with invalid category types due to lower and upper age limits
854
    * Patrons with invalid category types due to lower and upper age limits
855
    * Relationships that form guarantor loops
603
* Any date fields in the database (timestamp, datetime, date) set to 0000-00-00
856
* Any date fields in the database (timestamp, datetime, date) set to 0000-00-00
604
857
605
=head2 OPTIONS
858
=head2 OPTIONS
606
859
607
  --check-branch      Check for items without home or holding library
860
  --check-branch     Check for items without home or holding library
608
  --check-auth        Check for authority records with invalid authority type
861
  --check-auth       Check for authority records with invalid authority type
609
  --check-status      Check for bibliographic records and items without an item type or with an invalid item type
862
  --check-status     Check for bibliographic records and items without an item type or with an invalid item type
610
  --check-framework   Check for invalid values in fields where the framework limits to an authorized value category
863
  --check-framework  Check for invalid values in fields where the framework limits to an authorized value category
611
  --check-title       Check for bibliographic records without a title
864
  --check-title      Check for bibliographic records without a title
612
  --check-age         Check for patrons with invalid age for category
865
  --check-age        Check for patrons with invalid age for category
613
  --skip-branch       Skip checking for items without home or holding library
866
  --check-loop       Check for relationships that form guarantor loops
614
  --skip-auth         Skip checking for authority records with invalid authority type
867
  --skip-branch      Skip checking for items without home or holding library
615
  --skip-status       Skip checking for bibliographic records and items without an item type or with an invalid item type
868
  --skip-auth        Skip checking for authority records with invalid authority type
616
  --skip-framework    Skip checking for invalid values in fields where the framework limits to an authorized value category
869
  --skip-status      Skip checking for bibliographic records and items without an item type or with an invalid item type
617
  --skip-title        Skip checking for bibliographic records without a title
870
  --skip-framework   Skip checking for invalid values in fields where the framework limits to an authorized value category
618
  --skip-age          Skip checking for patrons with invalid age for category
871
  --skip-title       Skip checking for bibliographic records without a title
619
  --help              Print usage information
872
  --skip-age         Skip checking for patrons with invalid age for category
873
  --skip-loop        Skip checking for relationships that form guarantor loops
874
  --help             Print usage information
620
875
621
Note: If no options are provided, all tests will be run.
876
Note: If no options are provided, all tests will be run.
622
877
623
- 

Return to bug 36027