Attachment #50695 for bug #12598

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




package Koha::Patrons::Import;

# This file is part of Koha.
#
# Koha is free software; you can redistribute it and/or modify it under the
# terms of the GNU General Public License as published by the Free Software
# Foundation; either version 3 of the License, or (at your option) any later
# version.
#
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with Koha; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

use Modern::Perl;

use Carp;
use Text::CSV;

use C4::Members;
use C4::Branch;

sub import_patrons {
    my ($params) = @_;

    my $handle               = $params->{file};
    my $matchpoint           = $params->{matchpoint};
    my $defaults             = $params->{defaults};
    my $ext_preserve         = $params->{preserve_extended_attributes};
    my $overwrite_cardnumber = $params->{overwrite_cardnumber};

    carp("No file handle passed in!") && return unless $handle;

    my $extended            = C4::Context->preference('ExtendedPatronAttributes');
    my $set_messaging_prefs = C4::Context->preference('EnhancedMessagingPreferences');

    my @columnkeys = map { $_ ne 'borrowernumber' ? $_ : () } C4::Members::columns();
    push( @columnkeys, 'patron_attributes' ) if $extended;

    our $csv = Text::CSV->new( { binary => 1 } );    # binary needed for non-ASCII Unicode

    my @feedback;
    my @errors;

    my $imported    = 0;
    my $alreadyindb = 0;
    my $overwritten = 0;
    my $invalid     = 0;
    my $matchpoint_attr_type;

    # use header line to construct key to column map
    my $borrowerline = <$handle>;
    my $status       = $csv->parse($borrowerline);
    ($status) or push @errors, { badheader => 1, line => $., lineraw => $borrowerline };
    my @csvcolumns = $csv->fields();
    my %csvkeycol;
    my $col = 0;
    foreach my $keycol (@csvcolumns) {

        # columnkeys don't contain whitespace, but some stupid tools add it
        $keycol =~ s/ +//g;
        $csvkeycol{$keycol} = $col++;
    }

    #warn($borrowerline);
    if ($extended) {
        $matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint);
    }

    push @feedback, { feedback => 1, name => 'headerrow', value => join( ', ', @csvcolumns ) };
    my $today_iso = C4::Dates->new()->output('iso');
    my @criticals = qw(surname branchcode categorycode);    # there probably should be others
    my @bad_dates;                                          # I've had a few.
    my $date_re = C4::Dates->new->regexp('syspref');
    my $iso_re  = C4::Dates->new->regexp('iso');
  LINE: while ( my $borrowerline = <$handle> ) {
        my %borrower;
        my @missing_criticals;
        my $patron_attributes;
        my $status  = $csv->parse($borrowerline);
        my @columns = $csv->fields();
        if ( !$status ) {
            push @missing_criticals, { badparse => 1, line => $., lineraw => $borrowerline };
        }
        elsif ( @columns == @columnkeys ) {
            @borrower{@columnkeys} = @columns;

            # MJR: try to fill blanks gracefully by using default values
            foreach my $key (@columnkeys) {
                if ( $borrower{$key} !~ /\S/ ) {
                    $borrower{$key} = $defaults->{$key};
                }
            }
        }
        else {
            # MJR: try to recover gracefully by using default values
            foreach my $key (@columnkeys) {
                if ( defined( $csvkeycol{$key} ) and $columns[ $csvkeycol{$key} ] =~ /\S/ ) {
                    $borrower{$key} = $columns[ $csvkeycol{$key} ];
                }
                elsif ( $defaults->{$key} ) {
                    $borrower{$key} = $defaults->{$key};
                }
                elsif ( scalar grep { $key eq $_ } @criticals ) {

                    # a critical field is undefined
                    push @missing_criticals, { key => $key, line => $., lineraw => $borrowerline };
                }
                else {
                    $borrower{$key} = '';
                }
            }
        }

        #warn join(':',%borrower);
        if ( $borrower{categorycode} ) {
            push @missing_criticals,
              {
                key          => 'categorycode',
                line         => $.,
                lineraw      => $borrowerline,
                value        => $borrower{categorycode},
                category_map => 1
              }
              unless GetBorrowercategory( $borrower{categorycode} );
        }
        else {
            push @missing_criticals, { key => 'categorycode', line => $., lineraw => $borrowerline };
        }
        if ( $borrower{branchcode} ) {
            push @missing_criticals,
              {
                key        => 'branchcode',
                line       => $.,
                lineraw    => $borrowerline,
                value      => $borrower{branchcode},
                branch_map => 1
              }
              unless GetBranchName( $borrower{branchcode} );
        }
        else {
            push @missing_criticals, { key => 'branchcode', line => $., lineraw => $borrowerline };
        }
        if (@missing_criticals) {
            foreach (@missing_criticals) {
                $_->{borrowernumber} = $borrower{borrowernumber} || 'UNDEF';
                $_->{surname}        = $borrower{surname}        || 'UNDEF';
            }
            $invalid++;
            ( 25 > scalar @errors ) and push @errors, { missing_criticals => \@missing_criticals };

            # The first 25 errors are enough.  Keeping track of 30,000+ would destroy performance.
            next LINE;
        }
        if ($extended) {
            my $attr_str = $borrower{patron_attributes};
            $attr_str =~ s/\xe2\x80\x9c/"/g;    # fixup double quotes in case we are passed smart quotes
            $attr_str =~ s/\xe2\x80\x9d/"/g;
            push @feedback, { feedback => 1, name => 'attribute string', value => $attr_str };
            delete $borrower{patron_attributes}
              ;    # not really a field in borrowers, so we don't want to pass it to ModMember.
            $patron_attributes = extended_attributes_code_value_arrayref($attr_str);
        }

        # Popular spreadsheet applications make it difficult to force date outputs to be zero-padded, but we require it.
        foreach (qw(dateofbirth dateenrolled dateexpiry)) {
            my $tempdate = $borrower{$_} or next;
            if ( $tempdate =~ /$date_re/ ) {
                $borrower{$_} = format_date_in_iso($tempdate);
            }
            elsif ( $tempdate =~ /$iso_re/ ) {
                $borrower{$_} = $tempdate;
            }
            else {
                $borrower{$_} = '';
                push @missing_criticals, { key => $_, line => $., lineraw => $borrowerline, bad_date => 1 };
            }
        }
        $borrower{dateenrolled} = $today_iso unless $borrower{dateenrolled};
        $borrower{dateexpiry} = GetExpiryDate( $borrower{categorycode}, $borrower{dateenrolled} )
          unless $borrower{dateexpiry};
        my $borrowernumber;
        my $member;
        if ( ( $matchpoint eq 'cardnumber' ) && ( $borrower{'cardnumber'} ) ) {
            $member = GetMember( 'cardnumber' => $borrower{'cardnumber'} );
            if ($member) {
                $borrowernumber = $member->{'borrowernumber'};
            }
        }
        elsif ($extended) {
            if ( defined($matchpoint_attr_type) ) {
                foreach my $attr (@$patron_attributes) {
                    if ( $attr->{code} eq $matchpoint and $attr->{value} ne '' ) {
                        my @borrowernumbers = $matchpoint_attr_type->get_patrons( $attr->{value} );
                        $borrowernumber = $borrowernumbers[0] if scalar(@borrowernumbers) == 1;
                        last;
                    }
                }
            }
        }

        if ( C4::Members::checkcardnumber( $borrower{cardnumber}, $borrowernumber ) ) {
            push @errors,
              {
                invalid_cardnumber => 1,
                borrowernumber     => $borrowernumber,
                cardnumber         => $borrower{cardnumber}
              };
            $invalid++;
            next;
        }

        if ($borrowernumber) {

            # borrower exists
            unless ($overwrite_cardnumber) {
                $alreadyindb++;
                push(
                    @feedback,
                    {
                        already_in_db => 1,
                        value         => $borrower{'surname'} . ' / ' . $borrowernumber
                    }
                );
                next LINE;
            }
            $borrower{'borrowernumber'} = $borrowernumber;
            for my $col ( keys %borrower ) {

                # use values from extant patron unless our csv file includes this column or we provided a default.
                # FIXME : You cannot update a field with a  perl-evaluated false value using the defaults.

                # The password is always encrypted, skip it!
                next if $col eq 'password';

                unless ( exists( $csvkeycol{$col} ) || $defaults->{$col} ) {
                    $borrower{$col} = $member->{$col} if ( $member->{$col} );
                }
            }
            unless ( ModMember(%borrower) ) {
                $invalid++;

                push(
                    @errors,
                    {
                        name  => 'lastinvalid',
                        value => $borrower{'surname'} . ' / ' . $borrowernumber
                    }
                );
                next LINE;
            }
            if ( $borrower{debarred} ) {

                # Check to see if this debarment already exists
                my $debarrments = GetDebarments(
                    {
                        borrowernumber => $borrowernumber,
                        expiration     => $borrower{debarred},
                        comment        => $borrower{debarredcomment}
                    }
                );

                # If it doesn't, then add it!
                unless (@$debarrments) {
                    AddDebarment(
                        {
                            borrowernumber => $borrowernumber,
                            expiration     => $borrower{debarred},
                            comment        => $borrower{debarredcomment}
                        }
                    );
                }
            }
            if ($extended) {
                if ($ext_preserve) {
                    my $old_attributes = GetBorrowerAttributes($borrowernumber);
                    $patron_attributes = extended_attributes_merge( $old_attributes, $patron_attributes );
                }
                push @errors, { unknown_error => 1 }
                  unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes );
            }
            $overwritten++;
            push(
                @feedback,
                {
                    feedback => 1,
                    name     => 'lastoverwritten',
                    value    => $borrower{'surname'} . ' / ' . $borrowernumber
                }
            );
        }
        else {
            # FIXME: fixup_cardnumber says to lock table, but the web interface doesn't so this doesn't either.
            # At least this is closer to AddMember than in members/memberentry.pl
            if ( !$borrower{'cardnumber'} ) {
                $borrower{'cardnumber'} = fixup_cardnumber(undef);
            }
            if ( $borrowernumber = AddMember(%borrower) ) {

                if ( $borrower{debarred} ) {
                    AddDebarment(
                        {
                            borrowernumber => $borrowernumber,
                            expiration     => $borrower{debarred},
                            comment        => $borrower{debarredcomment}
                        }
                    );
                }

                if ($extended) {
                    SetBorrowerAttributes( $borrowernumber, $patron_attributes );
                }

                if ($set_messaging_prefs) {
                    C4::Members::Messaging::SetMessagingPreferencesFromDefaults(
                        {
                            borrowernumber => $borrowernumber,
                            categorycode   => $borrower{categorycode}
                        }
                    );
                }

                $imported++;
                push(
                    @feedback,
                    {
                        feedback => 1,
                        name     => 'lastimported',
                        value    => $borrower{'surname'} . ' / ' . $borrowernumber
                    }
                );
            }
            else {
                $invalid++;
                push @errors, { unknown_error => 1 };
                push(
                    @errors,
                    {
                        name  => 'lastinvalid',
                        value => $borrower{'surname'} . ' / AddMember',
                    }
                );
            }
        }
    }

    return {
        feedback      => \@feedback,
        errors        => \@errors,
        imported      => $imported,
        overwritten   => $overwritten,
        already_in_db => $alreadyindb,
        invalid       => $invalid,
    };
}

