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

(-)a/C4/Members.pm (-52 / +128 lines)
Lines 881-952 sub changepassword { Link Here
881
881
882
=head2 fixup_cardnumber
882
=head2 fixup_cardnumber
883
883
884
Warning: The caller is responsible for locking the members table in write
884
Previously, there was a gap in time between the insertion of the
885
mode, to avoid database corruption.
885
card number into borrowers and the generation. This gap meant that
886
the same card number could be calculated by two different processes
887
resulting in two different members being added with the same card
888
number.
886
889
887
=cut
890
By serializing the cardnumber requests, at worst we may skip a
891
cardnumber, rather than duplicate one. No locking required,
892
because the INSERT into cardnumber_sequence is atomic!
888
893
889
use vars qw( @weightings );
894
The reason no duplicates happen is because the sequence of
890
my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
895
cardnumbers is calculated from the previous one, and recalculating
896
would generate exactly the same value. So even if two processes
897
are calculating the same cardnumber for a given UID record, it
898
will result in the same value.
899
900
=cut
891
901
892
sub fixup_cardnumber {
902
sub fixup_cardnumber {
893
    my ($cardnumber) = @_;
903
    my ($cardnumber) = @_;
894
    my $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
904
    my @weightings = ( 8, 4, 6, 3, 5, 2, 1 );
905
    my (
906
        $autonumber_members,
907
        $checkdigit,
908
        $dbh,
909
        $sth,
910
        $my_UID,
911
        $current_UID,
912
        $previous_UID,
913
        $previous_cardnumber,
914
        $current_cardnumber,
915
        $sum,
916
        $i,
917
        $temp1,
918
        $temp2,
919
        $rem,
920
        $new_cardnumber,
921
       );
895
922
896
    # Find out whether member numbers should be generated
923
    # Find out whether member numbers should be generated
897
    # automatically. Should be either "1" or something else.
924
    # automatically. Should be either "1" or something else.
898
    # Defaults to "0", which is interpreted as "no".
925
    # Defaults to "0", which is interpreted as "no".
926
    $autonumber_members = C4::Context->boolean_preference('autoMemberNum') || 0;
899
927
900
    #     if ($cardnumber !~ /\S/ && $autonumber_members) {
928
    # if we don't auto-generate, then there is nothing to fixup.
929
    # So return what was passed.
901
    ($autonumber_members) or return $cardnumber;
930
    ($autonumber_members) or return $cardnumber;
902
    my $checkdigit = C4::Context->preference('checkdigit');
903
    my $dbh = C4::Context->dbh;
904
    if ( $checkdigit and $checkdigit eq 'katipo' ) {
905
906
        # if checkdigit is selected, calculate katipo-style cardnumber.
907
        # otherwise, just use the max()
908
        # purpose: generate checksum'd member numbers.
909
        # We'll assume we just got the max value of digits 2-8 of member #'s
910
        # from the database and our job is to increment that by one,
911
        # determine the 1st and 9th digits and return the full string.
912
        my $sth = $dbh->prepare(
913
            "select max(substring(borrowers.cardnumber,2,7)) as new_num from borrowers"
914
        );
915
        $sth->execute;
916
        my $data = $sth->fetchrow_hashref;
917
        $cardnumber = $data->{new_num};
918
        if ( !$cardnumber ) {    # If DB has no values,
919
            $cardnumber = 1000000;    # start at 1000000
920
        } else {
921
            $cardnumber += 1;
922
        }
923
924
        my $sum = 0;
925
        for ( my $i = 0 ; $i < 8 ; $i += 1 ) {
926
            # read weightings, left to right, 1 char at a time
927
            my $temp1 = $weightings[$i];
928
931
929
            # sequence left to right, 1 char at a time
932
    # determine which kind of auto-generate we are doing
930
            my $temp2 = substr( $cardnumber, $i, 1 );
933
    $checkdigit = C4::Context->preference('checkdigit');
931
934
932
            # mult each char 1-7 by its corresponding weighting
935
    # grab a database handle.
933
            $sum += $temp1 * $temp2;
936
    $dbh = C4::Context->dbh;
937
938
    # first we need to show our intent to generate a cardnumber.
939
    $sth = $dbh->prepare("
940
INSERT INTO cardnumber_sequence
941
    (cardnumber,checkdigit) VALUES
942
    ('',?);
943
        ");
944
    $sth->execute($checkdigit);
945
    $my_UID = $dbh->last_insert_id(undef,undef,'cardnumber_sequence','UID');
946
947
    # next we need to find the first ungenerated cardnumber.
948
    # if we don't find any, another process filled them for us!
949
    # if we find some, then we need to start generating.
950
    $sth = $dbh->prepare("
951
SELECT MIN(UID) AS UID FROM cardnumber_sequence
952
WHERE cardnumber='' AND checkdigit=?;
953
        ");
954
    $sth->execute($checkdigit);
955
    $current_UID = $sth->fetchrow();
956
    
957
    # if there is a current_UID, we need to calculate
958
    while ($current_UID) {
959
960
        # so find the previous UID
961
        $sth = $dbh->prepare("
962
SELECT MAX(UID) AS UID FROM cardnumber_sequence
963
WHERE UID < ? AND checkdigit=?;
964
            ");
965
        $sth->execute($current_UID,$checkdigit);
966
        $previous_UID = $sth->fetchrow();
967
968
        # grab the previous cardnumber
969
        $sth = $dbh->prepare("
970
SELECT cardnumber FROM cardnumber_sequence
971
WHERE UID = ?;
972
            ");
973
        $sth->execute($previous_UID);
974
        $previous_cardnumber = $sth->fetchrow();
975
976
        # now calculate the current cardnumber.
977
        if ($checkdigit && $checkdigit eq 'katipo') {
978
            $current_cardnumber = substr($previous_cardnumber,1,7);
979
            $current_cardnumber = $current_cardnumber+1;
980
            $sum = 0;
981
            foreach $i (1..7) {
982
                # read weightings, left to right, 1 char at a time
983
                $temp1 = $weightings[$i-1];
984
985
                # sequence left to right, 1 char at a time
986
                $temp2 = substr( $current_cardnumber, $i-1, 1 );
987
988
                # mult each char 1-7 by its corresponding weighting
989
                $sum += $temp1 * $temp2;
990
            }
991
            $rem = ( $sum % 11 );
992
            $rem = 'X' if $rem == 10;
993
            $current_cardnumber = "V$current_cardnumber$rem";
994
        }
995
        else {
996
            $current_cardnumber = $previous_cardnumber+1;
934
        }
997
        }
935
998
936
        my $rem = ( $sum % 11 );
999
        # update the current_UID record with the generated cardnumber.
937
        $rem = 'X' if $rem == 10;
1000
        $sth = $dbh->prepare("
938
1001
UPDATE cardnumber_sequence SET cardnumber=? WHERE UID=?;
939
        return "V$cardnumber$rem";
1002
            ");
940
     } else {
1003
        $sth->execute($current_cardnumber,$current_UID);
941
1004
942
        my $sth = $dbh->prepare(
1005
        # we need to find the next ungenerated cardnumber.
943
            'SELECT MAX( CAST( cardnumber AS SIGNED ) ) FROM borrowers WHERE cardnumber REGEXP "^-?[0-9]+$"'
1006
        # if we don't find any, we are done generating everything.
944
        );
1007
        $sth = $dbh->prepare("
945
        $sth->execute;
1008
SELECT MIN(UID) AS UID FROM cardnumber_sequence
946
        my ($result) = $sth->fetchrow;
1009
WHERE cardnumber='' and checkdigit=?;
947
        return $result + 1;
1010
            ");
948
    }
1011
        $sth->execute($checkdigit);
949
    return $cardnumber;     # just here as a fallback/reminder 
1012
        $current_UID = $sth->fetchrow();
1013
1014
    }
1015
1016
    # need already know our UID ($my_UID)
1017
    # let's grab the cardnumber.
1018
    $sth = $dbh->prepare("
1019
SELECT cardnumber FROM cardnumber_sequence
1020
WHERE UID=?;
1021
        ");
1022
    $sth->execute($my_UID);
1023
    $new_cardnumber = $sth->fetchrow();
1024
1025
    return $new_cardnumber;
950
}
1026
}
951
1027
952
=head2 GetGuarantees
1028
=head2 GetGuarantees
(-)a/installer/data/mysql/updatedatabase.pl (+101 lines)
Lines 7010-7015 CREATE TABLE IF NOT EXISTS borrower_files ( Link Here
7010
    SetVersion($DBversion);
7010
    SetVersion($DBversion);
7011
}
7011
}
7012
7012
7013
$DBversion = "3.13.00.XXX";
7014
if ( CheckVersion($DBversion) ) {
7015
7016
    # Create new table used to serialize cardnumber requests.
7017
    $dbh->do("
7018
CREATE TABLE IF NOT EXISTS cardnumber_sequence (
7019
  UID INT(16) NOT NULL AUTO_INCREMENT,
7020
  cardnumber VARCHAR(16) NOT NULL,
7021
  checkdigit TEXT NOT NULL,
7022
  PRIMARY KEY (UID)
7023
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
7024
        ");
7025
7026
    my (
7027
        $dbh,
7028
        $sth,
7029
        @cardnumbers_katipo,
7030
        @cardnumbers_none,
7031
        @cardnumbers,
7032
        $cardnumber,
7033
        $data,
7034
       );
7035
7036
    # Add 2 records for the case that borrowers is empty.
7037
    $dbh = C4::Context->dbh;
7038
    $sth = $dbh->prepare("
7039
INSERT INTO cardnumber_sequence
7040
    (cardnumber,checkdigit) VALUES
7041
    ('0','none');
7042
        ");
7043
    $sth->execute();
7044
7045
    $sth = $dbh->prepare("
7046
INSERT INTO cardnumber_sequence
7047
    (cardnumber,checkdigit) VALUES
7048
    ('V09999992','katipo');
7049
        ");
7050
    $sth->execute();
7051
7052
    # Convert existing borrowers records into records that
7053
    # were supposedly generated.
7054
7055
    # grab checkdigit = katipo records
7056
    $sth = $dbh->prepare("
7057
SELECT cardnumber FROM borrowers
7058
WHERE SUBSTR(cardnumber,1,1)
7059
      NOT IN ('0','1','2','3','4','5','6','7','8','9')
7060
ORDER BY cardnumber;
7061
        ");
7062
    $sth->execute();
7063
    $data = $sth->fetchrow_hashref();
7064
    $cardnumber = undef;
7065
    $cardnumber = $data->{'cardnumber'} if $data->{'cardnumber'};
7066
    while ($data->{'cardnumber'}) {
7067
        push @cardnumbers_katipo,$data->{'cardnumber'};
7068
        $data = $sth->fetchrow_hashref();
7069
        $cardnumber = undef;
7070
        $cardnumber = $data->{'cardnumber'} if $data->{'cardnumber'};
7071
    }
7072
7073
    # grab checkdigit = none records
7074
    $sth = $dbh->prepare("
7075
SELECT cardnumber FROM borrowers
7076
WHERE SUBSTR(cardnumber,1,1)
7077
      IN ('0','1','2','3','4','5','6','7','8','9')
7078
ORDER BY CAST(cardnumber AS SIGNED);
7079
        ");
7080
    $sth->execute();
7081
    $data = $sth->fetchrow_hashref();
7082
    $cardnumber = undef;
7083
    $cardnumber = $data->{'cardnumber'} if $data->{'cardnumber'};
7084
    while ($data->{'cardnumber'}) {
7085
        push @cardnumbers_none,$data->{'cardnumber'};
7086
        $data = $sth->fetchrow_hashref();
7087
        $cardnumber = undef;
7088
        $cardnumber = $data->{'cardnumber'} if $data->{'cardnumber'};
7089
    }
7090
7091
    # add the card numbers in.
7092
    foreach $cardnumber (@cardnumbers_none) {
7093
        $sth = $dbh->prepare("
7094
INSERT INTO cardnumber_sequence
7095
    (cardnumber,checkdigit) VALUES
7096
    (?,'none');
7097
            ");
7098
        $sth->execute($cardnumber);
7099
    }
7100
7101
    foreach $cardnumber (@cardnumbers_katipo) {
7102
        $sth = $dbh->prepare("
7103
INSERT INTO cardnumber_sequence
7104
    (cardnumber,checkdigit) VALUES
7105
    (?,'katipo');
7106
            ");
7107
        $sth->execute($cardnumber);
7108
    }
7109
7110
    print "Upgrade to $DBversion done (Bug 10454: Duplicate card numbers may be generated)\n";
7111
    SetVersion($DBversion);
7112
}
7113
7013
=head1 FUNCTIONS
7114
=head1 FUNCTIONS
7014
7115
7015
=head2 TableExists($table)
7116
=head2 TableExists($table)
(-)a/kohaversion.pl (-2 / +1 lines)
Lines 16-22 the kohaversion is divided in 4 parts : Link Here
16
use strict;
16
use strict;
17
17
18
sub kohaversion {
18
sub kohaversion {
19
    our $VERSION = '3.13.00.008';
19
    our $VERSION = '3.13.00.XXX';
20
    # version needs to be set this way
20
    # version needs to be set this way
21
    # so that it can be picked up by Makefile.PL
21
    # so that it can be picked up by Makefile.PL
22
    # during install
22
    # during install
23
- 

Return to bug 10454