@@ -, +, @@ --- C4/EDI.pm | 1086 ++++++++++++++++++++ acqui/basket.pl | 12 + admin/edi-accounts.pl | 72 ++ admin/edi-edit.pl | 75 ++ installer/data/mysql/atomicupdate/edifact.sql | 27 + .../data/mysql/de-DE/mandatory/userpermissions.sql | 1 + .../data/mysql/en/mandatory/userpermissions.sql | 1 + .../data/mysql/es-ES/mandatory/userpermissions.sql | 1 + .../mysql/fr-FR/1-Obligatoire/userpermissions.sql | 1 + .../data/mysql/it-IT/necessari/userpermissions.sql | 1 + installer/data/mysql/kohastructure.sql | 29 + .../mysql/nb-NO/1-Obligatorisk/userpermissions.sql | 1 + .../data/mysql/pl-PL/mandatory/userpermissions.sql | 1 + .../ru-RU/mandatory/permissions_and_user_flags.sql | 1 + installer/data/mysql/sysprefs.sql | 1 + .../uk-UA/mandatory/permissions_and_user_flags.sql | 1 + installer/data/mysql/updatedatabase.pl | 47 + .../intranet-tmpl/prog/en/includes/admin-menu.inc | 1 + .../intranet-tmpl/prog/en/includes/tools-menu.inc | 5 +- .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 11 +- .../prog/en/modules/admin/admin-home.tt | 2 + .../prog/en/modules/admin/edi-accounts.tt | 46 + .../prog/en/modules/admin/edi-edit.tt | 102 ++ .../intranet-tmpl/prog/en/modules/tools/edi.tt | 63 ++ .../prog/en/modules/tools/tools-home.tt | 5 + misc/cronjobs/clean_edifiles.pl | 43 + misc/cronjobs/edifact_order_ftp_transfer.pl | 127 +++ misc/cronjobs/send_queued_edi_orders.pl | 26 + misc/edi_files/README.txt | 3 + t/Calendar.t | 1 - t/EDI.t | 85 ++ tools/edi.pl | 43 + 32 files changed, 1918 insertions(+), 3 deletions(-) create mode 100644 C4/EDI.pm create mode 100755 admin/edi-accounts.pl create mode 100755 admin/edi-edit.pl create mode 100644 installer/data/mysql/atomicupdate/edifact.sql create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-accounts.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-edit.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/tools/edi.tt create mode 100755 misc/cronjobs/clean_edifiles.pl create mode 100755 misc/cronjobs/edifact_order_ftp_transfer.pl create mode 100755 misc/cronjobs/send_queued_edi_orders.pl create mode 100644 misc/edi_files/README.txt create mode 100644 t/EDI.t create mode 100755 tools/edi.pl --- a/C4/EDI.pm +++ a/C4/EDI.pm @@ -0,0 +1,1086 @@ +package C4::EDI; + +# Copyright 2011 Mark Gavillet +# +# 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 2 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 strict; +use warnings; +use C4::Context; +use C4::Acquisition; +use C4::Budgets qw( GetCurrency ); +use Net::FTP; +use Business::Edifact::Interchange; +use C4::Biblio; +use C4::Items; +use Business::ISBN; +use Carp; +use parent qw(Exporter); + +our $VERSION = 3.09.00.53; +our @EXPORT_OK = qw( + GetEDIAccounts + GetEDIAccountDetails + CreateEDIDetails + UpdateEDIDetails + LogEDIFactOrder + LogEDIFactQuote + DeleteEDIDetails + GetVendorList + GetEDIfactMessageList + GetEDIFTPAccounts + LogEDITransaction + GetVendorSAN + CreateEDIOrder + SendEDIOrder + SendQueuedEDIOrders + ParseEDIQuote + GetDiscountedPrice + GetBudgetID + CheckOrderItemExists + GetBranchCode + string35escape + GetOrderItemInfo + CheckVendorFTPAccountExists +); + +=head1 NAME + +C4::EDI - Perl Module containing functions for Vendor EDI accounts and EDIfact messages + +=head1 VERSION + +Version 0.01 + +=head1 SYNOPSIS + +use C4::EDI; + +=head1 DESCRIPTION + +This module contains routines for adding, modifying and deleting EDI account details for vendors, interacting with vendor FTP sites to send/retrieve quote and order messages, formatting EDIfact orders, and parsing EDIfact quotes to baskets + +=head2 GetVendorList + +Returns a list of vendors from aqbooksellers to populate drop down select menu + +=cut + +sub GetVendorList { + my $dbh = C4::Context->dbh; + my $sth; + $sth = + $dbh->prepare('select id, name from aqbooksellers order by name asc'); + $sth->execute(); + my $vendorlist = $sth->fetchall_arrayref( {} ); + return $vendorlist; +} + +=head2 CreateEDIDetails + +Inserts a new EDI vendor FTP account + +=cut + +sub CreateEDIDetails { + my ( $provider, $description, $host, $user, $pass, $in_dir, $san ) = @_; + my $dbh = C4::Context->dbh; + my $sth; + if ($provider) { + $sth = $dbh->prepare( +'insert into vendor_edi_accounts (description, host, username, password, provider, in_dir, san) values (?,?,?,?,?,?,?)' + ); + $sth->execute( $description, $host, $user, + $pass, $provider, $in_dir, $san ); + } + return; +} + +=head2 GetEDIAccounts + +Returns all vendor FTP accounts + +=cut + +sub GetEDIAccounts { + my $dbh = C4::Context->dbh; + my $sth; + $sth = $dbh->prepare( +'select vendor_edi_accounts.id, aqbooksellers.id as providerid, aqbooksellers.name as vendor, vendor_edi_accounts.description, vendor_edi_accounts.last_activity from vendor_edi_accounts inner join aqbooksellers on vendor_edi_accounts.provider = aqbooksellers.id order by aqbooksellers.name asc' + ); + $sth->execute(); + my $ediaccounts = $sth->fetchall_arrayref( {} ); + return $ediaccounts; +} + +=head2 DeleteEDIDetails + +Remove a vendor's FTP account + +=cut + +sub DeleteEDIDetails { + my ($id) = @_; + my $dbh = C4::Context->dbh; + my $sth; + if ($id) { + $sth = $dbh->prepare('delete from vendor_edi_accounts where id=?'); + $sth->execute($id); + } + return; +} + +=head2 UpdateEDIDetails + +Update a vendor's FTP account + +=cut + +sub UpdateEDIDetails { + my ( $editid, $description, $host, $user, $pass, $provider, $in_dir, $san ) + = @_; + my $dbh = C4::Context->dbh; + if ($editid) { + my $sth = $dbh->prepare( +'update vendor_edi_accounts set description=?, host=?, username=?, password=?, provider=?, in_dir=?, san=? where id=?' + ); + $sth->execute( $description, $host, $user, $pass, $provider, $in_dir, + $san, $editid ); + } + return; +} + +=head2 LogEDIFactOrder + +Updates or inserts to the edifact_messages table when processing an order and assigns a status and basket number + +=cut + +sub LogEDIFactOrder { + my ( $provider, $status, $basketno ) = @_; + my $dbh = C4::Context->dbh; + my $key; + my ( $sec, $min, $hour, $mday, $mon, $year ) = localtime(time); + my $date_sent = sprintf '%4d-%02d-%0d', $year + 1900, $mon + 1, $mday; + my $sth = $dbh->prepare( +'select edifact_messages.id from edifact_messages where basketno=? and provider=?' + ); + $sth->execute( $basketno, $provider ); + + while ( my @row = $sth->fetchrow_array() ) { + $key = $row[0]; + } + if ($key) { + $sth = $dbh->prepare( +'update edifact_messages set date_sent=?, status=? where edifact_messages.id=?' + ); + $sth->execute( $date_sent, $status, $key ); + } + else { + $sth = $dbh->prepare( +'insert into edifact_messages (message_type,date_sent,provider,status,basketno) values (?,?,?,?,?)' + ); + $sth->execute( 'ORDER', $date_sent, $provider, $status, $basketno ); + } + return; +} + +=head2 LogEDIFactOrder + +Updates or inserts to the edifact_messages table when processing a quote and assigns a status and basket number + +=cut + +sub LogEDIFactQuote { + my ( $provider, $status, $basketno, $key ) = @_; + my $dbh = C4::Context->dbh; + my $sth; + my ( $sec, $min, $hour, $mday, $mon, $year ) = localtime(time); + my $date_sent = sprintf '%4d-%02d-%0d', $year + 1900, $mon + 1, $mday; + if ( $key != 0 ) { + $sth = $dbh->prepare( +'update edifact_messages set date_sent=?, status=?, basketno=? where edifact_messages.id=?' + ); + $sth->execute( $date_sent, $status, $basketno, $key ); + } + else { + $sth = $dbh->prepare( +'insert into edifact_messages (message_type,date_sent,provider,status,basketno) values (?,?,?,?,?)' + ); + $sth->execute( 'QUOTE', $date_sent, $provider, $status, $basketno ); + $key = + $dbh->last_insert_id( undef, undef, qw(edifact_messages id), undef ); + } + return $key; +} + +=head2 GetEDIAccountDetails + +Returns FTP account details for a given vendor + +=cut + +sub GetEDIAccountDetails { + my ($id) = @_; + my $dbh = C4::Context->dbh; + my $sth; + if ($id) { + $sth = $dbh->prepare('select * from vendor_edi_accounts where id=?'); + $sth->execute($id); + return $sth->fetchrow_hashref; + } + return; +} + +=head2 GetEDIfactMessageList + +Returns a list of edifact_messages that have been processed, including the type (quote/order) and status + +=cut + +sub GetEDIfactMessageList { + my $dbh = C4::Context->dbh; + my $sth; + $sth = $dbh->prepare( +q|select edifact_messages.id, edifact_messages.message_type, DATE_FORMAT(edifact_messages.date_sent,'%d/%m/%Y') as date_sent, aqbooksellers.id as providerid, aqbooksellers.name as providername, edifact_messages.status, edifact_messages.basketno from edifact_messages inner join aqbooksellers on edifact_messages.provider = aqbooksellers.id order by edifact_messages.date_sent desc, edifact_messages.id desc| + ); + $sth->execute(); + return $sth->fetchall_arrayref( {} ); +} + +=head2 GetEDIFTPAccounts + +Returns all vendor FTP accounts. Used when retrieving quotes messages overnight + +=cut + +sub GetEDIFTPAccounts { + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( +'select id, host, username, password, provider, in_dir from vendor_edi_accounts order by id asc' + ); + $sth->execute(); + return $sth->fetchall_arrayref( {} ); +} + +=head2 LogEDITransaction + +Updates the timestamp for a given vendor FTP account whenever there is activity + +=cut + +sub LogEDITransaction { + my $id = shift; + my ( $sec, $min, $hour, $mday, $mon, $year ) = localtime(time); + my $datestamp = sprintf '%4d/%02d/%0d', $year + 1900, $mon + 1, $mday; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( + 'update vendor_edi_accounts set last_activity=? where id=?'); + $sth->execute( $datestamp, $id ); + return; +} + +=head2 GetVendorSAN + +Returns the stored SAN number for a given vendor + +=cut + +sub GetVendorSAN { + my $booksellerid = shift; + my $dbh = C4::Context->dbh; + my $san; + my $sth = + $dbh->prepare('select san from vendor_edi_accounts where provider=?'); + $sth->execute($booksellerid); + while ( my @result = $sth->fetchrow_array() ) { + $san = $result[0]; + } + return $san; +} + +=head2 CreateEDIOrder + +Formats an EDIfact order message from a given basket and stores as a file on the server + +=cut + +sub CreateEDIOrder { + my ( $basketno, $booksellerid ) = @_; + my @datetime = localtime(time); + my $longyear = $datetime[5] + 1900; + my $shortyear = sprintf '%02d', $datetime[5] - 100; + my $date = sprintf '%02d%02d', $datetime[4] + 1, $datetime[3]; + my $hourmin = sprintf '%02d%02d', $datetime[2], $datetime[1]; + my $year = $datetime[5] - 100; + my $month = sprintf '%02d', $datetime[4] + 1; + my $linecount = 0; + my $filename = "ediorder_$basketno.CEP"; + my $exchange = int( rand(99999999999999) ); + my $ref = int( rand(99999999999999) ); + my $san = GetVendorSAN($booksellerid); + my $message_type = GetMessageType($basketno); + my $output_file = C4::Context->config('intranetdir'); + + # Currencies must be the 3 upper case alpha codes + # Koha soes not currently enforce this + my $default_currency = GetCurrency(); + if ( $default_currency->{currency} =~ m/^[[:upper:]]{3}$/ ) { + $default_currency = $default_currency->{currency}; + } + else { + $default_currency = 'GBP'; + } + + $output_file .= "/misc/edi_files/$filename"; + + open my $fh, '>', $output_file + or croak "Unable to create $output_file : $!"; + + print $fh q{UNA:+.? '}; # print opening header + print $fh q{UNB+UNOC:2+} + . C4::Context->preference("EDIfactEAN") + . ":14+$san:31B+$shortyear$date:$hourmin+" + . $exchange + . "++ORDERS+++EANCOM'" + ; # print identifying EANs/SANs, date/time, exchange reference number + print $fh 'UNH+' . $ref + . q{+ORDERS:D:96A:UN:EAN008'}; # print message reference number + + if ( $message_type eq 'QUOTE' ) { + print $fh 'BGM+22V+' + . $basketno + . q{+9'}; # print order number and quote confirmation ref + } + else { + print $fh 'BGM+220+', $basketno, "+9'"; # print order number + } + print $fh "DTM+137:$longyear$date:102'"; # print date of message + print $fh "NAD+BY+" + . C4::Context->preference("EDIfactEAN") + . "::9'"; # print buyer EAN + print $fh 'NAD+SU+', $san, "::31B'"; # print vendor SAN + print $fh 'NAD+SU+', $booksellerid, "::92'"; # print internal ID + + # get items from basket + my @results = GetOrders($basketno); + foreach my $item (@results) { + $linecount++; + my $price; + my $title = string35escape( escape( $item->{title} ) ); + my $author = string35escape( escape( $item->{author} ) ); + my $publisher = string35escape( escape( $item->{publishercode} ) ); + $price = sprintf '%.2f', $item->{listprice}; + my $isbn; + if ( length( $item->{isbn} ) == 10 + || substr( $item->{isbn}, 0, 3 ) eq '978' + || index( $item->{isbn}, '|' ) != -1 ) + { + $isbn = cleanisbn( $item->{isbn} ); + $isbn = Business::ISBN->new($isbn); + if ($isbn) { + if ( $isbn->is_valid ) { + $isbn = ( $isbn->as_isbn13 )->isbn; + } + else { + $isbn = '0'; + } + } + else { + $isbn = 0; + } + } + else { + $isbn = $item->{isbn}; + } + my $copyrightdate = escape( $item->{publicationyear} ); + my $quantity = escape( $item->{quantity} ); + my $ordernumber = escape( $item->{ordernumber} ); + my $notes; + if ( $item->{notes} ) { + $notes = $item->{notes}; + $notes =~ s/[\r\n]+//g; + $notes = string35escape( escape($notes) ); + } + + my ( $branchcode, $callnumber, $itype, $lsqccode, $fund ) = + GetOrderItemInfo( $item->{'ordernumber'} ); + $callnumber = escape($callnumber); + + print $fh "LIN+$linecount++" . $isbn . ":EN'"; # line number, isbn + print $fh "PIA+5+" . $isbn + . ":IB'"; # isbn as main product identification + print $fh "IMD+L+050+:::$title'"; # title + print $fh "IMD+L+009+:::$author'"; # full name of author + print $fh "IMD+L+109+:::$publisher'"; # publisher + print $fh "IMD+L+170+:::$copyrightdate'"; # date of publication + print $fh "IMD+L+220+:::O'"; # binding (e.g. PB) (O if not specified) + if ( $callnumber ne '' ) { + print $fh "IMD+L+230+:::$callnumber'"; # shelfmark + } + print $fh "QTY+21:$quantity'"; # quantity + if ( $message_type ne 'QUOTE' && $quantity > 1 ) { + print $fh "GIR+001+$quantity:LQT+$branchcode:LLO+$fund:LFN+" + ; # branchcode, fund code + } + else { + print $fh + "GIR+001+$branchcode:LLO+$fund:LFN+"; # branchcode, fund code + } + if ( $callnumber ne '' ) { + print $fh "$callnumber:LCL+"; # shelfmark + } + print $fh $itype . ":LST+$lsqccode:LSQ'"; # stock category, sequence + if ($notes) { + print $fh "FTX+LIN+++:::$notes'"; + } + ###REQUEST ORDERS TO REVISIT +#if ($message_type ne 'QUOTE') +#{ +# print $fh "FTX+LIN++$linecount:10B:28'"; # freetext ** used for request orders to denote priority (to revisit) +#} + print $fh "PRI+AAB:$price'"; # price per item + my $currency = + $item->{currency} =~ m/^[[:upper:]]{3}$/ + ? $item->{currency} + : $default_currency; + print $fh "CUX+2:$currency:9'"; # currency (e.g. GBP, EUR, USD) + print $fh "RFF+LI:$ordernumber'"; # Local order number + if ( $message_type eq 'QUOTE' ) { + print $fh "RFF+QLI:" + . $item->{booksellerinvoicenumber} + . q{'}; # If QUOTE confirmation, include booksellerinvoicenumber + } + } + print $fh "UNS+S'"; # print summary section header + print $fh "CNT+2:$linecount'"; # print number of line items in the message + my $segments = ( ( $linecount * 13 ) + 9 ); + print $fh "UNT+$segments+" + . $ref . "'" + ; # No. of segments in message (UNH+UNT elements included, UNA, UNB, UNZ excluded) + # Message ref number + print $fh "UNZ+1+" . $exchange . "'\n"; # Exchange ref number + + close $fh; + + LogEDIFactOrder( $booksellerid, 'Queued', $basketno ); + + return $filename; + +} + +sub GetMessageType { + my $basketno = shift; + my $dbh = C4::Context->dbh; + my $sth; + my $message_type; + my @row; + $sth = $dbh->prepare( + 'select message_type from edifact_messages where basketno=?'); + $sth->execute($basketno); + while ( @row = $sth->fetchrow_array() ) { + $message_type = $row[0]; + } + return $message_type; +} + +sub cleanisbn { + my $isbn = shift; + if ($isbn) { + my $i = index( $isbn, '(' ); + if ( $i > 1 ) { + $isbn = substr( $isbn, 0, ( $i - 1 ) ); + } + if ( $isbn =~ /\|/ ) { + my @isbns = split( /\|/, $isbn ); + $isbn = $isbns[0]; + } + $isbn = escape($isbn); + $isbn =~ s/^\s+//; + $isbn =~ s/\s+$//; + return $isbn; + } + return; +} + +sub escape { + my $string = shift; + if ($string) { + $string =~ s/\?/\?\?/g; + $string =~ s/\'/\?\'/g; + $string =~ s/\:/\?\:/g; + $string =~ s/\+/\?\+/g; + return $string; + } + return; +} + +=head2 GetBranchCode + +Return branchcode for an order when formatting an EDIfact order message + +=cut + +sub GetBranchCode { + my $biblioitemnumber = shift; + my $dbh = C4::Context->dbh; + my $branchcode; + my @row; + my $sth = + $dbh->prepare("select homebranch from items where biblioitemnumber=?"); + $sth->execute($biblioitemnumber); + while ( @row = $sth->fetchrow_array() ) { + $branchcode = $row[0]; + } + return $branchcode; +} + +=head2 SendEDIOrder + +Transfers an EDIfact order message to the relevant vendor's FTP site + +=cut + +sub SendEDIOrder { + my ( $basketno, $booksellerid ) = @_; + my $newerr; + my $result; + + # check edi order file exists + my $edi_files = C4::Context->config('intranetdir'); + $edi_files .= '/misc/edi_files/'; + if ( -e "${edi_files}ediorder_$basketno.CEP" ) { + my $dbh = C4::Context->dbh; + my $sth; + $sth = $dbh->prepare( +"select id, host, username, password, provider, in_dir from vendor_edi_accounts where provider=?" + ); + $sth->execute($booksellerid); + my $ftpaccount = $sth->fetchrow_hashref; + + #check vendor edi account exists + if ($ftpaccount) { + + # connect to ftp account + my $ftp = Net::FTP->new( $ftpaccount->{host}, Timeout => 10 ) + or $newerr = 1; + if ( !$newerr ) { + $newerr = 0; + + # login + $ftp->login( $ftpaccount->{username}, $ftpaccount->{password} ) + or $newerr = 1; + $ftp->quit if $newerr; + if ( !$newerr ) { + + # cd to directory + $ftp->cwd( $ftpaccount->{in_dir} ) or $newerr = 1; + $ftp->quit if $newerr; + + # put file + if ( !$newerr ) { + $newerr = 0; + $ftp->put("${edi_files}ediorder_$basketno.CEP") + or $newerr = 1; + $ftp->quit if $newerr; + if ( !$newerr ) { + $result = +"File: ediorder_$basketno.CEP transferred successfully"; + $ftp->quit; + unlink "${edi_files}ediorder_$basketno.CEP"; + LogEDITransaction( $ftpaccount->{id} ); + LogEDIFactOrder( $booksellerid, 'Sent', $basketno ); + return $result; + } + else { + $result = +"Could not transfer the file ${edi_files}ediorder_$basketno.CEP to $ftpaccount->{host}: $_"; + FTPError($result); + LogEDIFactOrder( $booksellerid, 'Failed', + $basketno ); + return $result; + } + } + else { + $result = +"Cannot get remote directory ($ftpaccount->{in_dir}) on $ftpaccount->{host}"; + FTPError($result); + LogEDIFactOrder( $booksellerid, 'Failed', $basketno ); + return $result; + } + } + else { + $result = "Cannot log in to $ftpaccount->{host}: $!"; + FTPError($result); + LogEDIFactOrder( $booksellerid, 'Failed', $basketno ); + return $result; + } + } + else { + $result = + "Cannot make an FTP connection to $ftpaccount->{host}: $!"; + FTPError($result); + LogEDIFactOrder( $booksellerid, 'Failed', $basketno ); + return $result; + } + } + else { + $result = +"Vendor ID: $booksellerid does not have a current EDIfact FTP account"; + FTPError($result); + LogEDIFactOrder( $booksellerid, 'Failed', $basketno ); + return $result; + } + } + else { + $result = 'There is no EDIfact order for this basket'; + return $result; + } +} + +sub FTPError { + my $error = shift; + my $log_file = C4::Context->config('intranetdir'); + $log_file .= '/misc/edi_files/edi_ftp_error.log'; + open my $log_fh, '>>', $log_file + or croak "Could not open $log_file: $!"; + my ( $sec, $min, $hour, $mday, $mon, $year ) = localtime(time); + printf $log_fh "%4d-%02d-%02d %02d:%02d:%02d\n-----\n", + $year + 1900, $mon + 1, $mday, $hour, $min, $sec; + print $log_fh "$error\n\n"; + close $log_fh; + return; +} + +=head2 SendQueuedEDIOrders + +Sends all EDIfact orders that are held in the Queued + +=cut + +sub SendQueuedEDIOrders { + my $dbh = C4::Context->dbh; + my @orders; + my $sth = $dbh->prepare( + q|select basketno, provider from edifact_messages where status='Queued'| + ); + $sth->execute(); + while ( @orders = $sth->fetchrow_array() ) { + SendEDIOrder( $orders[0], $orders[1] ); + } + return; +} + +=head2 ParseEDIQuote + +Uses Business::Edifact::Interchange to parse a stored EDIfact quote message, creates basket, biblios, biblioitems, and items + +=cut + +sub ParseEDIQuote { + my ( $filename, $booksellerid ) = @_; + my $basketno; + my $ParseEDIQuoteItem; + + my $edi = Business::Edifact::Interchange->new; + my $path = C4::Context->config('intranetdir'); + $path .= '/misc/edi_files/'; + $edi->parse_file("$path$filename"); + my $messages = $edi->messages(); + my $msg_cnt = @{$messages}; + + # create default edifact_messages entry + my $messagekey = LogEDIFactQuote( $booksellerid, 'Failed', 0, 0 ); + + #create basket + if ( $msg_cnt > 0 && $booksellerid ) { + $basketno = NewBasket( $booksellerid, 0, $filename, q{}, q{}, q{} ); + } + + $ParseEDIQuoteItem = sub { + my ( $item, $gir, $bookseller_id ) = @_; + my $relnos = $item->{related_numbers}; + my $author = $item->author_surname . ", " . $item->author_firstname; + + my $ecost = + GetDiscountedPrice( $bookseller_id, $item->{price}->{price} ); + + my $ftxlin; + my $ftxlno; + if ( $item->{free_text}->{qualifier} eq 'LIN' ) { + $ftxlin = $item->{free_text}->{text}; + } + if ( $item->{free_text}->{qualifier} eq 'LNO' ) { + $ftxlno = $item->{free_text}->{text}; + } + + my ( $llo, $lfn, $lsq, $lst, $lfs, $lcl, $id ); + my $relcount = 0; + foreach my $rel ( @{$relnos} ) { + if ( $rel->{id} == ( $gir + 1 ) ) { + if ( $item->{related_numbers}->[$relcount]->{LLO}->[0] ) { + $llo = $item->{related_numbers}->[$relcount]->{LLO}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{LFN}->[0] ) { + $lfn = $item->{related_numbers}->[$relcount]->{LFN}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{LSQ}->[0] ) { + $lsq = $item->{related_numbers}->[$relcount]->{LSQ}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{LST}->[0] ) { + $lst = $item->{related_numbers}->[$relcount]->{LST}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{LFS}->[0] ) { + $lfs = $item->{related_numbers}->[$relcount]->{LFS}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{LCL}->[0] ) { + $lcl = $item->{related_numbers}->[$relcount]->{LCL}->[0]; + } + if ( $item->{related_numbers}->[$relcount]->{id} ) { + $id = $item->{related_numbers}->[$relcount]->{id}; + } + } + $relcount++; + } + + my $lclnote; + if ( !$lst ) { + $lst = uc( $item->item_format ); + } + if ( !$lcl ) { + $lcl = $item->shelfmark; + } + else { + ( $lcl, $lclnote ) = DawsonsLCL($lcl); + } + if ($lfs) { + $lcl .= " $lfs"; + } + + my $budget_id = GetBudgetID($lfn); + + #Uncomment section below to define a default budget_id if there is no match + #if (!defined $budget_id) + #{ + # $budget_id=0; + #} + + # create biblio record + my $bib_record = TransformKohaToMarc( + { + 'biblio.title' => $item->title, + 'biblio.author' => $author ? $author : q{}, + 'biblio.seriestitle' => q{}, + 'biblioitems.isbn' => $item->{item_number} + ? $item->{item_number} + : q{}, + 'biblioitems.publishercode' => $item->publisher + ? $item->publisher + : q{}, + 'biblioitems.publicationyear' => $item->date_of_publication + ? $item->date_of_publication + : q{}, + 'biblio.copyrightdate' => $item->date_of_publication + ? $item->date_of_publication + : q{}, + 'biblioitems.itemtype' => uc( $item->item_format ), + 'biblioitems.cn_source' => 'ddc', + 'items.cn_source' => 'ddc', + 'items.notforloan' => -1, + + #"items.ccode" => $lsq, + 'items.location' => $lsq, + 'items.homebranch' => $llo, + 'items.holdingbranch' => $llo, + 'items.booksellerid' => $bookseller_id, + 'items.price' => $item->{price}->{price}, + 'items.replacementprice' => $item->{price}->{price}, + 'items.itemcallnumber' => $lcl, + 'items.itype' => $lst, + 'items.cn_sort' => q{}, + } + ); + + #check if item already exists in catalogue + my $biblionumber; + my $bibitemnumber; + ( $biblionumber, $bibitemnumber ) = + CheckOrderItemExists( $item->{item_number} ); + + if ( !defined $biblionumber ) { + + # create the record in catalogue, with framework '' + ( $biblionumber, $bibitemnumber ) = AddBiblio( $bib_record, q{} ); + } + + my $ordernote; + if ($lclnote) { + $ordernote = $lclnote; + } + if ($ftxlno) { + $ordernote = $ftxlno; + } + if ($ftxlin) { + $ordernote = $ftxlin; + } + + my %orderinfo = ( + basketno => $basketno, + ordernumber => q{}, + subscription => 'no', + uncertainprice => 0, + biblionumber => $biblionumber, + title => $item->title, + quantity => 1, + biblioitemnumber => $bibitemnumber, + rrp => $item->{price}->{price}, + ecost => $ecost, + sort1 => q{}, + sort2 => q{}, + booksellerinvoicenumber => $item->{item_reference}[0][1], + listprice => $item->{price}->{price}, + branchcode => $llo, + budget_id => $budget_id, + notes => $ordernote, + ); + + my $orderinfo = \%orderinfo; + + my ( $retbasketno, $ordernumber ) = NewOrder($orderinfo); + + # now, add items if applicable + if ( C4::Context->preference('AcqCreateItem') eq 'ordering' ) { + my $itemnumber; + ( $biblionumber, $bibitemnumber, $itemnumber ) = + AddItemFromMarc( $bib_record, $biblionumber ); + NewOrderItem( $itemnumber, $ordernumber ); + } + }; + + for ( my $count = 0 ; $count < $msg_cnt ; $count++ ) { + my $items = $messages->[$count]->items(); + my $ref_num = $messages->[$count]->{ref_num}; + + foreach my $item ( @{$items} ) { + for ( my $i = 0 ; $i < $item->{quantity} ; $i++ ) { + &$ParseEDIQuoteItem( $item, $i, $booksellerid, $basketno ); + } + } + } + + # update edifact_messages entry + LogEDIFactQuote( $booksellerid, 'Received', $basketno, $messagekey ); + return 1; + +} + +=head2 GetDiscountedPrice + +Returns the discounted price for an order based on the discount rate for a given vendor + +=cut + +sub GetDiscountedPrice { + my ( $booksellerid, $price ) = @_; + my $dbh = C4::Context->dbh; + my $sth; + my @discount; + my $ecost; + my $percentage; + $sth = $dbh->prepare(q|select discount from aqbooksellers where id=?|); + $sth->execute($booksellerid); + + while ( @discount = $sth->fetchrow_array() ) { + $percentage = $discount[0]; + } + $ecost = ( $price - ( ( $percentage * $price ) / 100 ) ); + return $ecost; +} + +=head2 DawsonsLCL + +Checks for a call number encased by asterisks. If found, returns call number as $lcl and string with +asterisks as $lclnote to go into FTX field enabling spine label creation by Dawsons bookseller + +=cut + +sub DawsonsLCL { + my $lcl = shift; + my $lclnote; + my $f = index( $lcl, '*' ); + my $l = rindex( $lcl, '*' ); + if ( $f == 0 && $l == ( length($lcl) - 1 ) ) { + $lclnote = $lcl; + $lcl =~ s/\*//g; + } + return ( $lcl, $lclnote ); +} + +=head2 GetBudgetID + +Returns the budget_id for a given budget_code + +=cut + +sub GetBudgetID { + my $fundcode = shift; + my $dbh = C4::Context->dbh; + my @funds; + my $ecost; + my $budget_id; + my $sth = + $dbh->prepare('select budget_id from aqbudgets where budget_code=?'); + $sth->execute($fundcode); + + while ( @funds = $sth->fetchrow_array() ) { + $budget_id = $funds[0]; + } + return $budget_id; +} + +=head2 CheckOrderItemExists + +Checks to see if a biblio record already exists in the catalogue when parsing a quotes message +Converts 10-13 digit ISBNs and vice-versa if an initial match is not found + +=cut + +sub CheckOrderItemExists { + my $isbn = shift; + my $dbh = C4::Context->dbh; + my @matches; + my $biblionumber; + my $bibitemnumber; + my $sth = $dbh->prepare( + 'select biblionumber, biblioitemnumber from biblioitems where isbn=?'); + $sth->execute($isbn); + + while ( @matches = $sth->fetchrow_array() ) { + $biblionumber = $matches[0]; + $bibitemnumber = $matches[1]; + } + if ($biblionumber) { + return $biblionumber, $bibitemnumber; + } + else { + $isbn = cleanisbn($isbn); + if ( length($isbn) == 10 ) { + $isbn = Business::ISBN->new($isbn); + if ($isbn) { + if ( $isbn->is_valid ) { + $isbn = ( $isbn->as_isbn13 )->isbn; + $sth->execute($isbn); + while ( @matches = $sth->fetchrow_array() ) { + $biblionumber = $matches[0]; + $bibitemnumber = $matches[1]; + } + } + } + } + elsif ( length($isbn) == 13 ) { + $isbn = Business::ISBN->new($isbn); + if ($isbn) { + if ( $isbn->is_valid ) { + $isbn = ( $isbn->as_isbn10 )->isbn; + $sth->execute($isbn); + while ( @matches = $sth->fetchrow_array() ) { + $biblionumber = $matches[0]; + $bibitemnumber = $matches[1]; + } + } + } + } + return $biblionumber, $bibitemnumber; + } +} + +sub string35escape { + my $string = shift; + my $colon_string; + my @sections; + if ( length($string) > 35 ) { + my ( $chunk, $stringlength ) = ( 35, length($string) ); + for ( my $counter = 0 ; $counter < $stringlength ; $counter += $chunk ) + { + push @sections, substr( $string, $counter, $chunk ); + } + foreach my $section (@sections) { + $colon_string .= "$section:"; + } + chop $colon_string; + } + else { + $colon_string = $string; + } + return $colon_string; +} + +sub GetOrderItemInfo { + my $ordernumber = shift; + my $dbh = C4::Context->dbh; + my @rows; + my $homebranch; + my $callnumber; + my $itype; + my $ccode; + my $fund; + my $sth = $dbh->prepare( +q|select items.homebranch, items.itemcallnumber, items.itype, items.location from items + inner join aqorders_items on aqorders_items.itemnumber=items.itemnumber + where aqorders_items.ordernumber=?| + ); + $sth->execute($ordernumber); + + while ( @rows = $sth->fetchrow_array() ) { + $homebranch = $rows[0]; + $callnumber = $rows[1]; + $itype = $rows[2]; + $ccode = $rows[3]; + } + $sth = $dbh->prepare( + q|select aqbudgets.budget_code from aqbudgets inner join aqorders on + aqorders.budget_id=aqbudgets.budget_id where aqorders.ordernumber=?| + ); + $sth->execute($ordernumber); + while ( @rows = $sth->fetchrow_array() ) { + $fund = $rows[0]; + } + return $homebranch, $callnumber, $itype, $ccode, $fund; +} + +sub CheckVendorFTPAccountExists { + my $booksellerid = shift; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare( + q|select count(id) from vendor_edi_accounts where provider=?|); + $sth->execute($booksellerid); + while ( my @rows = $sth->fetchrow_array() ) { + if ( $rows[0] > 0 ) { + return 1; + } + } + return; +} + +1; + +__END__ + +=head1 AUTHOR + +Mark Gavillet + +=cut --- a/acqui/basket.pl +++ a/acqui/basket.pl @@ -28,6 +28,7 @@ use C4::Output; use CGI; use C4::Acquisition; use C4::Budgets; +use C4::EDI qw( CreateEDIOrder SendEDIOrder ); use C4::Bookseller qw( GetBookSellerFromId); use C4::Debug; use C4::Biblio; @@ -86,6 +87,8 @@ my $basket = GetBasket($basketno); # if no booksellerid in parameter, get it from basket # warn "=>".$basket->{booksellerid}; $booksellerid = $basket->{booksellerid} unless $booksellerid; +my $ediaccount = CheckVendorFTPAccountExists($booksellerid); +$template->param(ediaccount=>$ediaccount); my ($bookseller) = GetBookSellerFromId($booksellerid); my $op = $query->param('op'); if (!defined $op) { @@ -95,6 +98,15 @@ if (!defined $op) { my $confirm_pref= C4::Context->preference("BasketConfirmations") || '1'; $template->param( skip_confirm_reopen => 1) if $confirm_pref eq '2'; +if ( $op eq 'ediorder') { + my $edifile=CreateEDIOrder($basketno,$booksellerid); + $template->param(edifile => $edifile); +} +if ( $op eq 'edisend') { + my $edisend=SendEDIOrder($basketno,$booksellerid); + $template->param(edisend => $edisend); +} + if ( $op eq 'delete_confirm' ) { my $basketno = $query->param('basketno'); DelBasket($basketno); --- a/admin/edi-accounts.pl +++ a/admin/edi-accounts.pl @@ -0,0 +1,72 @@ +#!/usr/bin/perl + +# Copyright 2011,2012 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use CGI; +use C4::Auth; +use C4::Output; +use C4::EDI + qw( CreateEDIDetails UpdateEDIDetails GetEDIAccounts DeleteEDIDetails); + +my $input = CGI->new(); + +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => 'admin/edi-accounts.tmpl', + query => $input, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { borrowers => 1 }, + } +); + +my $op = $input->param('op'); +$template->param( op => $op ); + +if ( $op eq 'delsubmit' ) { + my $del = DeleteEDIDetails( $input->param('id') ); + $template->param( opdelsubmit => 1 ); +} + +if ( $op eq 'addsubmit' ) { + CreateEDIDetails( + $input->param('provider'), $input->param('description'), + $input->param('host'), $input->param('user'), + $input->param('pass'), $input->param('path'), + $input->param('in_dir'), $input->param('san') + ); + $template->param( opaddsubmit => 1 ); +} + +if ( $op eq 'editsubmit' ) { + UpdateEDIDetails( + $input->param('editid'), $input->param('description'), + $input->param('host'), $input->param('user'), + $input->param('pass'), $input->param('provider'), + $input->param('path'), $input->param('in_dir'), + $input->param('san') + ); + $template->param( opeditsubmit => 1 ); +} + +my $ediaccounts = GetEDIAccounts(); +$template->param( ediaccounts => $ediaccounts ); + +output_html_with_http_headers $input, $cookie, $template->output; --- a/admin/edi-edit.pl +++ a/admin/edi-edit.pl @@ -0,0 +1,75 @@ +#!/usr/bin/perl + +# Copyright 2011 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use CGI; +use C4::Auth; +use C4::Output; +use C4::EDI qw( GetVendorList GetEDIAccountDetails); + +my $input = CGI->new(); + +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => 'admin/edi-edit.tmpl', + query => $input, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { borrowers => 1 }, + } +); +my $vendorlist = GetVendorList(); + +my $op = $input->param('op'); +$template->param( op => $op ); + +if ( $op eq 'add' ) { + $template->param( opaddsubmit => 'addsubmit' ); +} +if ( $op eq 'edit' ) { + $template->param( opeditsubmit => 'editsubmit' ); + my $edi_details = GetEDIAccountDetails( $input->param('id') ); + my $selectedprovider = $edi_details->{provider}; + foreach my $prov (@$vendorlist) { + $prov->{selected} = 'selected' + if $prov->{id} == $selectedprovider; + } + $template->param( + editid => $edi_details->{id}, + description => $edi_details->{description}, + host => $edi_details->{host}, + user => $edi_details->{username}, + pass => $edi_details->{password}, + provider => $edi_details->{provider}, + in_dir => $edi_details->{in_dir}, + san => $edi_details->{san} + ); +} +if ( $op eq 'del' ) { + $template->param( + opdelsubmit => 'delsubmit', + opdel => 1, + id => $input->param('id') + ); +} + +$template->param( vendorlist => $vendorlist ); + +output_html_with_http_headers $input, $cookie, $template->output; --- a/installer/data/mysql/atomicupdate/edifact.sql +++ a/installer/data/mysql/atomicupdate/edifact.sql @@ -0,0 +1,27 @@ +CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( + id int(11) NOT NULL auto_increment, + description text NOT NULL, + host text, + username text, + password text, + last_activity date default NULL, + provider int(11) default NULL, + in_dir text, + san varchar(10) default NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS edifact_messages ( + id int(11) NOT NULL auto_increment, + message_type text NOT NULL, + date_sent date default NULL, + provider int(11) default NULL, + status text, + basketno int(11) NOT NULL default '0', + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +insert into permissions (module_bit, code, description) values (13, 'edi_manage', 'Manage EDIFACT transmissions'); + +INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES +('EDIfactEAN', '56781234', '', 'EAN identifier for the library used in EDIfact messages', 'Textarea'); --- a/installer/data/mysql/de-DE/mandatory/userpermissions.sql +++ a/installer/data/mysql/de-DE/mandatory/userpermissions.sql @@ -43,6 +43,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Von Benutzern vergebene Tags moderieren'), (13, 'rotating_collections', 'Wandernde Sammlungen verwalten'), (13, 'upload_local_cover_images', 'Eigene Coverbilder hochladen'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Ablauf eines Abonnements prüfen'), (15, 'claim_serials', 'Fehlende Hefte reklamieren'), (15, 'create_subscription', 'Neues Abonnement anlegen'), --- a/installer/data/mysql/en/mandatory/userpermissions.sql +++ a/installer/data/mysql/en/mandatory/userpermissions.sql @@ -43,6 +43,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags'), (13, 'rotating_collections', 'Manage rotating collections'), (13, 'upload_local_cover_images', 'Upload local cover images'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Check the expiration of a serial'), (15, 'claim_serials', 'Claim missing serials'), (15, 'create_subscription', 'Create a new subscription'), --- a/installer/data/mysql/es-ES/mandatory/userpermissions.sql +++ a/installer/data/mysql/es-ES/mandatory/userpermissions.sql @@ -43,6 +43,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags'), (13, 'rotating_collections', 'Manage rotating collections'), (13, 'upload_local_cover_images', 'Upload local cover images'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Check the expiration of a serial'), (15, 'claim_serials', 'Claim missing serials'), (15, 'create_subscription', 'Create a new subscription'), --- a/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql +++ a/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql @@ -43,6 +43,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'items_batchmod', 'Modifier les exemplaires par lot'), (13, 'items_batchdel', 'Supprimer les exemplaires par lot'), (13, 'upload_local_cover_images', 'Téléchargement des images de couverture'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Contrôler l''expiration d''un périodique'), (15, 'claim_serials', 'Réclamer les périodiques manquants'), (15, 'create_subscription', 'Créer de nouveaux abonnements'), --- a/installer/data/mysql/it-IT/necessari/userpermissions.sql +++ a/installer/data/mysql/it-IT/necessari/userpermissions.sql @@ -45,6 +45,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Modera i tag inseriti dagli utenti'), (13, 'rotating_collections', 'Gestisci le collezioni circolanti (rotating collections)'), (13, 'upload_local_cover_images', 'Carica copertine in locale'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Controlla la scadenza di una risora in continuazione'), (15, 'claim_serials', 'Richiedi i fascicoli non arrivati'), (15, 'create_subscription', 'Crea un nuovo abbonamento'), --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -3041,6 +3041,35 @@ CREATE TABLE IF NOT EXISTS `borrower_modifications` ( PRIMARY KEY (`verification_token`,`borrowernumber`), KEY `verification_token` (`verification_token`), KEY `borrowernumber` (`borrowernumber`) + +-- +-- Table structure for table `vendor_edi_accounts` +-- + +CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( + id int(11) NOT NULL auto_increment, + description text NOT NULL, + host text, + username text, + password text, + last_activity date default NULL, + provider int(11) default NULL, + in_dir text, + san varchar(10) default NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `edifact_messages` +-- +CREATE TABLE IF NOT EXISTS edifact_messages ( + id int(11) NOT NULL auto_increment, + message_type text NOT NULL, + date_sent date default NULL, + provider int(11) default NULL, + status text, + basketno int(11) NOT NULL default '0', + PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- a/installer/data/mysql/nb-NO/1-Obligatorisk/userpermissions.sql +++ a/installer/data/mysql/nb-NO/1-Obligatorisk/userpermissions.sql @@ -63,6 +63,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Behandle tagger fra lånere'), (13, 'rotating_collections', 'Administrere roterende samlinger'), (13, 'upload_local_cover_images', 'Laste opp lokale omslagsbilder'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Sjekke utløpsdato for et periodikum'), (15, 'claim_serials', 'Purre manglende tidsskrifthefter'), (15, 'create_subscription', 'Opprette abonnementer'), --- a/installer/data/mysql/pl-PL/mandatory/userpermissions.sql +++ a/installer/data/mysql/pl-PL/mandatory/userpermissions.sql @@ -44,6 +44,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (16, 'execute_reports', 'Execute SQL reports'), (13, 'rotating_collections', 'Manage rotating collections'), (13, 'upload_local_cover_images', 'Upload local cover images'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Check the expiration of a serial'), (15, 'claim_serials', 'Claim missing serials'), (15, 'create_subscription', 'Create a new subscription'), --- a/installer/data/mysql/ru-RU/mandatory/permissions_and_user_flags.sql +++ a/installer/data/mysql/ru-RU/mandatory/permissions_and_user_flags.sql @@ -67,6 +67,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags'), (13, 'rotating_collections', 'Manage rotating collections'), (13, 'upload_local_cover_images', 'Upload local cover images'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Check the expiration of a serial'), (15, 'claim_serials', 'Claim missing serials'), (15, 'create_subscription', 'Create a new subscription'), --- a/installer/data/mysql/sysprefs.sql +++ a/installer/data/mysql/sysprefs.sql @@ -418,3 +418,4 @@ INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) V INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('UseQueryParser', '0', 'If enabled, try to use QueryParser for queries.', NULL, 'YesNo'); INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('FinesIncludeGracePeriod','1','If enabled, fines calculations will include the grace period.',NULL,'YesNo'); INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('UNIMARCAuthorsFacetsSeparator',', ', 'UNIMARC authors facets separator', NULL, 'short'); +INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES('EDIfactEAN', '56781234', '', 'EAN identifier for the library used in EDIfact messages', 'Textarea'); --- a/installer/data/mysql/uk-UA/mandatory/permissions_and_user_flags.sql +++ a/installer/data/mysql/uk-UA/mandatory/permissions_and_user_flags.sql @@ -67,6 +67,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags'), (13, 'rotating_collections', 'Manage rotating collections'), (13, 'upload_local_cover_images', 'Upload local cover images'), + (13, 'edi_manage', 'Manage EDIFACT transmissions'), (15, 'check_expiration', 'Check the expiration of a serial'), (15, 'claim_serials', 'Claim missing serials'), (15, 'create_subscription', 'Create a new subscription'), --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -6557,6 +6557,53 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + print "Upgrade to $DBversion done (Add tables for EDI EDIfact ordering)\n"; +my $sql1 = <<"END_EDI1"; +CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( + id int(11) NOT NULL auto_increment, + description text NOT NULL, + host text, + username text, + password text, + last_activity date default NULL, + provider int(11) default NULL, + in_dir text, + san varchar(10) default NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_EDI1 + +my $sql2 = <<"END_EDI2"; +CREATE TABLE IF NOT EXISTS edifact_messages ( + id int(11) NOT NULL auto_increment, + message_type text NOT NULL, + date_sent date default NULL, + provider int(11) default NULL, + status text, + basketno int(11) NOT NULL default '0', + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +END_EDI2 + +my $sql3 = <<"END_EDI3"; +insert into permissions (module_bit, code, description) values (13, 'edi_manage', 'Manage EDIFACT transmissions'); +END_EDI3 + +my $sql4 = <<"END_EDI4"; +INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES +('EDIfactEAN', '56781234', '', 'EAN identifier for the library used in EDIfact messages', 'Textarea'); +END_EDI4 + + $dbh->do($sql1); + $dbh->do($sql2); + $dbh->do($sql3); + $dbh->do($sql4); + + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) --- a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc +++ a/koha-tmpl/intranet-tmpl/prog/en/includes/admin-menu.inc @@ -63,6 +63,7 @@
  • Z39.50 client targets
  • Did you mean?
  • +
  • EDI Accounts
  • --- a/koha-tmpl/intranet-tmpl/prog/en/includes/tools-menu.inc +++ a/koha-tmpl/intranet-tmpl/prog/en/includes/tools-menu.inc @@ -23,7 +23,7 @@ [% IF ( CAN_user_tools_import_patrons ) %]
  • Import patrons
  • [% END %] - [% IF ( CAN_user_tools_edit_notices ) %] + [% IF CAN_user_tools_edit_notices %]
  • Notices & slips
  • [% END %] [% IF ( CAN_user_tools_edit_notice_status_triggers ) %] @@ -98,4 +98,7 @@ [% IF ( CAN_user_tools_edit_quotes ) %]
  • Quote editor
  • [% END %] + [% IF ( CAN_user_tools_edi_manage ) %] +
  • EDIfact messages
  • + [% END %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -146,6 +146,7 @@ new YAHOO.widget.Button("basketheadbutton"); new YAHOO.widget.Button("exportbutton"); new YAHOO.widget.Button("delbasketbutton"); + new YAHOO.widget.Button("ediorderbutton"); } //]]> @@ -160,6 +161,9 @@
  • Close this basket
  • [% END %]
  • Export this basket as CSV
  • + [% IF ediaccount %] +
  • EDIfact order
  • + [% END %] @@ -187,7 +191,12 @@ [% END %] [% END %] [% END %] - + [% IF ( edifile ) %] +
    EDIfact order created and will be sent overnight. Filename: [% edifile %] - Send this order now?
    + [% END %] + [% IF ( edisend ) %] +
    [% edisend %]
    + [% END %] [% IF ( NO_BOOKSELLER ) %]

    Vendor not found

    [% ELSE %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -107,6 +107,8 @@
    Define which servers to query for MARC data in the integrated Z39.50 client.
    Did you mean?
    Choose which plugins to use to suggest searches to patrons and staff.
    +
    EDI Accounts
    +
    Manage vendor EDI accounts for import/export
    --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-accounts.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-accounts.tt @@ -0,0 +1,46 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration - EDI Accounts +[% INCLUDE 'doc-head-close.inc' %] + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
    +
    +
    +
    Vendor EDI Accounts + [% IF ( ediaccounts ) %] +
    + [% IF ( opdelsubmit ) %] +
    The account was successfully deleted
    + [% END %] + [% IF ( opaddsubmit ) %] +
    The account was successfully added
    + [% END %] + [% IF ( opeditsubmit ) %] +
    The account was successfully updated
    + [% END %] + + + + [% FOREACH account IN ediaccounts %] + + [% END %] +
    IDVendorDescriptionLast activityActions
    [% account.id %][% account.vendor %][% account.description %][% account.last_activity %]Edit | Delete
    +
    + [% ELSE %] +

    You currently do not have any Vendor EDI Accounts. To add a new account click here.

    + [% END %] +
    +
    +
    + [% INCLUDE 'admin-menu.inc' %] +
    +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-edit.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/edi-edit.tt @@ -0,0 +1,102 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration - EDI Accounts +[% INCLUDE 'doc-head-close.inc' %] + +[% INCLUDE 'calendar.inc' %] + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + +
    +
    +
    +
    +

    Vendor EDI Accounts

    +
    You must complete all required fields
    +
    + + + + [% IF opdel %] +

    Are you sure you want to delete this account?

    +

    YES | NO

    + [% ELSE %] +
    + EDI Account details +
      +
    1. + + +
    2. +
    3. + + +
    4. +
    5. + + +
    6. +
    7. + + +
    8. +
    9. + + +
    10. +
    11. + + +
    12. +
    13. + + +
    14. +
    +
    +
    + + Cancel +
    + [% END %] +
    + +
    +
    +
    + [% INCLUDE 'admin-menu.inc' %] +
    +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/edi.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/edi.tt @@ -0,0 +1,63 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Tools › EDIfact messages +[% INCLUDE 'doc-head-close.inc' %] + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + + +
    +
    +
    +
    EDIfact messages + [% IF messagelist %] +
    + [% IF opdelsubmit %] +
    The account was successfully deleted
    + [% END %] + [% IF opaddsubmit %] +
    The account was successfully added
    + [% END %] + [% IF opeditsubmit %] +
    The account was successfully updated
    + [% END %] + + + [% FOREACH message IN messagelist %] + + + + + + + [% END %] +
    DateMessage typeProviderStatusBasket
    [% message.date_sent %][% message.message_type %][% message.providername %][% message.status %] + [% IF message.basketno %] + View basket + [% END %] +
    +
    + + [% ELSE %] +

    There are currently no EDIfact messages to display.

    + [% END %] +
    +
    +
    + [% INCLUDE 'tools-menu.inc' %] +
    +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tt @@ -97,6 +97,11 @@
    Quote editor for Quote-of-the-day feature in OPAC
    [% END %] + [% IF ( CAN_user_tools_edi_manage ) %] +
    EDIfact messages
    +
    Manage EDIfact transmissions
    + [% END %] +
    --- a/misc/cronjobs/clean_edifiles.pl +++ a/misc/cronjobs/clean_edifiles.pl @@ -0,0 +1,43 @@ +#!/usr/bin/perl + +# Copyright 2011 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use C4::Context; +my $edidir = C4::Context->config('intranetdir'); + +$edidir .= '/misc/edi_files'; +opendir( my $dh, $edidir ); +my @files = readdir($dh); +close $dh; + +foreach my $file (@files) { + my $now = time; + my @stat = stat("$edidir/$file"); + if ( + $stat[9] < ( $now - 2592000 ) + && ( ( index lc($file), '.ceq' ) > -1 + || ( index lc($file), '.cep' ) > -1 ) + ) + { + print "Deleting file $edidir/$file..."; + unlink("$edidir/$file"); + print "Done.\n"; + } +} --- a/misc/cronjobs/edifact_order_ftp_transfer.pl +++ a/misc/cronjobs/edifact_order_ftp_transfer.pl @@ -0,0 +1,127 @@ +#!/usr/bin/perl + +# Copyright 2011,2012 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use C4::Context; +use C4::EDI qw ( GetEDIFTPAccounts ParseEDIQuote LogEDITransaction); +use Net::FTP; + +my $ftpaccounts = GetEDIFTPAccounts; + +my @errors; +my @putdirlist; +my $newerr; +my @files; +my $putdir = C4::Context->config('intranetdir'); +$putdir .= '/misc/edi_files/'; +my $ediparse; +opendir( my $dh, $putdir ); +@putdirlist = readdir $dh; +closedir $dh; + +foreach my $accounts (@$ftpaccounts) { + my $ftp = Net::FTP->new( $accounts->{host}, Timeout => 10, Passive => 1 ) + or $newerr = 1; + $ftp->binary(); + push @errors, "Can't ftp to $accounts->{host}: $!\n" if $newerr; + myerr() if $newerr; + if ( !$newerr ) { + $newerr = 0; + print "Connected to $accounts->{host}\n"; + + $ftp->login( "$accounts->{username}", "$accounts->{password}" ) + or $newerr = 1; + print "Getting file list\n"; + push @errors, "Can't login to $accounts->{host}: $!\n" if $newerr; + $ftp->quit if $newerr; + myerr() if $newerr; + if ( !$newerr ) { + print "Logged in\n"; + $ftp->cwd( $accounts->{in_dir} ) or $newerr = 1; + push @errors, "Can't cd in server $accounts->{host} $!\n" + if $newerr; + myerr() if $newerr; + $ftp->quit if $newerr; + + @files = $ftp->ls or $newerr = 1; + push @errors, + "Can't get file list from server $accounts->{host} $!\n" + if $newerr; + myerr() if $newerr; + if ( !$newerr ) { + print "Got file list\n"; + foreach my $file (@files) { + if ( $file =~ m/\.ceq/i ) { + my $match; + foreach my $f (@putdirlist) { + if ( $f eq $file ) { + $match = 1; + last; + } + } + if ( $match != 1 ) { + chdir $putdir; + $ftp->get($file) or $newerr = 1; + push @errors, +"Can't transfer file ($file) from $accounts->{host} $!\n" + if $newerr; + $ftp->quit if $newerr; + myerr() if $newerr; + if ( !$newerr ) { + $ediparse = + ParseEDIQuote( $file, $accounts->{provider} ); + } + if ( $ediparse == 1 ) { + my $qext = '.ceq'; + my $rext = '.eeq'; + my $renamed = lc $file; + $renamed =~ s/$qext/$rext/g; + $ftp->rename( $file, $renamed ); + } + } + } + } + } + } + if ( !$newerr ) { + LogEDITransaction( $accounts->{id} ); + } + $ftp->quit; + } + $newerr = 0; +} + +print "\n@errors\n"; + +if (@errors) { + my $logfile = C4::Context->config('intranetdir'); + $logfile .= '/misc/edi_files/edi_ftp_error.log'; + open my $fh, '>>', $logfile; + my ( $sec, $min, $hour, $mday, $mon, $year ) = localtime(time); + printf $fh "%4d-%02d-%02d %02d:%02d:%02d\n-----\n", $year + 1900, + $mon + 1, $mday, $hour, $min, $sec; + print $fh "@errors\n"; + close $fh; +} + +sub myerr { + print 'Error: ', @errors; + return; +} --- a/misc/cronjobs/send_queued_edi_orders.pl +++ a/misc/cronjobs/send_queued_edi_orders.pl @@ -0,0 +1,26 @@ +#!/usr/bin/perl + +# Copyright 2011 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use C4::Context; +use C4::EDI qw( SendQueuedEDIOrders ); + + +SendQueuedEDIOrders(); --- a/misc/edi_files/README.txt +++ a/misc/edi_files/README.txt @@ -0,0 +1,3 @@ +This is a readme file but is secretly just a placeholder for the edi_files directory. + +--Elliott Davis --- a/t/Calendar.t +++ a/t/Calendar.t @@ -36,7 +36,6 @@ $module_context->mock( return 'Calendar'; } ); - SKIP: { skip "DBD::Mock is too old", 33 --- a/t/EDI.t +++ a/t/EDI.t @@ -0,0 +1,85 @@ +#!/usr/bin/env perl + +use strict; +use warnings; +use Test::More qw/no_plan/; +use Test::MockModule; +use DBD::Mock; + +my $module_context = new Test::MockModule('C4::Context'); +my $module_budgets = new Test::MockModule("C4::Budgets"); + +$module_context->mock( + '_new_dbh', + sub { + my $dbh = DBI->connect( 'DBI:Mock:', '', '' ) + || die "Cannot create handle: $DBI::errstr\n"; + return $dbh; + } + ); + +my $dbh = C4::Context->dbh(); + +$module_context->mock("EDIfactEAN",sub{ return 1; }); +$module_budgets->mock('GetCurrency',sub{ return {currency => "USD"}; }); + +use_ok('C4::EDI'); + SKIP: { + skip "DBD::Mock is too old", 33 + unless $DBD::Mock::VERSION >= 1.45; + + $dbh->{mock_session} = DBD::Mock::Session->new('EDI_session' => ( + { #GetVendorList + statement => "select id, name from aqbooksellers order by name asc", + results => [ + ["id","name"], + [1,"Amazon"], + ] + }, + { #GetEDIAccounts + statement => "select vendor_edi_accounts.id, aqbooksellers.id as providerid, aqbooksellers.name as vendor, vendor_edi_accounts.description, vendor_edi_accounts.last_activity from vendor_edi_accounts inner join aqbooksellers on vendor_edi_accounts.provider = aqbooksellers.id order by aqbooksellers.name asc", + results => [ + ["vendor_edi_accounts.id", "providerid", "vendor", "vendor_edi_accounts.description", "vendor_edi_accounts.last_activity"], + [1,1,"Amazon", "Online Bookseller","2013-01-01 00:00:00"], + ] + }, + { #GetEDIAccounts + statement => "select san from vendor_edi_accounts where provider=?", + results => [ + ["san"], + [1], + ] + }, + { + statement => "select message_type from edifact_messages where basketno=?", + results => [ + ["message_type"], + ["QUOTE"], + ] + } + ) + ); + cmp_ok(C4::EDI->GetVendorList()->[0]->{name}, 'eq', 'Amazon'); + cmp_ok(C4::EDI->GetEDIAccounts->[0]->{vendor}, 'eq', 'Amazon'); + +my $module_acquisition = new Test::MockModule("C4::Acquisition"); +$module_acquisition->mock('GetOrderItemInfo',sub{ return ("MPL","npl.lgf","BOOK","1234","Emergency"); }); +$module_acquisition->mock('GetOrders',sub{ + return ({title => "test", + author => "test", + publishercode => '1', + isbn => '1234567890', + publicationyear => "1996", + quantity => '1', + ordernumber => '35', + notes => "test_notes", + }); + +}); + +my ($basketno, $booksellerid) = 1; +(-e C4::EDI->CreateEDIOrder($basketno,$booksellerid)) ? + pass("Create EDI File!") : + fail("Create EDI file!"); + +} # End Skip Block --- a/tools/edi.pl +++ a/tools/edi.pl @@ -0,0 +1,43 @@ +#!/usr/bin/perl + +# Copyright 2011 Mark Gavillet & PTFS Europe Ltd +# +# 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 2 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 strict; +use warnings; +use CGI; +use C4::Auth; +use C4::Output; +use C4::EDI qw( GetEDIfactMessageList); + +my $input = CGI->new(); + +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => 'tools/edi.tmpl', + query => $input, + type => 'intranet', + authnotrequired => 0, + flagsrequired => { borrowers => 1 }, + } +); + +my $messagelist = GetEDIfactMessageList(); + +$template->param( messagelist => $messagelist ); + +output_html_with_http_headers $input, $cookie, $template->output; --