END { }    # module clean-up code here (global destructor)

1;

__END__

=head1 AUTHOR

Koha Team

=cut




     <div class="yui-u first">
<h1>Import patrons</h1>
[% IF ( uploadborrowers ) %]
    <h5>Import results :</h5>
<ul>
	<li>[% imported %] imported records [% IF ( lastimported ) %](last was [% lastimported %])[% END %]</li>
	<li>[% overwritten %] overwritten [% IF ( lastoverwritten ) %](last was [% lastoverwritten %])[% END %]</li>
	<li>[% alreadyindb %] not imported because already in borrowers table and overwrite disabled [% IF ( lastalreadyindb ) %](last was [% lastalreadyindb %])[% END %]</li>
	<li>[% invalid %] not imported because they are not in the expected format [% IF ( lastinvalid ) %](last was [% lastinvalid %])[% END %]</li>
	<li>[% total %] records parsed</li>
	<li><a href="/cgi-bin/koha/tools/tools-home.pl">Back to Tools</a></li>
</ul>
  [% IF ( FEEDBACK ) %]
  <br /><br />
    <div>
    <h5>Feedback:</h5>
    <ul class="feedback">
    [% FOREACH FEEDBAC IN FEEDBACK %]
    <li>
        [% IF ( FEEDBAC.filename ) %]Parsing upload file <span class="filename">[% FEEDBAC.filename %]</span>
        [% ELSIF ( FEEDBAC.backend ) %]Upload parsed using [% FEEDBAC.backend %]
        [% ELSIF ( FEEDBAC.headerrow ) %]These fields found: [% FEEDBAC.value %]
        [% ELSE %][% FEEDBAC.name %] : [% FEEDBAC.value %]
        [% END %]
    </li>
    [% END %]
    </ul>
    </div>
  [% END %]
  [% IF ( ERRORS ) %]
  <br /><br />
    <div>
    <h5>Error analysis:</h5>
    <ul>
        <li>[% imported %] imported records [% IF ( lastimported ) %](last was [% lastimported %])[% END %]</li>
        <li>[% overwritten %] overwritten [% IF ( lastoverwritten ) %](last was [% lastoverwritten %])[% END %]</li>
        <li>[% alreadyindb %] not imported because already in borrowers table and overwrite disabled [% IF ( lastalreadyindb ) %](last was [% lastalreadyindb %])[% END %]</li>
        <li>[% invalid %] not imported because they are not in the expected format [% IF ( lastinvalid ) %](last was [% lastinvalid %])[% END %]</li>
        <li>[% total %] records parsed</li>
        <li><a href="/cgi-bin/koha/tools/tools-home.pl">Back to Tools</a></li>
    </ul>

    [% IF ( feedback ) %]
        <br /><br />

        <div>
            <h5>Feedback:</h5>
                <ul class="feedback">
                    [% FOREACH f IN feedback %]
                        <li>
                            [% IF ( f.filename ) %]
                                Parsing upload file <span class="filename">[% f.filename %]</span>
                            [% ELSIF ( f.backend ) %]
                                Upload parsed using [% f.backend %]
                            [% ELSIF ( f.headerrow ) %]
                                These fields found: [% f.value %]
                            [% ELSIF ( f.already_in_db ) %]
                                Patron already in database: [% f.value %]
                            [% ELSE %]
                                [% f.name %] : [% f.value %]
                            [% END %]
                        </li>
                    [% END %]
                </ul>
        </div>
    [% END %]

    [% IF ( errors ) %]
        <br /><br />

        <div>
            <h5>Error analysis:</h5>
            <ul>
                [% FOREACH e IN errors %]
                    [% IF ( e.badheader ) %]<li>Header row could not be parsed</li>[% END %]

                    [% FOREACH missing_critical IN e.missing_criticals %]
                        <li class="line_error">
                            Line <span class="linenumber">[% missing_critical.line %]</span>

                            [% IF ( missing_critical.badparse ) %]
                                could not be parsed!
                            [% ELSIF ( missing_critical.bad_date ) %]
                                has &quot;[% missing_critical.key %]&quot; in unrecognized format: &quot;[% missing_critical.value %]&quot;
                            [% ELSE %]
                                Critical field &quot;[% missing_critical.key %]&quot;

                                [% IF ( missing_critical.branch_map ) %]
                                    has unrecognized value &quot;[% missing_critical.value %]&quot;
                                [% ELSIF ( missing_critical.category_map ) %]
                                    has unrecognized value &quot;[% missing_critical.value %]&quot;
                                [% ELSE %]
                                    missing
                                [% END %]

                                (borrowernumber: [% missing_critical.borrowernumber %]; surname: [% missing_critical.surname %]).
                            [% END %]

                            <br/>
                            <code>[% missing_critical.lineraw %]</code>
                        </li>
                    [% END %]

                    [% IF e.invalid_cardnumber %]
                        <li class="line_error">
                            Cardnumber [% e.cardnumber %] is not a valid cardnumber
                            [% IF e.borrowernumber %] (for patron with borrowernumber [% e.borrowernumber %])[% END %]
                        </li>
                    [% END %]
                [% END %]
            </ul>
        </div>
            <br /><code>[% missing_critical.lineraw %]</code>
        </li>
        [% END %]
        [% IF ERROR.invalid_cardnumber %]
            <li class="line_error">
                Cardnumber [% ERROR.cardnumber %] is not a valid cardnumber
                [% IF ERROR.borrowernumber %] (for patron with borrowernumber [% ERROR.borrowernumber %])[% END %]
            </li>
        [% END %]
        [% IF ERROR.duplicate_userid %]
            <li class="line_error">
                Userid [% ERROR.userid %] is already used by another patron or is empty.
            </li>
        [% END %]
    [% END %]
    </ul>
    </div>
  [% END %]
[% ELSE %]
    <ul>
        <li>Select a file to import into the borrowers table</li>
        <li>If a cardnumber exists in the table, you can choose whether to ignore the new one or overwrite the old one.</li>
    </ul>

    <form method="post" action="[% SCRIPT_NAME %]" enctype="multipart/form-data">
        <fieldset class="rows">
            <legend>Import into the borrowers table</legend>

            <ol>
                <li>
                    <label for="uploadborrowers">Select the file to import: </label>
                    <input type="file" id="uploadborrowers" name="uploadborrowers" />
                </li>
            </ol>
        </fieldset>

        <fieldset class="rows">
            <legend>Field to use for record matching</legend>
            <ol>
                <li class="radio">
                    <select name="matchpoint" id="matchpoint">
                        <option value="cardnumber">Cardnumber</option>
                        <option value="userid">Username</option>
                        [% FOREACH matchpoint IN matchpoints %]
                            <option value="[% matchpoint.code %]">[% matchpoint.description %]</option>
                        [% END %]
                    </select>
                </li>
            </ol>
        </fieldset>

        <fieldset class="rows">
            <legend>Default values</legend>

            <ol>
                [% FOREACH borrower_field IN borrower_fields %]

                    [% SWITCH borrower_field.field %]
                    [% CASE 'branchcode' %]
                        <li>
                            <label class="description" for="branchcode">[% borrower_field.description %]: </label>
                            <select id="branchcode" name="branchcode">
                                <option value="" selected="selected"></option>
                                [% FOREACH branch IN branches %]
                                    <option value="[% branch.branchcode %]">[% branch.branchname %]</option>
                                [% END %]
                            </select>
                            <span class="field_hint">[% borrower_field.field %]</span>
                        </li>
                    [% CASE 'categorycode' %]
                        <li>
                            <label class="description" for="categorycode">[% borrower_field.description %]: </label>
                            <select id="categorycode" name="categorycode">
                                <option value="" selected="selected"></option>
                                [% FOREACH category IN categories %]
                                    <option value="[% category.categorycode %]">[% category.description %]</option>
                                [% END %]
                            </select>
                            <span class="field_hint">[% borrower_field.field %]</span>
                        </li>
                    [% CASE %]
                        <li>
                            <label class="description" for="[% borrower_field.field %]">[% borrower_field.description %]: </label>
                            <input id="[% borrower_field.field %]" name="[% borrower_field.field %]" />
                            <span class="field_hint">[% borrower_field.field %]</span>
                        </li>
                    [% END %]

                [% END %]

                [% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
                    <li>
                        <label class="description" for="patron_attributes">Patron attributes: </label>
                        <input id="patron_attributes" name="patron_attributes" />
                        <span class="field_hint">patron_attributes</span>
                    </li>
                [% END %]

            </ol>
        </fieldset>

        <fieldset class="rows">
            <legend>If matching record is already in the borrowers table:</legend>

            <ol>
                <li class="radio">
                    <input type="radio" id="overwrite_cardnumberno" name="overwrite_cardnumber" value="0" checked="checked" /><label for="overwrite_cardnumberno">Ignore this one, keep the existing one</label>
                </li>

                <li class="radio">
                    <input type="radio" id="overwrite_cardnumberyes" name="overwrite_cardnumber" value="1" /><label for="overwrite_cardnumberyes">Overwrite the existing one with this</label>
                </li>
            </ol>
        </fieldset>

        [% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
            <fieldset class="rows">
                <legend>Patron attributes</legend>

                <ol>
                    <li class="radio">
                        <input type="radio" id="ext_preserve_0" name="ext_preserve" value="0" checked="checked" /><label for="ext_preserve_0">Replace all patron attributes</label>
                    </li>

                    <li class="radio">
                        <input type="radio" id="ext_preserve_1" name="ext_preserve" value="1" /><label for="ext_preserve_1">Replace only included patron attributes</label>
                    </li>
                </ol>
            </fieldset>
        [% END %]

        <fieldset class="action"><input type="submit" value="Import" /></fieldset>
    </form>
    <li>
        <label class="description" for="[% borrower_field.field %]">[% borrower_field.description %]: </label>
        <input id="[% borrower_field.field %]" name="[% borrower_field.field %]" /><span class="field_hint">[% borrower_field.field %]</span>
    </li>
  [% END %]
[% END %]
[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
    <li>
        <label class="description" for="patron_attributes">Patron attributes: </label>
        <input id="patron_attributes" name="patron_attributes" />
        <span class="field_hint">patron_attributes</span>
    </li>
[% END %]

</div>

<div class="yui-u">
    <h2>Notes:</h2>
    <ul>
        <li>The first line in the file must be a header row defining which columns you are supplying in the import file.</li>

        <li><b>Download a starter CSV file with all the columns <a href="?sample=1">here</a>.</b>  Values are comma-separated.</li>

        <li>
            OR choose which fields you want to supply from the following list:
            <ul>
                <li>
                    [% FOREACH columnkey IN borrower_fields %]'[% columnkey.field %]', [% END %]
                </li>
            </ul>
        </li>

        [% IF ( ExtendedPatronAttributes ) %]
            <li>
                If loading patron attributes, the 'patron_attributes' field should contain a comma-separated list of attribute types and values. The attribute type code and a colon should precede each value. For example: <b>INSTID:12345,LANG:fr</b> or <b>STARTDATE:January 1 2010,TRACK:Day</b>. If an input record has more than one attribute, the fields should either be entered as an unquoted string (previous examples), or with each field wrapped in separate double quotes and delimited by a comma: <b>&quot;STARTDATE:January 1, 2010&quot;,&quot;TRACK:Day&quot;</b>.  The second syntax would be required if the data might have a comma in it, like a date string.
            </li>
        [% END %]

        <li>
            The fields 'branchcode' and 'categorycode' are <b>required</b> and <b>must match</b> valid entries in your database.
        </li>

        <li>
            'password' should be stored in plaintext, and will be converted to a Bcrypt hash (if your passwords are already encrypted, talk to your system administrator about options).
	<fieldset class="rows">
    <legend>Patron attributes</legend>
    <ol><li class="radio">
        <input type="radio" id="ext_preserve_0" name="ext_preserve" value="0" checked="checked" /><label for="ext_preserve_0">Replace all patron attributes</label>
        </li>

        <li>
            Date formats should match your system preference, and <b>must</b> be zero-padded, e.g. '01/02/2008'.  Alternatively,
you can supply dates in ISO format (e.g., '2010-10-28').
        </li>
    </ul>

    [% END %]
	<fieldset class="action"><input type="submit" value="Import" /></fieldset>
</form>
[% END %]
</div>
</div>
</div>
</div>

<div class="yui-b noprint">
    [% INCLUDE 'tools-menu.inc' %]
</div>
</div>

    <ul><li>
       [% FOREACH columnkey IN borrower_fields %]'[% columnkey.field %]', [% END %]
    </li></ul>
</li>
[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
<li><b>Extended patron attributes: </b>If loading patron attributes, the 'patron_attributes' field should contain a comma-separated list of attribute types and values. The attribute type code and a colon should precede each value.
    <ul><li>Example 1: INSTID:12345,LANG:fr</li><li>Example 2: STARTDATE:January 1 2010,TRACK:Day</li></ul>
If an input record has more than one attribute, the fields should either be entered as an unquoted string (previous examples), or with each field wrapped in separate double quotes and delimited by a comma:
    <ul><li>Example 3: &quot;STARTDATE:January 1, 2010&quot;,&quot;TRACK:Day&quot;</li></ul>
The second syntax would be required if the data might have a comma in it, like a date string.</li>
[% END %]
<li><b>Required fields: </b>The fields 'branchcode' and 'categorycode' are required and must match valid entries in your database.</li>
<li><b>Password: </b>Values for the field 'password' should be stored in plaintext, and will be converted to a Bcrypt hash (if your passwords are already encrypted, talk to your system administrator about options).</li>
<li><b>Date formats: </b> Date values should match your system preference, and must be zero-padded.
    <ul><li>Example: '01/02/2008'</li></ul>
Alternatively, you can supply dates in ISO format.
    <ul><li>Example: '2010-10-28'</li></ul>
</li>
</ul>

     </div>
    </div>
   </div>
  </div>
  <div class="yui-b noprint">
[% INCLUDE 'tools-menu.inc' %]
  </div>
 </div>
[% INCLUDE 'intranet-bottom.inc' %]




# dates should be in the format you have set up Koha to expect
# branchcode and categorycode need to be valid

use Modern::Perl;
use warnings;

use C4::Auth;
use C4::Output;
use C4::Context;
use C4::Branch qw/GetBranchesLoop GetBranchName/;
use C4::Members;
use C4::Members::Attributes qw(:all);
use C4::Members::AttributeTypes;
use C4::Members::Messaging;
use C4::Reports::Guided;
use C4::Templates;
use Koha::Patron::Debarments;
use Koha::DateUtils;

use C4::Branch;
use C4::Members;

use Koha::Patrons::Import qw(import_patrons);

use Text::CSV;

# Text::CSV::Unicode, even in binary mode, fails to parse lines with these diacriticals:

# use encoding 'utf8';    # don't do this

my ( @errors, @feedback );
my $extended = C4::Context->preference('ExtendedPatronAttributes');

my @columnkeys = map { $_ ne 'borrowernumber' ? $_ : () } C4::Members::columns();
push( @columnkeys, 'patron_attributes' ) if $extended;
if ($extended) {
    push @columnkeys, 'patron_attributes';
}

my $input = CGI->new();
our $csv = Text::CSV->new( { binary => 1 } );    # binary needed for non-ASCII Unicode

#push @feedback, {feedback=>1, name=>'backend', value=>$csv->backend, backend=>$csv->backend}; #XXX


$template->param( borrower_fields => $columns );

if ( $input->param('sample') ) {
    our $csv = Text::CSV->new( { binary => 1 } );    # binary needed for non-ASCII Unicode
    print $input->header(
        -type       => 'application/vnd.sun.xml.calc',    # 'application/vnd.ms-excel' ?
        -attachment => 'patron_import.csv',

    print $csv->string, "\n";
    exit 0;
}

my $uploadborrowers = $input->param('uploadborrowers');
my $matchpoint      = $input->param('matchpoint');
if ($matchpoint) {
    $matchpoint =~ s/^patron_attribute_//;
}
my $overwrite_cardnumber = $input->param('overwrite_cardnumber');

$template->param( SCRIPT_NAME => '/cgi-bin/koha/tools/import_borrowers.pl' );

if ( $uploadborrowers && length($uploadborrowers) > 0 ) {
    my $handle   = $input->upload('uploadborrowers');
    my $handle     = $input->upload('uploadborrowers');
    my $uploadinfo = $input->uploadInfo($uploadborrowers);
    foreach ( keys %$uploadinfo ) {
        push @feedback, { feedback => 1, name => $_, value => $uploadinfo->{$_}, $_ => $uploadinfo->{$_} };
    }
    my $imported    = 0;
    my $alreadyindb = 0;
    my $overwritten = 0;
    my $invalid     = 0;
    my $matchpoint_attr_type;
    my %defaults = $input->Vars;

    my $return = Koha::Patrons::Import::import_patrons(
        {
            file                         => $handle,
            defaults                     => \%defaults,
            matchpoint                   => $matchpoint,
            overwrite_cardnumber         => $input->param('overwrite_cardnumber'),
            preserve_extended_attributes => $input->param('ext_preserve') || 0,
    foreach my $keycol (@csvcolumns) {

        # columnkeys don't contain whitespace, but some stupid tools add it
        $keycol =~ s/ +//g;
        $csvkeycol{$keycol} = $col++;
    }

    #warn($borrowerline);
    my $ext_preserve = $input->param('ext_preserve') || 0;
    if ($extended) {
        $matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint);
    }

    push @feedback, { feedback => 1, name => 'headerrow', value => join( ', ', @csvcolumns ) };
    my $today_iso = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
    my @criticals = qw(surname branchcode categorycode);    # there probably should be others
    my @bad_dates;                                          # I've had a few.
  LINE: while ( my $borrowerline = <$handle> ) {
        my %borrower;
        my @missing_criticals;
        my $patron_attributes;
        my $status  = $csv->parse($borrowerline);
        my @columns = $csv->fields();
        if ( !$status ) {
            push @missing_criticals, { badparse => 1, line => $., lineraw => $borrowerline };
        }
        elsif ( @columns == @columnkeys ) {
            @borrower{@columnkeys} = @columns;

            # MJR: try to fill blanks gracefully by using default values
            foreach my $key (@columnkeys) {
                if ( $borrower{$key} !~ /\S/ ) {
                    $borrower{$key} = $defaults{$key};
                }
            }
        }
        else {
            # MJR: try to recover gracefully by using default values
            foreach my $key (@columnkeys) {
                if ( defined( $csvkeycol{$key} ) and $columns[ $csvkeycol{$key} ] =~ /\S/ ) {
                    $borrower{$key} = $columns[ $csvkeycol{$key} ];
                }
                elsif ( $defaults{$key} ) {
                    $borrower{$key} = $defaults{$key};
                }
                elsif ( scalar grep { $key eq $_ } @criticals ) {

                    # a critical field is undefined
                    push @missing_criticals, { key => $key, line => $., lineraw => $borrowerline };
                }
                else {
                    $borrower{$key} = '';
                }
            }
        }

        #warn join(':',%borrower);
        if ( $borrower{categorycode} ) {
            push @missing_criticals,
              {
                key          => 'categorycode',
                line         => $.,
                lineraw      => $borrowerline,
                value        => $borrower{categorycode},
                category_map => 1
              }
              unless GetBorrowercategory( $borrower{categorycode} );
        }
        else {
            push @missing_criticals, { key => 'categorycode', line => $., lineraw => $borrowerline };
        }
        if ( $borrower{branchcode} ) {
            push @missing_criticals,
              {
                key        => 'branchcode',
                line       => $.,
                lineraw    => $borrowerline,
                value      => $borrower{branchcode},
                branch_map => 1
              }
              unless GetBranchName( $borrower{branchcode} );
        }
    );
            push @missing_criticals, { key => 'branchcode', line => $., lineraw => $borrowerline };
        }
        if (@missing_criticals) {
            foreach (@missing_criticals) {
                $_->{borrowernumber} = $borrower{borrowernumber} || 'UNDEF';
                $_->{surname}        = $borrower{surname}        || 'UNDEF';
            }
            $invalid++;
            ( 25 > scalar @errors ) and push @errors, { missing_criticals => \@missing_criticals };

            # The first 25 errors are enough.  Keeping track of 30,000+ would destroy performance.
            next LINE;
        }
        if ($extended) {
            my $attr_str = $borrower{patron_attributes};
            $attr_str =~ s/\xe2\x80\x9c/"/g;    # fixup double quotes in case we are passed smart quotes
            $attr_str =~ s/\xe2\x80\x9d/"/g;
            push @feedback,
              { feedback => 1, name => 'attribute string', value => $attr_str, filename => $uploadborrowers };
            delete $borrower{patron_attributes}
              ;    # not really a field in borrowers, so we don't want to pass it to ModMember.
            $patron_attributes = extended_attributes_code_value_arrayref($attr_str);
        }

        # Popular spreadsheet applications make it difficult to force date outputs to be zero-padded, but we require it.
        foreach (qw(dateofbirth dateenrolled dateexpiry)) {
            my $tempdate = $borrower{$_} or next;
            $tempdate =
              eval { output_pref( { dt => dt_from_string($tempdate), dateonly => 1, dateformat => 'iso' } ); };
            if ($tempdate) {
                $borrower{$_} = $tempdate;
            }
            else {
                $borrower{$_} = '';
                push @missing_criticals, { key => $_, line => $., lineraw => $borrowerline, bad_date => 1 };
            }
        }
        $borrower{dateenrolled} = $today_iso unless $borrower{dateenrolled};
        $borrower{dateexpiry} = GetExpiryDate( $borrower{categorycode}, $borrower{dateenrolled} )
          unless $borrower{dateexpiry};
        my $borrowernumber;
        my $member;
        if ( ( $matchpoint eq 'cardnumber' ) && ( $borrower{'cardnumber'} ) ) {
            $member = GetMember( 'cardnumber' => $borrower{'cardnumber'} );
            if ($member) {
                $borrowernumber = $member->{'borrowernumber'};
            }
        }
        elsif ( ( $matchpoint eq 'userid' ) && ( $borrower{'userid'} ) ) {
            $member = GetMember( 'userid' => $borrower{'userid'} );
            if ($member) {
                $borrowernumber = $member->{'borrowernumber'};
            }
        }
        elsif ($extended) {
            if ( defined($matchpoint_attr_type) ) {
                foreach my $attr (@$patron_attributes) {
                    if ( $attr->{code} eq $matchpoint and $attr->{value} ne '' ) {
                        my @borrowernumbers = $matchpoint_attr_type->get_patrons( $attr->{value} );
                        $borrowernumber = $borrowernumbers[0] if scalar(@borrowernumbers) == 1;
                        last;
                    }
                }
            }
        }

        if ( C4::Members::checkcardnumber( $borrower{cardnumber}, $borrowernumber ) ) {
            push @errors,
              {
                invalid_cardnumber => 1,
                borrowernumber     => $borrowernumber,
                cardnumber         => $borrower{cardnumber}
              };
            $invalid++;
            next;
        }

        if ($borrowernumber) {

            # borrower exists
            unless ($overwrite_cardnumber) {
                $alreadyindb++;
                $template->param( 'lastalreadyindb' => $borrower{'surname'} . ' / ' . $borrowernumber );
                next LINE;
            }
            $borrower{'borrowernumber'} = $borrowernumber;
            for my $col ( keys %borrower ) {

                # use values from extant patron unless our csv file includes this column or we provided a default.
                # FIXME : You cannot update a field with a  perl-evaluated false value using the defaults.

                # The password is always encrypted, skip it!
                next if $col eq 'password';

                unless ( exists( $csvkeycol{$col} ) || $defaults{$col} ) {
                    $borrower{$col} = $member->{$col} if ( $member->{$col} );
                }
            }
            unless ( ModMember(%borrower) ) {
                $invalid++;

                # until we have better error trapping, we have no way of knowing why ModMember errored out...
                push @errors, { unknown_error => 1 };
                $template->param( 'lastinvalid' => $borrower{'surname'} . ' / ' . $borrowernumber );
                next LINE;
            }

            # Don't add a new restriction if the existing 'combined' restriction matches this one
            if ( $borrower{debarred} && ( ( $borrower{debarred} ne $member->{debarred} ) || ( $borrower{debarredcomment} ne $member->{debarredcomment} ) ) ) {
                # Check to see if this debarment already exists
                my $debarrments = GetDebarments(
                    {
                        borrowernumber => $borrowernumber,
                        expiration     => $borrower{debarred},
                        comment        => $borrower{debarredcomment}
                    }
                );

                # If it doesn't, then add it!
                unless (@$debarrments) {
                    AddDebarment(
                        {
                            borrowernumber => $borrowernumber,
                            expiration     => $borrower{debarred},
                            comment        => $borrower{debarredcomment}
                        }
                    );
                }
            }

            if ($extended) {
                if ($ext_preserve) {
                    my $old_attributes = GetBorrowerAttributes($borrowernumber);
                    $patron_attributes = extended_attributes_merge( $old_attributes, $patron_attributes )
                      ;    #TODO: expose repeatable options in template
                }
                
                push @errors, { unknown_error => 1 }
                  unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes );
            }
            $overwritten++;
            $template->param( 'lastoverwritten' => $borrower{'surname'} . ' / ' . $borrowernumber );
        }
        else {
            # FIXME: fixup_cardnumber says to lock table, but the web interface doesn't so this doesn't either.
            # At least this is closer to AddMember than in members/memberentry.pl
            if ( !$borrower{'cardnumber'} ) {
                $borrower{'cardnumber'} = fixup_cardnumber(undef);
            }
            if ( $borrowernumber = AddMember(%borrower) ) {

    my $feedback    = $return->{feedback};
    my $errors      = $return->{errors};
    my $imported    = $return->{imported};
    my $overwritten = $return->{overwritten};
    my $alreadyindb = $return->{already_in_db};
    my $invalid     = $return->{invalid};
                        }
                    );
                }

    my $uploadinfo = $input->uploadInfo($uploadborrowers);
    foreach ( keys %$uploadinfo ) {
        push @$feedback, { feedback => 1, name => $_, value => $uploadinfo->{$_}, $_ => $uploadinfo->{$_} };
    }

    push @$feedback, { feedback => 1, name => 'filename', value => $uploadborrowers, filename => $uploadborrowers };
                    C4::Members::Messaging::SetMessagingPreferencesFromDefaults(
                        {
                            borrowernumber => $borrowernumber,
                            categorycode   => $borrower{categorycode}
                        }
                    );
                }

                $imported++;
                $template->param( 'lastimported' => $borrower{'surname'} . ' / ' . $borrowernumber );
            }
            else {
                $invalid++;
                push @errors, { unknown_error => 1 };
                $template->param( 'lastinvalid' => $borrower{'surname'} . ' / AddMember' );
            }
        }
    }
    (@errors)   and $template->param( ERRORS   => \@errors );
    (@feedback) and $template->param( FEEDBACK => \@feedback );
    $template->param(
        uploadborrowers => 1,
        errors          => $errors,
        feedback        => $feedback,
        imported        => $imported,
        overwritten     => $overwritten,
        alreadyindb     => $alreadyindb,
        invalid         => $invalid,
        total           => $imported + $alreadyindb + $invalid + $overwritten,
    );

}
- 

Return to bug 12598

Line 0 Link Here

(-)a/Koha/Patrons/Import.pm (+370 lines)
		1	package Koha::Patrons::Import;
		2
		3	# This file is part of Koha.
		4	#
		5	# Koha is free software; you can redistribute it and/or modify it under the
		6	# terms of the GNU General Public License as published by the Free Software
		7	# Foundation; either version 3 of the License, or (at your option) any later
		8	# version.
		9	#
		10	# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
		11	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
		12	# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
		13	#
		14	# You should have received a copy of the GNU General Public License along
		15	# with Koha; if not, write to the Free Software Foundation, Inc.,
		16	# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
		17
		18	use Modern::Perl;
		19
		20	use Carp;
		21	use Text::CSV;
		22
		23	use C4::Members;
		24	use C4::Branch;
		25
		26	sub import_patrons {
		27	my ($params) = @_;
		28
		29	my $handle = $params->{file};
		30	my $matchpoint = $params->{matchpoint};
		31	my $defaults = $params->{defaults};
		32	my $ext_preserve = $params->{preserve_extended_attributes};
		33	my $overwrite_cardnumber = $params->{overwrite_cardnumber};
		34
		35	carp("No file handle passed in!") && return unless $handle;
		36
		37	my $extended = C4::Context->preference('ExtendedPatronAttributes');
		38	my $set_messaging_prefs = C4::Context->preference('EnhancedMessagingPreferences');
		39
		40	my @columnkeys = map { $_ ne 'borrowernumber' ? $_ : () } C4::Members::columns();
		41	push( @columnkeys, 'patron_attributes' ) if $extended;
		42
		43	our $csv = Text::CSV->new( { binary => 1 } ); # binary needed for non-ASCII Unicode
		44
		45	my @feedback;
		46	my @errors;
		47
		48	my $imported = 0;
		49	my $alreadyindb = 0;
		50	my $overwritten = 0;
		51	my $invalid = 0;
		52	my $matchpoint_attr_type;
		53
		54	# use header line to construct key to column map
		55	my $borrowerline = <$handle>;
		56	my $status = $csv->parse($borrowerline);
		57	($status) or push @errors, { badheader => 1, line => $., lineraw => $borrowerline };
		58	my @csvcolumns = $csv->fields();
		59	my %csvkeycol;
		60	my $col = 0;
		61	foreach my $keycol (@csvcolumns) {
		62
		63	# columnkeys don't contain whitespace, but some stupid tools add it
		64	$keycol =~ s/ +//g;
65	$csvkeycol{$keycol} = $col++;
66	}
67
68	#warn($borrowerline);
69	if ($extended) {
70	$matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint);
71	}
72
73	push @feedback, { feedback => 1, name => 'headerrow', value => join( ', ', @csvcolumns ) };
74	my $today_iso = C4::Dates->new()->output('iso');
75	my @criticals = qw(surname branchcode categorycode); # there probably should be others
76	my @bad_dates; # I've had a few.
77	my $date_re = C4::Dates->new->regexp('syspref');
78	my $iso_re = C4::Dates->new->regexp('iso');
79	LINE: while ( my $borrowerline = <$handle> ) {
80	my %borrower;
81	my @missing_criticals;
82	my $patron_attributes;
83	my $status = $csv->parse($borrowerline);
84	my @columns = $csv->fields();
85	if ( !$status ) {
86	push @missing_criticals, { badparse => 1, line => $., lineraw => $borrowerline };
87	}
88	elsif ( @columns == @columnkeys ) {
89	@borrower{@columnkeys} = @columns;
90
91	# MJR: try to fill blanks gracefully by using default values
92	foreach my $key (@columnkeys) {
93	if ( $borrower{$key} !~ /\S/ ) {
94	$borrower{$key} = $defaults->{$key};
95	}
96	}
97	}
98	else {
99	# MJR: try to recover gracefully by using default values
100	foreach my $key (@columnkeys) {
101	if ( defined( $csvkeycol{$key} ) and $columns[ $csvkeycol{$key} ] =~ /\S/ ) {
102	$borrower{$key} = $columns[ $csvkeycol{$key} ];
103	}
104	elsif ( $defaults->{$key} ) {
105	$borrower{$key} = $defaults->{$key};
106	}
107	elsif ( scalar grep { $key eq $_ } @criticals ) {
108
109	# a critical field is undefined
110	push @missing_criticals, { key => $key, line => $., lineraw => $borrowerline };
111	}
112	else {
113	$borrower{$key} = '';
114	}
115	}
116	}
117
118	#warn join(':',%borrower);
119	if ( $borrower{categorycode} ) {
120	push @missing_criticals,
121	{
122	key => 'categorycode',
123	line => $.,
124	lineraw => $borrowerline,
125	value => $borrower{categorycode},
126	category_map => 1
127	}
128	unless GetBorrowercategory( $borrower{categorycode} );
129	}
130	else {
131	push @missing_criticals, { key => 'categorycode', line => $., lineraw => $borrowerline };
132	}
133	if ( $borrower{branchcode} ) {
134	push @missing_criticals,
135	{
136	key => 'branchcode',
137	line => $.,
138	lineraw => $borrowerline,
139	value => $borrower{branchcode},
140	branch_map => 1
141	}
142	unless GetBranchName( $borrower{branchcode} );
143	}
144	else {
145	push @missing_criticals, { key => 'branchcode', line => $., lineraw => $borrowerline };
146	}
147	if (@missing_criticals) {
148	foreach (@missing_criticals) {
149	$_->{borrowernumber} = $borrower{borrowernumber} \|\| 'UNDEF';
150	$_->{surname} = $borrower{surname} \|\| 'UNDEF';
151	}
152	$invalid++;
153	( 25 > scalar @errors ) and push @errors, { missing_criticals => \@missing_criticals };
154
155	# The first 25 errors are enough. Keeping track of 30,000+ would destroy performance.
156	next LINE;
157	}
158	if ($extended) {
159	my $attr_str = $borrower{patron_attributes};
160	$attr_str =~ s/\xe2\x80\x9c/"/g; # fixup double quotes in case we are passed smart quotes
161	$attr_str =~ s/\xe2\x80\x9d/"/g;
162	push @feedback, { feedback => 1, name => 'attribute string', value => $attr_str };
163	delete $borrower{patron_attributes}
164	; # not really a field in borrowers, so we don't want to pass it to ModMember.
165	$patron_attributes = extended_attributes_code_value_arrayref($attr_str);
166	}
167
168	# Popular spreadsheet applications make it difficult to force date outputs to be zero-padded, but we require it.
169	foreach (qw(dateofbirth dateenrolled dateexpiry)) {
170	my $tempdate = $borrower{$_} or next;
171	if ( $tempdate =~ /$date_re/ ) {
172	$borrower{$_} = format_date_in_iso($tempdate);
173	}
174	elsif ( $tempdate =~ /$iso_re/ ) {
175	$borrower{$_} = $tempdate;
176	}
177	else {
178	$borrower{$_} = '';
179	push @missing_criticals, { key => $_, line => $., lineraw => $borrowerline, bad_date => 1 };
180	}
181	}
182	$borrower{dateenrolled} = $today_iso unless $borrower{dateenrolled};
183	$borrower{dateexpiry} = GetExpiryDate( $borrower{categorycode}, $borrower{dateenrolled} )
184	unless $borrower{dateexpiry};
185	my $borrowernumber;
186	my $member;
187	if ( ( $matchpoint eq 'cardnumber' ) && ( $borrower{'cardnumber'} ) ) {
188	$member = GetMember( 'cardnumber' => $borrower{'cardnumber'} );
189	if ($member) {
190	$borrowernumber = $member->{'borrowernumber'};
191	}
192	}
193	elsif ($extended) {
194	if ( defined($matchpoint_attr_type) ) {
195	foreach my $attr (@$patron_attributes) {
196	if ( $attr->{code} eq $matchpoint and $attr->{value} ne '' ) {
197	my @borrowernumbers = $matchpoint_attr_type->get_patrons( $attr->{value} );
198	$borrowernumber = $borrowernumbers[0] if scalar(@borrowernumbers) == 1;
199	last;
200	}
201	}
202	}
203	}
204
205	if ( C4::Members::checkcardnumber( $borrower{cardnumber}, $borrowernumber ) ) {
206	push @errors,
207	{
208	invalid_cardnumber => 1,
209	borrowernumber => $borrowernumber,
210	cardnumber => $borrower{cardnumber}
211	};
212	$invalid++;
213	next;
214	}
215
216	if ($borrowernumber) {
217
218	# borrower exists
219	unless ($overwrite_cardnumber) {
220	$alreadyindb++;
221	push(
222	@feedback,
223	{
224	already_in_db => 1,
225	value => $borrower{'surname'} . ' / ' . $borrowernumber
226	}
227	);
228	next LINE;
229	}
230	$borrower{'borrowernumber'} = $borrowernumber;
231	for my $col ( keys %borrower ) {
232
233	# use values from extant patron unless our csv file includes this column or we provided a default.
234	# FIXME : You cannot update a field with a perl-evaluated false value using the defaults.
235
236	# The password is always encrypted, skip it!
237	next if $col eq 'password';
238
239	unless ( exists( $csvkeycol{$col} ) \|\| $defaults->{$col} ) {
240	$borrower{$col} = $member->{$col} if ( $member->{$col} );
241	}
242	}
243	unless ( ModMember(%borrower) ) {
244	$invalid++;
245
246	push(
247	@errors,
248	{
249	name => 'lastinvalid',
250	value => $borrower{'surname'} . ' / ' . $borrowernumber
251	}
252	);
253	next LINE;
254	}
255	if ( $borrower{debarred} ) {
256
257	# Check to see if this debarment already exists
258	my $debarrments = GetDebarments(
259	{
260	borrowernumber => $borrowernumber,
261	expiration => $borrower{debarred},
262	comment => $borrower{debarredcomment}
263	}
264	);
265
266	# If it doesn't, then add it!
267	unless (@$debarrments) {
268	AddDebarment(
269	{
270	borrowernumber => $borrowernumber,
271	expiration => $borrower{debarred},
272	comment => $borrower{debarredcomment}
273	}
274	);
275	}
276	}
277	if ($extended) {
278	if ($ext_preserve) {
279	my $old_attributes = GetBorrowerAttributes($borrowernumber);
280	$patron_attributes = extended_attributes_merge( $old_attributes, $patron_attributes );
281	}
282	push @errors, { unknown_error => 1 }
283	unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes );
284	}
285	$overwritten++;
286	push(
287	@feedback,
288	{
289	feedback => 1,
290	name => 'lastoverwritten',
291	value => $borrower{'surname'} . ' / ' . $borrowernumber
292	}
293	);
294	}
295	else {
296	# FIXME: fixup_cardnumber says to lock table, but the web interface doesn't so this doesn't either.
297	# At least this is closer to AddMember than in members/memberentry.pl
298	if ( !$borrower{'cardnumber'} ) {
299	$borrower{'cardnumber'} = fixup_cardnumber(undef);
300	}
301	if ( $borrowernumber = AddMember(%borrower) ) {
302
303	if ( $borrower{debarred} ) {
304	AddDebarment(
305	{
306	borrowernumber => $borrowernumber,
307	expiration => $borrower{debarred},
308	comment => $borrower{debarredcomment}
309	}
310	);
311	}
312
313	if ($extended) {
314	SetBorrowerAttributes( $borrowernumber, $patron_attributes );
315	}
316
317	if ($set_messaging_prefs) {
318	C4::Members::Messaging::SetMessagingPreferencesFromDefaults(
319	{
320	borrowernumber => $borrowernumber,
321	categorycode => $borrower{categorycode}
322	}
323	);
324	}
325
326	$imported++;
327	push(
328	@feedback,
329	{
330	feedback => 1,
331	name => 'lastimported',
332	value => $borrower{'surname'} . ' / ' . $borrowernumber
333	}
334	);
335	}
336	else {
337	$invalid++;
338	push @errors, { unknown_error => 1 };
339	push(
340	@errors,
341	{
342	name => 'lastinvalid',
343	value => $borrower{'surname'} . ' / AddMember',
344	}
345	);
346	}
347	}
348	}
349
350	return {
351	feedback => \@feedback,
352	errors => \@errors,
353	imported => $imported,
354	overwritten => $overwritten,
355	already_in_db => $alreadyindb,
356	invalid => $invalid,
357	};
358	}
359
360	END { } # module clean-up code here (global destructor)
361
362	1;
363
364	__END__
365
366	=head1 AUTHOR
367
368	Koha Team
369
370	=cut

Lines 33-235 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/import_borrowers.tt (-187 / +234 lines)
33	<div class="yui-u first">	33	<div class="yui-u first">
34	<h1>Import patrons</h1>	34	<h1>Import patrons</h1>
35	[% IF ( uploadborrowers ) %]	35	[% IF ( uploadborrowers ) %]
36	<h5>Import results :</h5>	36	<h5>Import results :</h5>
37	<ul>
38	<li>[% imported %] imported records [% IF ( lastimported ) %](last was [% lastimported %])[% END %]</li>
39	<li>[% overwritten %] overwritten [% IF ( lastoverwritten ) %](last was [% lastoverwritten %])[% END %]</li>
40	<li>[% alreadyindb %] not imported because already in borrowers table and overwrite disabled [% IF ( lastalreadyindb ) %](last was [% lastalreadyindb %])[% END %]</li>
41	<li>[% invalid %] not imported because they are not in the expected format [% IF ( lastinvalid ) %](last was [% lastinvalid %])[% END %]</li>
42	<li>[% total %] records parsed</li>
43	<li><a href="/cgi-bin/koha/tools/tools-home.pl">Back to Tools</a></li>
44	</ul>
45	[% IF ( FEEDBACK ) %]
46	<br /><br />
47	<div>
48	<h5>Feedback:</h5>
49	<ul class="feedback">
50	[% FOREACH FEEDBAC IN FEEDBACK %]
51	<li>
52	[% IF ( FEEDBAC.filename ) %]Parsing upload file <span class="filename">[% FEEDBAC.filename %]</span>
53	[% ELSIF ( FEEDBAC.backend ) %]Upload parsed using [% FEEDBAC.backend %]
54	[% ELSIF ( FEEDBAC.headerrow ) %]These fields found: [% FEEDBAC.value %]
55	[% ELSE %][% FEEDBAC.name %] : [% FEEDBAC.value %]
56	[% END %]
57	</li>
58	[% END %]
59	</ul>
60	</div>
61	[% END %]
62	[% IF ( ERRORS ) %]
63	<br /><br />
64	<div>
65	<h5>Error analysis:</h5>
66	<ul>	37	<ul>
67	[% FOREACH ERROR IN ERRORS %]	38	<li>[% imported %] imported records [% IF ( lastimported ) %](last was [% lastimported %])[% END %]</li>
68	[% IF ( ERROR.badheader ) %]<li>Header row could not be parsed</li>[% END %]	39	<li>[% overwritten %] overwritten [% IF ( lastoverwritten ) %](last was [% lastoverwritten %])[% END %]</li>
69	[% FOREACH missing_critical IN ERROR.missing_criticals %]	40	<li>[% alreadyindb %] not imported because already in borrowers table and overwrite disabled [% IF ( lastalreadyindb ) %](last was [% lastalreadyindb %])[% END %]</li>
70	<li class="line_error">	41	<li>[% invalid %] not imported because they are not in the expected format [% IF ( lastinvalid ) %](last was [% lastinvalid %])[% END %]</li>
71	Line <span class="linenumber">[% missing_critical.line %]</span>	42	<li>[% total %] records parsed</li>
72	[% IF ( missing_critical.badparse ) %]	43	<li><a href="/cgi-bin/koha/tools/tools-home.pl">Back to Tools</a></li>
73	could not be parsed!	44	</ul>
74	[% ELSIF ( missing_critical.bad_date ) %]	45
75	has "[% missing_critical.key %]" in unrecognized format: "[% missing_critical.value %]"	46	[% IF ( feedback ) %]
76	[% ELSE %]	47	<br /><br />
77	Critical field "[% missing_critical.key %]"	48
78	[% IF ( missing_critical.branch_map ) %]has unrecognized value "[% missing_critical.value %]"	49	<div>
79	[% ELSIF ( missing_critical.category_map ) %]has unrecognized value "[% missing_critical.value %]"	50	<h5>Feedback:</h5>
80	[% ELSE %]missing	51	<ul class="feedback">
		52	[% FOREACH f IN feedback %]
		53	<li>
		54	[% IF ( f.filename ) %]
		55	Parsing upload file <span class="filename">[% f.filename %]</span>
		56	[% ELSIF ( f.backend ) %]
		57	Upload parsed using [% f.backend %]
		58	[% ELSIF ( f.headerrow ) %]
		59	These fields found: [% f.value %]
		60	[% ELSIF ( f.already_in_db ) %]
		61	Patron already in database: [% f.value %]
		62	[% ELSE %]
		63	[% f.name %] : [% f.value %]
		64	[% END %]
		65	</li>
		66	[% END %]
		67	</ul>
		68	</div>
		69	[% END %]
		70
		71	[% IF ( errors ) %]
		72	<br /><br />
		73
		74	<div>
		75	<h5>Error analysis:</h5>
		76	<ul>
		77	[% FOREACH e IN errors %]
		78	[% IF ( e.badheader ) %]<li>Header row could not be parsed</li>[% END %]
		79
		80	[% FOREACH missing_critical IN e.missing_criticals %]
		81	<li class="line_error">
		82	Line <span class="linenumber">[% missing_critical.line %]</span>
		83
		84	[% IF ( missing_critical.badparse ) %]
		85	could not be parsed!
		86	[% ELSIF ( missing_critical.bad_date ) %]
		87	has "[% missing_critical.key %]" in unrecognized format: "[% missing_critical.value %]"
		88	[% ELSE %]
		89	Critical field "[% missing_critical.key %]"
		90
		91	[% IF ( missing_critical.branch_map ) %]
		92	has unrecognized value "[% missing_critical.value %]"
		93	[% ELSIF ( missing_critical.category_map ) %]
		94	has unrecognized value "[% missing_critical.value %]"
		95	[% ELSE %]
		96	missing
		97	[% END %]
		98
		99	(borrowernumber: [% missing_critical.borrowernumber %]; surname: [% missing_critical.surname %]).
		100	[% END %]
		101
		102	<br/>
		103	<code>[% missing_critical.lineraw %]</code>
		104	</li>
		105	[% END %]
		106
		107	[% IF e.invalid_cardnumber %]
		108	<li class="line_error">
		109	Cardnumber [% e.cardnumber %] is not a valid cardnumber
		110	[% IF e.borrowernumber %] (for patron with borrowernumber [% e.borrowernumber %])[% END %]
		111	</li>
		112	[% END %]
81	[% END %]	113	[% END %]
82	(borrowernumber: [% missing_critical.borrowernumber %]; surname: [% missing_critical.surname %]).	114	</ul>
83	[% END %]	115	</div>
84	<br /><code>[% missing_critical.lineraw %]</code>
85	</li>
86	[% END %]
87	[% IF ERROR.invalid_cardnumber %]
88	<li class="line_error">
89	Cardnumber [% ERROR.cardnumber %] is not a valid cardnumber
90	[% IF ERROR.borrowernumber %] (for patron with borrowernumber [% ERROR.borrowernumber %])[% END %]
91	</li>
92	[% END %]
93	[% IF ERROR.duplicate_userid %]
94	<li class="line_error">
95	Userid [% ERROR.userid %] is already used by another patron or is empty.
96	</li>
97	[% END %]
98	[% END %]	116	[% END %]
99	</ul>
100	</div>
101	[% END %]
102	[% ELSE %]	117	[% ELSE %]
103	<ul>	118	<ul>
104	<li>Select a file to import into the borrowers table.</li>	119	<li>Select a file to import into the borrowers table</li>
105	<li>If a cardnumber exists in the table, you can choose whether to ignore the new one or overwrite the old one.</li>	120	<li>If a cardnumber exists in the table, you can choose whether to ignore the new one or overwrite the old one.</li>
106	</ul>	121	</ul>
107	<form method="post" action="[% SCRIPT_NAME %]" enctype="multipart/form-data">	122
108	<fieldset class="rows">	123	<form method="post" action="[% SCRIPT_NAME %]" enctype="multipart/form-data">
109	<legend>Import into the borrowers table</legend>	124	<fieldset class="rows">
110	<ol>	125	<legend>Import into the borrowers table</legend>
111	<li>	126
112	<label for="uploadborrowers">Select the file to import: </label>	127	<ol>
113	<input type="file" id="uploadborrowers" name="uploadborrowers" />	128	<li>
114	</li>	129	<label for="uploadborrowers">Select the file to import: </label>
115	</ol></fieldset>	130	<input type="file" id="uploadborrowers" name="uploadborrowers" />
116	<fieldset class="rows">	131	</li>
117	<legend>Field to use for record matching</legend>	132	</ol>
118	<ol>	133	</fieldset>
119	<li class="radio">	134
120	<select name="matchpoint" id="matchpoint">	135	<fieldset class="rows">
121	<option value="cardnumber">Cardnumber</option>	136	<legend>Field to use for record matching</legend>
122	<option value="userid">Username</option>	137	<ol>
123	[% FOREACH matchpoint IN matchpoints %]	138	<li class="radio">
124	<option value="[% matchpoint.code %]">[% matchpoint.description %]</option>	139	<select name="matchpoint" id="matchpoint">
		140	<option value="cardnumber">Cardnumber</option>
		141	<option value="userid">Username</option>
		142	[% FOREACH matchpoint IN matchpoints %]
		143	<option value="[% matchpoint.code %]">[% matchpoint.description %]</option>
		144	[% END %]
		145	</select>
		146	</li>
		147	</ol>
		148	</fieldset>
		149
		150	<fieldset class="rows">
		151	<legend>Default values</legend>
		152
		153	<ol>
		154	[% FOREACH borrower_field IN borrower_fields %]
		155
		156	[% SWITCH borrower_field.field %]
		157	[% CASE 'branchcode' %]
		158	<li>
		159	<label class="description" for="branchcode">[% borrower_field.description %]: </label>
		160	<select id="branchcode" name="branchcode">
		161	<option value="" selected="selected"></option>
		162	[% FOREACH branch IN branches %]
		163	<option value="[% branch.branchcode %]">[% branch.branchname %]</option>
		164	[% END %]
		165	</select>
		166	<span class="field_hint">[% borrower_field.field %]</span>
		167	</li>
		168	[% CASE 'categorycode' %]
		169	<li>
		170	<label class="description" for="categorycode">[% borrower_field.description %]: </label>
		171	<select id="categorycode" name="categorycode">
		172	<option value="" selected="selected"></option>
		173	[% FOREACH category IN categories %]
		174	<option value="[% category.categorycode %]">[% category.description %]</option>
		175	[% END %]
		176	</select>
		177	<span class="field_hint">[% borrower_field.field %]</span>
		178	</li>
		179	[% CASE %]
		180	<li>
		181	<label class="description" for="[% borrower_field.field %]">[% borrower_field.description %]: </label>
		182	<input id="[% borrower_field.field %]" name="[% borrower_field.field %]" />
		183	<span class="field_hint">[% borrower_field.field %]</span>
		184	</li>
125	[% END %]	185	[% END %]
126	</select>	186
127	</li>	187	[% END %]
128	</ol>	188
129	</fieldset>	189	[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
130	<fieldset class="rows">	190	<li>
131	<legend>Default values</legend>	191	<label class="description" for="patron_attributes">Patron attributes: </label>
132	<ol>	192	<input id="patron_attributes" name="patron_attributes" />
133	[% FOREACH borrower_field IN borrower_fields %]	193	<span class="field_hint">patron_attributes</span>
134	[% SWITCH borrower_field.field %]	194	</li>
135	[% CASE 'branchcode' %]	195	[% END %]
136	<li>	196
137	<label class="description" for="branchcode">[% borrower_field.description %]: </label>	197	</ol>
138	<select id="branchcode" name="branchcode">	198	</fieldset>
139	<option value="" selected="selected"></option>	199
140	[% FOREACH branch IN branches %]	200	<fieldset class="rows">
141	<option value="[% branch.branchcode %]">	201	<legend>If matching record is already in the borrowers table:</legend>
142	[% branch.branchname %]</option>	202
143	[% END %]	203	<ol>
144	</select><span class="field_hint">[% borrower_field.field %]</span>	204	<li class="radio">
145	</li>	205	<input type="radio" id="overwrite_cardnumberno" name="overwrite_cardnumber" value="0" checked="checked" /><label for="overwrite_cardnumberno">Ignore this one, keep the existing one</label>
146	[% CASE 'categorycode' %]	206	</li>
147	<li>	207
148	<label class="description" for="categorycode">[% borrower_field.description %]: </label>	208	<li class="radio">
149	<select id="categorycode" name="categorycode">	209	<input type="radio" id="overwrite_cardnumberyes" name="overwrite_cardnumber" value="1" /><label for="overwrite_cardnumberyes">Overwrite the existing one with this</label>
150	<option value="" selected="selected"></option>	210	</li>
151	[% FOREACH category IN categories %]	211	</ol>
152	<option value="[% category.categorycode %]">	212	</fieldset>
153	[% category.description %]</option>	213
		214	[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
		215	<fieldset class="rows">
		216	<legend>Patron attributes</legend>
		217
		218	<ol>
		219	<li class="radio">
		220	<input type="radio" id="ext_preserve_0" name="ext_preserve" value="0" checked="checked" /><label for="ext_preserve_0">Replace all patron attributes</label>
		221	</li>
		222
		223	<li class="radio">
		224	<input type="radio" id="ext_preserve_1" name="ext_preserve" value="1" /><label for="ext_preserve_1">Replace only included patron attributes</label>
		225	</li>
		226	</ol>
		227	</fieldset>
154	[% END %]	228	[% END %]
155	</select><span class="field_hint">[% borrower_field.field %]</span>	229
156	</li>	230	<fieldset class="action"><input type="submit" value="Import" /></fieldset>
157	[% CASE %]	231	</form>
158	<li>
159	<label class="description" for="[% borrower_field.field %]">[% borrower_field.description %]: </label>
160	<input id="[% borrower_field.field %]" name="[% borrower_field.field %]" /><span class="field_hint">[% borrower_field.field %]</span>
161	</li>
162	[% END %]
163	[% END %]
164	[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
165	<li>
166	<label class="description" for="patron_attributes">Patron attributes: </label>
167	<input id="patron_attributes" name="patron_attributes" />
168	<span class="field_hint">patron_attributes</span>
169	</li>
170	[% END %]	232	[% END %]
171	</ol></fieldset>	233
172	<fieldset class="rows">	234	</div>
173	<legend>If matching record is already in the borrowers table:</legend>	235
174	<ol><li class="radio">	236	<div class="yui-u">
175	<input type="radio" id="overwrite_cardnumberno" name="overwrite_cardnumber" value="0" checked="checked" /><label for="overwrite_cardnumberno">Ignore this one, keep the existing one</label>	237	<h2>Notes:</h2>
		238	<ul>
		239	<li>The first line in the file must be a header row defining which columns you are supplying in the import file.</li>
		240
		241	<li><b>Download a starter CSV file with all the columns <a href="?sample=1">here</a>.</b> Values are comma-separated.</li>
		242
		243	<li>
		244	OR choose which fields you want to supply from the following list:
		245	<ul>
		246	<li>
		247	[% FOREACH columnkey IN borrower_fields %]'[% columnkey.field %]', [% END %]
		248	</li>
		249	</ul>
176	</li>	250	</li>
177	<li class="radio">	251
178	<input type="radio" id="overwrite_cardnumberyes" name="overwrite_cardnumber" value="1" /><label for="overwrite_cardnumberyes">Overwrite the existing one with this</label>	252	[% IF ( ExtendedPatronAttributes ) %]
		253	<li>
		254	If loading patron attributes, the 'patron_attributes' field should contain a comma-separated list of attribute types and values. The attribute type code and a colon should precede each value. For example: <b>INSTID:12345,LANG:fr</b> or <b>STARTDATE:January 1 2010,TRACK:Day</b>. If an input record has more than one attribute, the fields should either be entered as an unquoted string (previous examples), or with each field wrapped in separate double quotes and delimited by a comma: <b>"STARTDATE:January 1, 2010","TRACK:Day"</b>. The second syntax would be required if the data might have a comma in it, like a date string.
		255	</li>
		256	[% END %]
		257
		258	<li>
		259	The fields 'branchcode' and 'categorycode' are <b>required</b> and <b>must match</b> valid entries in your database.
179	</li>	260	</li>
180	</ol>	261
181	</fieldset>	262	<li>
182	[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]	263	'password' should be stored in plaintext, and will be converted to a Bcrypt hash (if your passwords are already encrypted, talk to your system administrator about options).
183	<fieldset class="rows">
184	<legend>Patron attributes</legend>
185	<ol><li class="radio">
186	<input type="radio" id="ext_preserve_0" name="ext_preserve" value="0" checked="checked" /><label for="ext_preserve_0">Replace all patron attributes</label>
187	</li>	264	</li>
188	<li class="radio">	265
189	<input type="radio" id="ext_preserve_1" name="ext_preserve" value="1" /><label for="ext_preserve_1">Replace only included patron attributes</label>	266	<li>
		267	Date formats should match your system preference, and <b>must</b> be zero-padded, e.g. '01/02/2008'. Alternatively,
		268	you can supply dates in ISO format (e.g., '2010-10-28').
190	</li>	269	</li>
191	</ol>	270	</ul>
192	</fieldset>	271
193	[% END %]
194	<fieldset class="action"><input type="submit" value="Import" /></fieldset>
195	</form>
196	[% END %]
197	</div>	272	</div>
198	<div class="yui-u">	273	</div>
199	<h2>Notes:</h2>	274	</div>
200	<ul>	275	</div>
201	<li><b>Header: </b>The first line in the file must be a header row defining which columns you are supplying in the import file.</li>	276
202	<li><b>Separator: </b>Values are comma-separated.</li>	277	<div class="yui-b noprint">
203	<li><b>Starter CSV: </b> Koha provides a starter CSV with all the columns.	278	[% INCLUDE 'tools-menu.inc' %]
204	<ul><li><a href="?sample=1">Download starter CSV</a></li></ul>	279	</div>
205	</li>	280	</div>
206	<li><b>Field list: </b>Alternatively, you can create your own CSV and choose which fields you want to supply from the following list:	281
207	<ul><li>
208	[% FOREACH columnkey IN borrower_fields %]'[% columnkey.field %]', [% END %]
209	</li></ul>
210	</li>
211	[% IF ( Koha.Preference('ExtendedPatronAttributes') == 1 ) %]
212	<li><b>Extended patron attributes: </b>If loading patron attributes, the 'patron_attributes' field should contain a comma-separated list of attribute types and values. The attribute type code and a colon should precede each value.
213	<ul><li>Example 1: INSTID:12345,LANG:fr</li><li>Example 2: STARTDATE:January 1 2010,TRACK:Day</li></ul>
214	If an input record has more than one attribute, the fields should either be entered as an unquoted string (previous examples), or with each field wrapped in separate double quotes and delimited by a comma:
215	<ul><li>Example 3: "STARTDATE:January 1, 2010","TRACK:Day"</li></ul>
216	The second syntax would be required if the data might have a comma in it, like a date string.</li>
217	[% END %]
218	<li><b>Required fields: </b>The fields 'branchcode' and 'categorycode' are required and must match valid entries in your database.</li>
219	<li><b>Password: </b>Values for the field 'password' should be stored in plaintext, and will be converted to a Bcrypt hash (if your passwords are already encrypted, talk to your system administrator about options).</li>
220	<li><b>Date formats: </b> Date values should match your system preference, and must be zero-padded.
221	<ul><li>Example: '01/02/2008'</li></ul>
222	Alternatively, you can supply dates in ISO format.
223	<ul><li>Example: '2010-10-28'</li></ul>
224	</li>
225	</ul>
226
227	</div>
228	</div>
229	</div>
230	</div>
231	<div class="yui-b noprint">
232	[% INCLUDE 'tools-menu.inc' %]
233	</div>
234	</div>
235	[% INCLUDE 'intranet-bottom.inc' %]	282	[% INCLUDE 'intranet-bottom.inc' %]

Lines 34-55 Link Here

(-)a/tools/import_borrowers.pl (-307 / +40 lines)
34	# dates should be in the format you have set up Koha to expect	34	# dates should be in the format you have set up Koha to expect
35	# branchcode and categorycode need to be valid	35	# branchcode and categorycode need to be valid
36		36
37	use strict;	37	use Modern::Perl;
38	use warnings;
39		38
40	use C4::Auth;	39	use C4::Auth;
41	use C4::Output;	40	use C4::Output;
42	use C4::Context;
43	use C4::Branch qw/GetBranchesLoop GetBranchName/;
44	use C4::Members;
45	use C4::Members::Attributes qw(:all);
46	use C4::Members::AttributeTypes;
47	use C4::Members::Messaging;
48	use C4::Reports::Guided;
49	use C4::Templates;	41	use C4::Templates;
50	use Koha::Patron::Debarments;	42	use Koha::Patron::Debarments;
51	use Koha::DateUtils;	43	use Koha::DateUtils;
52		44
		45	use C4::Branch;
		46	use C4::Members;
		47
		48	use Koha::Patrons::Import qw(import_patrons);
		49
53	use Text::CSV;	50	use Text::CSV;
54		51
55	# Text::CSV::Unicode, even in binary mode, fails to parse lines with these diacriticals:	52	# Text::CSV::Unicode, even in binary mode, fails to parse lines with these diacriticals:
Lines 61-76 use CGI qw ( -utf8 ); Link Here
61	# use encoding 'utf8'; # don't do this	58	# use encoding 'utf8'; # don't do this
62		59
63	my ( @errors, @feedback );	60	my ( @errors, @feedback );
64	my $extended = C4::Context->preference('ExtendedPatronAttributes');	61	my $extended = C4::Context->preference('ExtendedPatronAttributes');
65	my $set_messaging_prefs = C4::Context->preference('EnhancedMessagingPreferences');	62
66	my @columnkeys = C4::Members::columns();	63	my @columnkeys = map { $_ ne 'borrowernumber' ? $_ : () } C4::Members::columns();
67	@columnkeys = map { $_ ne 'borrowernumber' ? $_ : () } @columnkeys;	64	push( @columnkeys, 'patron_attributes' ) if $extended;
68	if ($extended) {
69	push @columnkeys, 'patron_attributes';
70	}
71		65
72	my $input = CGI->new();	66	my $input = CGI->new();
73	our $csv = Text::CSV->new( { binary => 1 } ); # binary needed for non-ASCII Unicode
74		67
75	#push @feedback, {feedback=>1, name=>'backend', value=>$csv->backend, backend=>$csv->backend}; #XXX	68	#push @feedback, {feedback=>1, name=>'backend', value=>$csv->backend, backend=>$csv->backend}; #XXX
76		69
Lines 97-102 $columns = [ grep { $_->{field} ne 'borrowernumber' ? $_ : () } @$columns ]; Link Here
97	$template->param( borrower_fields => $columns );	90	$template->param( borrower_fields => $columns );
98		91
99	if ( $input->param('sample') ) {	92	if ( $input->param('sample') ) {
		93	our $csv = Text::CSV->new( { binary => 1 } ); # binary needed for non-ASCII Unicode
100	print $input->header(	94	print $input->header(
101	-type => 'application/vnd.sun.xml.calc', # 'application/vnd.ms-excel' ?	95	-type => 'application/vnd.sun.xml.calc', # 'application/vnd.ms-excel' ?
102	-attachment => 'patron_import.csv',	96	-attachment => 'patron_import.csv',
Lines 105-416 if ( $input->param('sample') ) { Link Here
105	print $csv->string, "\n";	99	print $csv->string, "\n";
106	exit 0;	100	exit 0;
107	}	101	}
		102
108	my $uploadborrowers = $input->param('uploadborrowers');	103	my $uploadborrowers = $input->param('uploadborrowers');
109	my $matchpoint = $input->param('matchpoint');	104	my $matchpoint = $input->param('matchpoint');
110	if ($matchpoint) {	105	if ($matchpoint) {
111	$matchpoint =~ s/^patron_attribute_//;	106	$matchpoint =~ s/^patron_attribute_//;
112	}	107	}
113	my $overwrite_cardnumber = $input->param('overwrite_cardnumber');
114		108
115	$template->param( SCRIPT_NAME => '/cgi-bin/koha/tools/import_borrowers.pl' );	109	$template->param( SCRIPT_NAME => '/cgi-bin/koha/tools/import_borrowers.pl' );
116		110
117	if ( $uploadborrowers && length($uploadborrowers) > 0 ) {	111	if ( $uploadborrowers && length($uploadborrowers) > 0 ) {
118	push @feedback, { feedback => 1, name => 'filename', value => $uploadborrowers, filename => $uploadborrowers };	112	my $handle = $input->upload('uploadborrowers');
119	my $handle = $input->upload('uploadborrowers');
120	my $uploadinfo = $input->uploadInfo($uploadborrowers);
121	foreach ( keys %$uploadinfo ) {
122	push @feedback, { feedback => 1, name => $_, value => $uploadinfo->{$_}, $_ => $uploadinfo->{$_} };
123	}
124	my $imported = 0;
125	my $alreadyindb = 0;
126	my $overwritten = 0;
127	my $invalid = 0;
128	my $matchpoint_attr_type;
129	my %defaults = $input->Vars;	113	my %defaults = $input->Vars;
130		114
131	# use header line to construct key to column map	115	my $return = Koha::Patrons::Import::import_patrons(
132	my $borrowerline = <$handle>;	116	{
133	my $status = $csv->parse($borrowerline);	117	file => $handle,
134	($status) or push @errors, { badheader => 1, line => $., lineraw => $borrowerline };	118	defaults => \%defaults,
135	my @csvcolumns = $csv->fields();	119	matchpoint => $matchpoint,
136	my %csvkeycol;	120	overwrite_cardnumber => $input->param('overwrite_cardnumber'),
137	my $col = 0;	121	preserve_extended_attributes => $input->param('ext_preserve') \|\| 0,
138	foreach my $keycol (@csvcolumns) {
139
140	# columnkeys don't contain whitespace, but some stupid tools add it
141	$keycol =~ s/ +//g;
142	$csvkeycol{$keycol} = $col++;
143	}
144
145	#warn($borrowerline);
146	my $ext_preserve = $input->param('ext_preserve') \|\| 0;
147	if ($extended) {
148	$matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint);
149	}
150
151	push @feedback, { feedback => 1, name => 'headerrow', value => join( ', ', @csvcolumns ) };
152	my $today_iso = output_pref( { dt => dt_from_string, dateonly => 1, dateformat => 'iso' } );
153	my @criticals = qw(surname branchcode categorycode); # there probably should be others
154	my @bad_dates; # I've had a few.
155	LINE: while ( my $borrowerline = <$handle> ) {
156	my %borrower;
157	my @missing_criticals;
158	my $patron_attributes;
159	my $status = $csv->parse($borrowerline);
160	my @columns = $csv->fields();
161	if ( !$status ) {
162	push @missing_criticals, { badparse => 1, line => $., lineraw => $borrowerline };
163	}
164	elsif ( @columns == @columnkeys ) {
165	@borrower{@columnkeys} = @columns;
166
167	# MJR: try to fill blanks gracefully by using default values
168	foreach my $key (@columnkeys) {
169	if ( $borrower{$key} !~ /\S/ ) {
170	$borrower{$key} = $defaults{$key};
171	}
172	}
173	}
174	else {
175	# MJR: try to recover gracefully by using default values
176	foreach my $key (@columnkeys) {
177	if ( defined( $csvkeycol{$key} ) and $columns[ $csvkeycol{$key} ] =~ /\S/ ) {
178	$borrower{$key} = $columns[ $csvkeycol{$key} ];
179	}
180	elsif ( $defaults{$key} ) {
181	$borrower{$key} = $defaults{$key};
182	}
183	elsif ( scalar grep { $key eq $_ } @criticals ) {
184
185	# a critical field is undefined
186	push @missing_criticals, { key => $key, line => $., lineraw => $borrowerline };
187	}
188	else {
189	$borrower{$key} = '';
190	}
191	}
192	}
193
194	#warn join(':',%borrower);
195	if ( $borrower{categorycode} ) {
196	push @missing_criticals,
197	{
198	key => 'categorycode',
199	line => $.,
200	lineraw => $borrowerline,
201	value => $borrower{categorycode},
202	category_map => 1
203	}
204	unless GetBorrowercategory( $borrower{categorycode} );
205	}
206	else {
207	push @missing_criticals, { key => 'categorycode', line => $., lineraw => $borrowerline };
208	}
209	if ( $borrower{branchcode} ) {
210	push @missing_criticals,
211	{
212	key => 'branchcode',
213	line => $.,
214	lineraw => $borrowerline,
215	value => $borrower{branchcode},
216	branch_map => 1
217	}
218	unless GetBranchName( $borrower{branchcode} );
219	}	122	}
220	else {	123	);
221	push @missing_criticals, { key => 'branchcode', line => $., lineraw => $borrowerline };
222	}
223	if (@missing_criticals) {
224	foreach (@missing_criticals) {
225	$_->{borrowernumber} = $borrower{borrowernumber} \|\| 'UNDEF';
226	$_->{surname} = $borrower{surname} \|\| 'UNDEF';
227	}
228	$invalid++;
229	( 25 > scalar @errors ) and push @errors, { missing_criticals => \@missing_criticals };
230
231	# The first 25 errors are enough. Keeping track of 30,000+ would destroy performance.
232	next LINE;
233	}
234	if ($extended) {
235	my $attr_str = $borrower{patron_attributes};
236	$attr_str =~ s/\xe2\x80\x9c/"/g; # fixup double quotes in case we are passed smart quotes
237	$attr_str =~ s/\xe2\x80\x9d/"/g;
238	push @feedback,
239	{ feedback => 1, name => 'attribute string', value => $attr_str, filename => $uploadborrowers };
240	delete $borrower{patron_attributes}
241	; # not really a field in borrowers, so we don't want to pass it to ModMember.
242	$patron_attributes = extended_attributes_code_value_arrayref($attr_str);
243	}
244
245	# Popular spreadsheet applications make it difficult to force date outputs to be zero-padded, but we require it.
246	foreach (qw(dateofbirth dateenrolled dateexpiry)) {
247	my $tempdate = $borrower{$_} or next;
248	$tempdate =
249	eval { output_pref( { dt => dt_from_string($tempdate), dateonly => 1, dateformat => 'iso' } ); };
250	if ($tempdate) {
251	$borrower{$_} = $tempdate;
252	}
253	else {
254	$borrower{$_} = '';
255	push @missing_criticals, { key => $_, line => $., lineraw => $borrowerline, bad_date => 1 };
256	}
257	}
258	$borrower{dateenrolled} = $today_iso unless $borrower{dateenrolled};
259	$borrower{dateexpiry} = GetExpiryDate( $borrower{categorycode}, $borrower{dateenrolled} )
260	unless $borrower{dateexpiry};
261	my $borrowernumber;
262	my $member;
263	if ( ( $matchpoint eq 'cardnumber' ) && ( $borrower{'cardnumber'} ) ) {
264	$member = GetMember( 'cardnumber' => $borrower{'cardnumber'} );
265	if ($member) {
266	$borrowernumber = $member->{'borrowernumber'};
267	}
268	}
269	elsif ( ( $matchpoint eq 'userid' ) && ( $borrower{'userid'} ) ) {
270	$member = GetMember( 'userid' => $borrower{'userid'} );
271	if ($member) {
272	$borrowernumber = $member->{'borrowernumber'};
273	}
274	}
275	elsif ($extended) {
276	if ( defined($matchpoint_attr_type) ) {
277	foreach my $attr (@$patron_attributes) {
278	if ( $attr->{code} eq $matchpoint and $attr->{value} ne '' ) {
279	my @borrowernumbers = $matchpoint_attr_type->get_patrons( $attr->{value} );
280	$borrowernumber = $borrowernumbers[0] if scalar(@borrowernumbers) == 1;
281	last;
282	}
283	}
284	}
285	}
286
287	if ( C4::Members::checkcardnumber( $borrower{cardnumber}, $borrowernumber ) ) {
288	push @errors,
289	{
290	invalid_cardnumber => 1,
291	borrowernumber => $borrowernumber,
292	cardnumber => $borrower{cardnumber}
293	};
294	$invalid++;
295	next;
296	}
297
298	if ($borrowernumber) {
299
300	# borrower exists
301	unless ($overwrite_cardnumber) {
302	$alreadyindb++;
303	$template->param( 'lastalreadyindb' => $borrower{'surname'} . ' / ' . $borrowernumber );
304	next LINE;
305	}
306	$borrower{'borrowernumber'} = $borrowernumber;
307	for my $col ( keys %borrower ) {
308
309	# use values from extant patron unless our csv file includes this column or we provided a default.
310	# FIXME : You cannot update a field with a perl-evaluated false value using the defaults.
311
312	# The password is always encrypted, skip it!
313	next if $col eq 'password';
314
315	unless ( exists( $csvkeycol{$col} ) \|\| $defaults{$col} ) {
316	$borrower{$col} = $member->{$col} if ( $member->{$col} );
317	}
318	}
319	unless ( ModMember(%borrower) ) {
320	$invalid++;
321
322	# until we have better error trapping, we have no way of knowing why ModMember errored out...
323	push @errors, { unknown_error => 1 };
324	$template->param( 'lastinvalid' => $borrower{'surname'} . ' / ' . $borrowernumber );
325	next LINE;
326	}
327
328	# Don't add a new restriction if the existing 'combined' restriction matches this one
329	if ( $borrower{debarred} && ( ( $borrower{debarred} ne $member->{debarred} ) \|\| ( $borrower{debarredcomment} ne $member->{debarredcomment} ) ) ) {
330	# Check to see if this debarment already exists
331	my $debarrments = GetDebarments(
332	{
333	borrowernumber => $borrowernumber,
334	expiration => $borrower{debarred},
335	comment => $borrower{debarredcomment}
336	}
337	);
338
339	# If it doesn't, then add it!
340	unless (@$debarrments) {
341	AddDebarment(
342	{
343	borrowernumber => $borrowernumber,
344	expiration => $borrower{debarred},
345	comment => $borrower{debarredcomment}
346	}
347	);
348	}
349	}
350
351	if ($extended) {
352	if ($ext_preserve) {
353	my $old_attributes = GetBorrowerAttributes($borrowernumber);
354	$patron_attributes = extended_attributes_merge( $old_attributes, $patron_attributes )
355	; #TODO: expose repeatable options in template
356	}
357
358	push @errors, { unknown_error => 1 }
359	unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes );
360	}
361	$overwritten++;
362	$template->param( 'lastoverwritten' => $borrower{'surname'} . ' / ' . $borrowernumber );
363	}
364	else {
365	# FIXME: fixup_cardnumber says to lock table, but the web interface doesn't so this doesn't either.
366	# At least this is closer to AddMember than in members/memberentry.pl
367	if ( !$borrower{'cardnumber'} ) {
368	$borrower{'cardnumber'} = fixup_cardnumber(undef);
369	}
370	if ( $borrowernumber = AddMember(%borrower) ) {
371		124
372	if ( $borrower{debarred} ) {	125	my $feedback = $return->{feedback};
373	AddDebarment(	126	my $errors = $return->{errors};
374	{	127	my $imported = $return->{imported};
375	borrowernumber => $borrowernumber,	128	my $overwritten = $return->{overwritten};
376	expiration => $borrower{debarred},	129	my $alreadyindb = $return->{already_in_db};
377	comment => $borrower{debarredcomment}	130	my $invalid = $return->{invalid};
378	}
379	);
380	}
381		131
382	if ($extended) {	132	my $uploadinfo = $input->uploadInfo($uploadborrowers);
383	SetBorrowerAttributes( $borrowernumber, $patron_attributes );	133	foreach ( keys %$uploadinfo ) {
384	}	134	push @$feedback, { feedback => 1, name => $_, value => $uploadinfo->{$_}, $_ => $uploadinfo->{$_} };
		135	}
385		136
386	if ($set_messaging_prefs) {	137	push @$feedback, { feedback => 1, name => 'filename', value => $uploadborrowers, filename => $uploadborrowers };
387	C4::Members::Messaging::SetMessagingPreferencesFromDefaults(
388	{
389	borrowernumber => $borrowernumber,
390	categorycode => $borrower{categorycode}
391	}
392	);
393	}
394		138
395	$imported++;
396	$template->param( 'lastimported' => $borrower{'surname'} . ' / ' . $borrowernumber );
397	}
398	else {
399	$invalid++;
400	push @errors, { unknown_error => 1 };
401	$template->param( 'lastinvalid' => $borrower{'surname'} . ' / AddMember' );
402	}
403	}
404	}
405	(@errors) and $template->param( ERRORS => \@errors );
406	(@feedback) and $template->param( FEEDBACK => \@feedback );
407	$template->param(	139	$template->param(
408	'uploadborrowers' => 1,	140	uploadborrowers => 1,
409	'imported' => $imported,	141	errors => $errors,
410	'overwritten' => $overwritten,	142	feedback => $feedback,
411	'alreadyindb' => $alreadyindb,	143	imported => $imported,
412	'invalid' => $invalid,	144	overwritten => $overwritten,
413	'total' => $imported + $alreadyindb + $invalid + $overwritten,	145	alreadyindb => $alreadyindb,
		146	invalid => $invalid,
		147	total => $imported + $alreadyindb + $invalid + $overwritten,
414	);	148	);
415		149
416	}	150	}
417	-