From 67fd0891594b6381eec00eee3434690404beb1a6 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Mon, 4 May 2020 11:49:40 -0400 Subject: [PATCH] more robust versions of kmig import and export --- kmig.d/bin/mig-export | 295 +++++++++++++++++++++++++++++++++--- kmig.d/bin/mig-import | 402 +++++++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 665 insertions(+), 32 deletions(-) diff --git a/kmig.d/bin/mig-export b/kmig.d/bin/mig-export index 4b0cb19..0e6d27b 100755 --- a/kmig.d/bin/mig-export +++ b/kmig.d/bin/mig-export @@ -16,64 +16,92 @@ use FindBin; my $mig_bin = "$FindBin::Bin/"; use lib "$FindBin::Bin/"; use XML::Writer; +use open ':std', ':encoding(UTF-8)'; +use Encode qw( decode_utf8 ); use KMig; my $dbh = KMig::db_connect(); +$dbh->{mysql_enable_utf8mb4} = 1; #to do check for array passed and if not present then use tags my @taglist = @ARGV; my $arg_list_length = scalar @taglist; -if($arg_list_length < 1) { @taglist = ("preferences","authorisedvalues","libraries","borrowerattributes","itemtypes"); } +if($arg_list_length < 1) { @taglist = ("authorisedvalues","calendar","circrules","itemtypes","libraries","patrontypes","preferences","smsproviders"); } #borrowerattributes $MIGGITDIR =~ s/\/\//\//; my $timestamp = create_timestamp(); foreach my $backup (@taglist) { my $backupfile; - if ($backup eq 'preferences') { - $backupfile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; - backup_preferences($dbh,$backupfile); - } if ($backup eq 'authorisedvalues') { - $backupfile = $MIGGITDIR . 'authorised_values' . '.' . $timestamp . '.xml'; - backup_authorised_values($dbh,$backupfile); + $backupfile = $MIGGITDIR . 'authorisedvalues' . '.' . $timestamp . '.xml'; + print "Backing up $backupfile ... \n"; + backup_authorisedvalues($dbh,$backupfile); } if ($backup eq 'borrowerattributes') { $backupfile = $MIGGITDIR . 'borrower_attributes' . '.' . $timestamp . '.xml'; - backup_borrower_attributes($dbh,$backupfile); + print "Restoring from $restorefile ... \n"; + backup_borrower_attributes($dbh,$backupfile); } - if ($backup eq 'libraries') { - $backupfile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml'; - backup_libraries($dbh,$backupfile); + if ($backup eq 'calendar') { + $backupfile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_calendar($dbh,$backupfile); + } + if ($backup eq 'circrules') { + $backupfile = $MIGGITDIR . 'circrules' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_circrules($dbh,$backupfile); } if ($backup eq 'itemtypes') { $backupfile = $MIGGITDIR . 'itemtypes' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; backup_itemtypes($dbh,$backupfile); } + if ($backup eq 'libraries') { + $backupfile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_libraries($dbh,$backupfile); + } + if ($backup eq 'patrontypes') { + $backupfile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_patrontypes($dbh,$backupfile); + } + if ($backup eq 'preferences') { + $backupfile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_preferences($dbh,$backupfile); + } + if ($backup eq 'smsproviders') { + $backupfile = $MIGGITDIR . 'smsproviders' . '.' . $timestamp . '.xml'; + print "Restoring from $restorefile ... \n"; + backup_smsproviders($dbh,$backupfile); + } } -sub backup_authorised_values { +sub backup_authorisedvalues { my $dbh = shift; my $backupfile = shift; open(my $fh, '>', $backupfile) or die "Could not open $backupfile!"; my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); $xml->xmlDecl('UTF-8'); - $xml->startTag('authorised_values'); - my $query = "SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values"; + $xml->startTag('authorisedvalues'); + my $query = "SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values WHERE category != 'ITEMTYPECAT'"; my $sth = $dbh->prepare($query); $sth->execute(); while (my @row = $sth->fetchrow_array) { $xml->startTag('value'); $xml->dataElement('category', $row[0]); - $xml->dataElement('authvalue', $row[1]); + $xml->dataElement('authorised_value', $row[1]); $xml->dataElement('lib', $row[2]); $xml->dataElement('lib_opac', $row[3]); - $xml->dataElement('image_url', $row[4]); + $xml->dataElement('imageurl', $row[4]); $xml->endTag('value'); } - $xml->endTag('authorised_values'); + $xml->endTag('authorisedvalues'); $xml->end(); close $fh; return; @@ -137,6 +165,124 @@ sub backup_borrower_attributes { return; } +sub backup_calendar { + my $dbh = shift; + my $backupfile = shift; + + open(my $fh, '>', $backupfile) or die "Could not open $backupfile!"; + my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); + $xml->xmlDecl('UTF-8'); + $xml->startTag('holidays'); + + my $query = "SELECT branchcode,weekday,day,month,title,description FROM repeatable_holidays"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('repeatable'); + $xml->dataElement('branchcode',$row[0]); + $xml->dataElement('weekday',$row[1]); + $xml->dataElement('day',$row[2]); + $xml->dataElement('month',$row[3]); + $xml->dataElement('title',$row[4]); + $xml->dataElement('description',$row[5]); + $xml->endTag('repeatable'); + } + + $query = "SELECT branchcode,day,month,year,isexception,title,description FROM special_holidays"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('special'); + $xml->dataElement('branchcode',$row[0]); + $xml->dataElement('day',$row[1]); + $xml->dataElement('month',$row[2]); + $xml->dataElement('year',$row[3]); + $xml->dataElement('isexception',$row[4]); + $xml->dataElement('title',$row[5]); + $xml->dataElement('description',$row[6]); + $xml->endTag('special'); + } + + $xml->endTag('holidays'); + $xml->end(); + close $fh; + return; +} + +sub backup_circrules { + my $dbh = shift; + my $backupfile = shift; + + open(my $fh, '>', $backupfile) or die "Could not open $backupfile!"; + my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); + $xml->xmlDecl('UTF-8'); + $xml->startTag('document'); + + $xml->startTag('circ'); + my $query = "SELECT branchcode, categorycode, itemtype, rule_name, rule_value FROM circulation_rules"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('rule'); + $xml->dataElement('branchcode', $row[0]); + $xml->dataElement('categorycode', $row[1]); + $xml->dataElement('itemtype', $row[2]); + $xml->dataElement('rule_name', $row[3]); + $xml->dataElement('rule_value', $row[4]); + $xml->endTag('rule'); + } + $xml->endTag('circ'); + + $xml->startTag('issuing'); + $query = "SELECT categorycode,itemtype,restrictedtype,rentaldiscount,reservecharge,fine,finedays,maxsuspensiondays,suspension_chargeperiod,firstremind,chargeperiod,chargeperiod_charge_at,accountsent,issuelength,lengthunit,hardduedate,hardduedatecompare,renewalsallowed,renewalperiod,norenewalbefore,auto_renew,no_auto_renewal_after,no_auto_renewal_after_hard_limit,reservesallowed,holds_per_record,holds_per_day,branchcode,overduefinescap,cap_fine_to_replacement_price,onshelfholds,opacitemholds,article_requests,note FROM issuingrules"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('rule'); + $xml->dataElement('categorycode', $row[0]); + $xml->dataElement('itemtype', $row[1]); + $xml->dataElement('restrictedtype', $row[2]); + $xml->dataElement('rentaldiscount', $row[3]); + $xml->dataElement('reservecharge', $row[4]); + $xml->dataElement('fine', $row[5]); + $xml->dataElement('finedays', $row[6]); + $xml->dataElement('maxsuspensiondays', $row[7]); + $xml->dataElement('suspension_chargeperiod', $row[8]); + $xml->dataElement('firstremind', $row[9]); + $xml->dataElement('chargeperiod', $row[10]); + $xml->dataElement('chargeperiod_charge_at', $row[11]); + $xml->dataElement('accountsent', $row[12]); + $xml->dataElement('issuelength', $row[13]); + $xml->dataElement('lengthunit', $row[14]); + $xml->dataElement('hardduedate', $row[15]); + $xml->dataElement('hardduedatecompare', $row[16]); + $xml->dataElement('renewalsallowed', $row[17]); + $xml->dataElement('renewalperiod', $row[18]); + $xml->dataElement('norenewalbefore', $row[19]); + $xml->dataElement('auto_renew', $row[20]); + $xml->dataElement('no_auto_renewal_after', $row[21]); + $xml->dataElement('no_auto_renewal_after_hard_limit', $row[22]); + $xml->dataElement('reservesallowed', $row[23]); + $xml->dataElement('holds_per_record', $row[24]); + $xml->dataElement('holds_per_day', $row[25]); + $xml->dataElement('branchcode', $row[26]); + $xml->dataElement('overduefinescap', $row[27]); + $xml->dataElement('cap_fine_to_replacement_price', $row[28]); + $xml->dataElement('onshelfholds', $row[29]); + $xml->dataElement('opacitemholds', $row[30]); + $xml->dataElement('article_requests', $row[31]); + $xml->dataElement('note', $row[32]); + $xml->endTag('rule'); + } + $xml->endTag('issuing'); + + $xml->endTag('document'); + $xml->end(); + close $fh; + return; +} + + sub backup_itemtypes { my $dbh = shift; my $backupfile = shift; @@ -157,7 +303,7 @@ sub backup_itemtypes { $xml->dataElement('rentalcharge', $row[2]); $xml->dataElement('rentalcharge_daily', $row[3]); $xml->dataElement('rentalcharge_hourly', $row[4]); - $xml->dataElement('defaultreplacementcost', $row[5]); + $xml->dataElement('defaultreplacecost', $row[5]); $xml->dataElement('processfee', $row[6]); $xml->dataElement('notforloan', $row[7]); $xml->dataElement('imageurl', $row[8]); @@ -186,6 +332,20 @@ sub backup_itemtypes { } $xml->endTag('authorised_values'); + $xml->startTag('localizations'); + $query = "SELECT entity, code, lang, translation FROM localization WHERE entity = 'itemtypes';"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('value'); + $xml->dataElement('entity', $row[0]); + $xml->dataElement('code', $row[1]); + $xml->dataElement('lang', $row[2]); + $xml->dataElement('translation', $row[3]); + $xml->endTag('value'); + } + $xml->endTag('localizations'); + $xml->endTag('document'); $xml->end(); close $fh; @@ -200,11 +360,12 @@ sub backup_libraries { my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); $xml->xmlDecl('UTF-8'); $xml->startTag('libraries'); + my $query = "SELECT id,parent_id,branchcode,title,description,ft_hide_patron_info,ft_search_groups_opac,ft_search_groups_staff FROM library_groups"; my $sth = $dbh->prepare($query); $sth->execute(); while (my @row = $sth->fetchrow_array) { - $xml->startTag('library'); + $xml->startTag('library_group'); $xml->dataElement('id',$row[0]); $xml->dataElement('parent_id',$row[1]); $xml->dataElement('branchcode',$row[2]); @@ -213,14 +374,86 @@ sub backup_libraries { $xml->dataElement('ft_hide_patron_info',$row[5]); $xml->dataElement('ft_search_groups_opac',$row[6]); $xml->dataElement('ft_search_groups_staff',$row[7]); + $xml->endTag('library_group'); + } + + $query = "SELECT branchcode,branchname,branchaddress1,branchaddress2,branchaddress3,branchzip,branchcity,branchstate,branchcountry,branchphone,branchfax,branchemail,branchreplyto,branchreturnpath,branchurl,issuing,branchip,branchprinter,branchnotes,opac_info,geolocation,marcorgcode,pickup_location FROM branches"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('library'); + $xml->dataElement('branchcode',$row[0]); + $xml->dataElement('branchname',$row[1]); + $xml->dataElement('branchaddress1',$row[2]); + $xml->dataElement('branchaddress2',$row[3]); + $xml->dataElement('branchaddress3',$row[4]); + $xml->dataElement('branchzip',$row[5]); + $xml->dataElement('branchcity',$row[6]); + $xml->dataElement('branchstate',$row[7]); + $xml->dataElement('branchcountry',$row[8]); + $xml->dataElement('branchphone',$row[9]); + $xml->dataElement('branchfax',$row[10]); + $xml->dataElement('branchemail',$row[11]); + $xml->dataElement('branchreplyto',$row[12]); + $xml->dataElement('branchreturnpath',$row[13]); + $xml->dataElement('branchurl',$row[14]); + $xml->dataElement('issuing',$row[15]); + $xml->dataElement('branchip',$row[16]); + $xml->dataElement('branchprinter',$row[17]); + $xml->dataElement('branchnotes',$row[18]); + $xml->dataElement('opac_info',$row[19]); + $xml->dataElement('geolocation',$row[20]); + $xml->dataElement('marcorgcode',$row[21]); + $xml->dataElement('pickup_location',$row[22]); $xml->endTag('library'); } + $xml->endTag('libraries'); $xml->end(); close $fh; return; } +sub backup_patrontypes { + my $dbh = shift; + my $backupfile = shift; + + open(my $fh, '>', $backupfile) or die "Could not open $backupfile!"; + my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); + $xml->xmlDecl('UTF-8'); + $xml->startTag('patron'); + my $query = "SELECT categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,finetype,bulk,enrolmentfee,overduenoticerequired,issuelimit,reservefee,hidelostitems,category_type,BlockExpiredPatronOpacActions,default_privacy,checkprevcheckout,reset_password,change_password FROM categories"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('category'); + $xml->dataElement('categorycode',$row[0]); + $xml->dataElement('description',$row[1]); + $xml->dataElement('enrolmentperiod',$row[2]); + $xml->dataElement('enrolmentperioddate',$row[3]); + $xml->dataElement('upperagelimit',$row[4]); + $xml->dataElement('dateofbirthrequired',$row[5]); + $xml->dataElement('finetype',$row[6]); + $xml->dataElement('bulk',$row[7]); + $xml->dataElement('enrolmentfee',$row[8]); + $xml->dataElement('overduenoticerequired',$row[9]); + $xml->dataElement('issuelimit',$row[10]); + $xml->dataElement('reservefee',$row[11]); + $xml->dataElement('hidelostitems',$row[12]); + $xml->dataElement('category_type',$row[13]); + $xml->dataElement('BlockExpiredPatronOpacActions',$row[14]); + $xml->dataElement('default_privacy',$row[15]); + $xml->dataElement('checkprevcheckout',$row[16]); + $xml->dataElement('reset_password',$row[17]); + $xml->dataElement('change_password',$row[18]); + $xml->endTag('category'); + } + $xml->endTag('patron'); + $xml->end(); + close $fh; + return; +} + sub backup_preferences { my $dbh = shift; my $backupfile = shift; @@ -244,6 +477,30 @@ sub backup_preferences { return; } +sub backup_smsproviders { + my $dbh = shift; + my $backupfile = shift; + + open(my $fh, '>', $backupfile) or die "Could not open $backupfile!"; + my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, ); + $xml->xmlDecl('UTF-8'); + $xml->startTag('sms'); + my $query = "SELECT name,domain FROM sms_providers"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('provider'); + $xml->dataElement('name',$row[0]); + $xml->dataElement('domain',$row[1]); + $xml->endTag('provider'); + } + $xml->endTag('sms'); + $xml->end(); + close $fh; + return; +} + + sub create_timestamp { my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); $year += 1900; diff --git a/kmig.d/bin/mig-import b/kmig.d/bin/mig-import index f5e7f8a..6788d28 100755 --- a/kmig.d/bin/mig-import +++ b/kmig.d/bin/mig-import @@ -14,29 +14,80 @@ use Cwd 'abs_path'; use Cwd qw(getcwd); use FindBin; use XML::LibXML; +use open ':std', ':encoding(UTF-8)'; +use Encode qw( decode_utf8 ); use List::Util qw( min max ); my $mig_bin = "$FindBin::Bin/"; use lib "$FindBin::Bin/"; use KMig; my $dbh = KMig::db_connect(); +#$dbh->{mysql_enable_utf8mb4} = 1; +$dbh->do('SET NAMES utf8mb4'); #to do check for array passed and if not present then use tags my @taglist = @ARGV; my $arg_list_length = scalar @taglist; -if($arg_list_length < 1) { @taglist = ("preferences","libraries"); } +if($arg_list_length < 1) { @taglist = ("authorisedvalues","calendar","circrules","itemtypes","libraries","patrontypes","preferences","smsproviders"); } $MIGGITDIR =~ s/\/\//\//; foreach my $restore (@taglist) { my $restorefile; + if ($restore eq 'authorisedvalues') { + my $timestamp = most_recent_single($MIGGITDIR,'authorisedvalues'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'authorisedvalues' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_authorisedvalues($dbh,$restorefile); } + } + if ($restore eq 'calendar') { + my $timestamp = most_recent_single($MIGGITDIR,'calendar'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_calendar($dbh,$restorefile); } + } + if ($restore eq 'circrules') { + my $timestamp = most_recent_single($MIGGITDIR,'circrules'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'circrules' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_circrules($dbh,$restorefile); } + } + if ($restore eq 'itemtypes') { + my $timestamp = most_recent_single($MIGGITDIR,'itemtypes'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'itemtypes' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_itemtypes($dbh,$restorefile); } + } + if ($restore eq 'libraries') { + my $timestamp = most_recent_single($MIGGITDIR,'libraries'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_libraries($dbh,$restorefile); } + } + if ($restore eq 'patrontypes') { + my $timestamp = most_recent_single($MIGGITDIR,'patrontypes'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_patrontypes($dbh,$restorefile); } + } if ($restore eq 'preferences') { my $timestamp = most_recent_single($MIGGITDIR,'systempreferences'); - $restorefile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; - restore_preferences($dbh,$restorefile); + if ($timestamp) { $restorefile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_preferences($dbh,$restorefile); } + print "IMPORTANT : if you are changing system preferences you may need to run 'sudo systemctl restart apache2 memcached'\n\n"; + } + if ($restore eq 'smsproviders') { + my $timestamp = most_recent_single($MIGGITDIR,'smsproviders'); + if ($timestamp) { $restorefile = $MIGGITDIR . 'smsproviders' . '.' . $timestamp . '.xml'; } + print "Restoring from $restorefile ... \n"; + if ($restorefile) { restore_smsproviders($dbh,$restorefile); } } } +print "Done.\n"; + + sub most_recent_single { my $MGIGITDIR = shift; my $str = shift; @@ -55,31 +106,331 @@ sub most_recent_single { return $max; } +sub restore_authorisedvalues { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM authorised_values WHERE category != 'ITEMTYPECAT'"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + foreach my $node ($dom->findnodes('//value')) { + my $category = sql_str($node->findvalue('./category')); + my $authorised_value = sql_str($node->findvalue('./authorised_value')); + my $lib = sql_str($node->findvalue('./lib')); + my $lib_opac = sql_str($node->findvalue('./lib_opac')); + my $imageurl = sql_str($node->findvalue('./imageurl')); + $query = "INSERT INTO authorised_values (category,authorised_value,lib,lib_opac,imageurl) VALUES ($category,$authorised_value,$lib,$lib_opac,$imageurl)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + return; +} + +sub restore_calendar { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM repeatable_holidays WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "DELETE FROM special_holidays WHERE 1 = 1"; + $sth = $dbh->prepare($query); + $sth->execute(); + + foreach my $node ($dom->findnodes('/holidays/repeatable')) { + my $branchcode = sql_str($node->findvalue('./branchcode')); + my $weekday = sql_num($node->findvalue('./weekday')); + my $day = sql_num($node->findvalue('./day')); + my $month = sql_num($node->findvalue('./month')); + my $title = sql_str($node->findvalue('./title')); + my $description = sql_str($node->findvalue('./description')); + $query = "INSERT INTO repeatable_holidays (branchcode,weekday,day,month,title,description) VALUES ($branchcode,$weekday,$day,$month,$title,$description)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + foreach my $node ($dom->findnodes('/holidays/special')) { + my $branchcode = sql_str($node->findvalue('./branchcode')); + my $day = sql_num($node->findvalue('./day')); + my $month = sql_num($node->findvalue('./month')); + my $year = sql_num($node->findvalue('./year')); + my $isexception = sql_num($node->findvalue('./isexception')); + my $title = sql_str($node->findvalue('./title')); + my $description = sql_str($node->findvalue('./description')); + $query = "INSERT INTO special_holidays (branchcode,day,month,year,isexception,title,description) VALUES ($branchcode,$day,$month,$year,$isexception,$title,$description)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + return; +} + +sub restore_circrules { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + #order of operations - clean out circ_rules, then issuingrules; restore issuingrules then circ_rules + + my $query = "DELETE FROM circulation_rules WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "DELETE FROM issuingrules WHERE 1 = 1"; + $sth = $dbh->prepare($query); + $sth->execute(); + + foreach my $node ($dom->findnodes('//issuing/rule')) { + my $categorycode = sql_str($node->findvalue('./categorycode')); + my $itemtype = sql_str($node->findvalue('./itemtype')); + my $restrictedtype = sql_num($node->findvalue('./restrictedtype')); + my $rentaldiscount = sql_num($node->findvalue('./rentaldiscount')); + my $reservecharge = sql_num($node->findvalue('./reservecharge')); + my $fine = sql_num($node->findvalue('./fine')); + my $finedays = sql_num($node->findvalue('./finedays')); + my $maxsuspensiondays = sql_num($node->findvalue('./maxsuspensiondays')); + my $suspension_chargeperiod = sql_num($node->findvalue('./suspension_chargeperiod')); + my $firstremind = sql_num($node->findvalue('./firstremind')); + my $chargeperiod = sql_num($node->findvalue('./chargeperiod')); + my $chargeperiod_charge_at = sql_num($node->findvalue('./chargeperiod_charge_at')); + my $accountsent = sql_num($node->findvalue('./accountsent')); + my $issuelength = sql_num($node->findvalue('./issuelength')); + my $lengthunit = sql_str($node->findvalue('./lengthunit')); + my $hardduedate = sql_str($node->findvalue('./hardduedate')); + my $hardduedatecompare = sql_num($node->findvalue('./hardduedatecompare')); + my $renewalsallowed = sql_num($node->findvalue('./renewalsallowed')); + my $renewalperiod = sql_num($node->findvalue('./renewalperiod')); + my $norenewalbefore = sql_num($node->findvalue('./norenewalbefore')); + my $auto_renew = sql_num($node->findvalue('./auto_renew')); + my $no_auto_renewal_after = sql_num($node->findvalue('./no_auto_renewal_after')); + my $no_auto_renewal_after_hard_limit = sql_str($node->findvalue('./no_auto_renewal_after_hard_limit')); + my $reservesallowed = sql_num($node->findvalue('./reservesallowed')); + my $holds_per_record = sql_num($node->findvalue('./holds_per_record')); + my $holds_per_day = sql_num($node->findvalue('./holds_per_day')); + my $branchcode = sql_str($node->findvalue('./branchcode')); + my $overduefinescap = sql_num($node->findvalue('./overduefinescap')); + my $cap_fine_to_replacement_price = sql_num($node->findvalue('./cap_fine_to_replacement_price')); + my $onshelfholds = sql_num($node->findvalue('./onshelfholds')); + my $opacitemholds = sql_str($node->findvalue('./opacitemholds')); + my $article_requests = sql_str($node->findvalue('./article_requests')); + my $note = sql_str($node->findvalue('./note')); + $query = "INSERT INTO issuingrules (categorycode,itemtype,restrictedtype,rentaldiscount,reservecharge,fine,finedays,maxsuspensiondays,suspension_chargeperiod,firstremind,chargeperiod,chargeperiod_charge_at,accountsent,issuelength,lengthunit,hardduedate,hardduedatecompare,renewalsallowed,renewalperiod,norenewalbefore,auto_renew,no_auto_renewal_after,no_auto_renewal_after_hard_limit,reservesallowed,holds_per_record,holds_per_day,branchcode,overduefinescap,cap_fine_to_replacement_price,onshelfholds,opacitemholds,article_requests,note) VALUES ($categorycode,$itemtype,$restrictedtype,$rentaldiscount,$reservecharge,$fine,$finedays,$maxsuspensiondays,$suspension_chargeperiod,$firstremind,$chargeperiod,$chargeperiod_charge_at,$accountsent,$issuelength,$lengthunit,$hardduedate,$hardduedatecompare,$renewalsallowed,$renewalperiod,$norenewalbefore,$auto_renew,$no_auto_renewal_after,$no_auto_renewal_after_hard_limit,$reservesallowed,$holds_per_record,$holds_per_day,$branchcode,$overduefinescap,$cap_fine_to_replacement_price,$onshelfholds,$opacitemholds,$article_requests,$note)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + foreach my $node ($dom->findnodes('//circ/rule')) { + my $branchcode = sql_str($node->findvalue('./branchcode')); + my $categorycode = sql_str($node->findvalue('./categorycode')); + my $itemtype = sql_str($node->findvalue('./itemtype')); + my $rule_name = sql_str($node->findvalue('./rule_name')); + my $rule_value = sql_str($node->findvalue('./rule_value')); + $query = "INSERT INTO circulation_rules (branchcode,categorycode,itemtype,rule_name,rule_value) VALUES ($branchcode,$categorycode,$itemtype,$rule_name,$rule_value)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + return; +} + +sub restore_itemtypes { + my $dbh = shift; + my $restorefile = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restorefile); + + my $query = "UPDATE itemtypes SET searchcategory = NULL"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "DELETE FROM authorised_values WHERE category = 'ITEMTYPECAT'"; + $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "DELETE FROM localization WHERE entity = 'itemtypes'"; + $sth = $dbh->prepare($query); + $sth->execute(); + + foreach my $node ($dom->findnodes('/authorised_values/value')) { + my $category = sql_str($node->findvalue('./category')); + my $authvalue = sql_str($node->findvalue('./authvalue')); + my $lib = sql_str($node->findvalue('./lib')); + my $lib_opac = sql_str($node->findvalue('./lib_opac')); + my $image_url = sql_str($node->findvalue('./image_url')); + my $query = "INSERT INTO authorised_values (category,authvalue,lib,lib_opac,image_url) VALUES ($category,$authvalue,$lib,$lib_opac,$image_url)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + foreach my $node ($dom->findnodes('/itemtypes/value')) { + my $itemtype = sql_str($node->findvalue('./itemtype')); + my $description = sql_str($node->findvalue('./description')); + my $rentalcharge = sql_str($node->findvalue('./rentalcharge')); + my $rentalcharge_daily = sql_str($node->findvalue('./rentalcharge_daily')); + my $rentalcharge_hourly = sql_str($node->findvalue('./rentalcharge_hourly')); + my $defaultreplacecost = sql_str($node->findvalue('./defaultreplacecost')); + my $processfee = sql_str($node->findvalue('./processfee')); + my $notforloan = sql_str($node->findvalue('./notforloan')); + my $imageurl = sql_str($node->findvalue('./imageurl')); + my $summary = sql_str($node->findvalue('./summary')); + my $checkinmsg = sql_str($node->findvalue('./checkinmsg')); + my $checkinmsgtype = sql_str($node->findvalue('./checkinmsgtype')); + my $sip_media_type = sql_str($node->findvalue('./sip_media_type')); + my $hideinopac = sql_str($node->findvalue('./hideinopac')); + my $searchcategory = sql_str($node->findvalue('./searchcategory')); + $query = "SELECT itemtype FROM itemtypes WHERE itemtype = $itemtype"; + $sth = $dbh->prepare($query); + $sth->execute(); + my $answer; + while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); } + if (!defined $answer or $answer ne $itemtype) { + $query = "INSERT INTO itemtypes (itemtype,description,rentalcharge,rentalcharge_daily,rentalcharge_hourly,defaultreplacecost,processfee,notforloan,imageurl,summary,checkinmsg,sip_media_type,hideinopac,searchcategory) + VALUES ($itemtype,$description,$rentalcharge,$rentalcharge_daily,$rentalcharge_hourly,$defaultreplacecost,$processfee,$notforloan,$imageurl,$summary,$checkinmsg,$sip_media_type,$hideinopac,$searchcategory)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } else { + $query = "UPDATE itemtypes SET description = $description,rentalcharge = $rentalcharge,rentalcharge_daily = $rentalcharge_daily,rentalcharge_hourly = $rentalcharge_hourly,defaultreplacecost = $defaultreplacecost,processfee = $processfee,notforloan = $notforloan,imageurl = $imageurl,summary = $summary,checkinmsg = $checkinmsg,sip_media_type = $sip_media_type,hideinopac = $hideinopac,searchcategory = $searchcategory + WHERE itemtype = $itemtype"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + } + + foreach my $node ($dom->findnodes('//localizations/value')) { + my $entity = sql_str($node->findvalue('./entity')); + my $code = sql_str($node->findvalue('./code')); + my $lang = sql_str($node->findvalue('./lang')); + my $translation = sql_str($node->findvalue('./translation')); + my $query = "INSERT INTO localization (entity,code,lang,translation) VALUES ($entity,$code,$lang,$translation)"; + $sth = $dbh->prepare($query); + print "$query\n"; + $sth->execute(); + } + + return; +} sub restore_libraries { my $dbh = shift; my $restorefile = shift; my $parser = XML::LibXML->new(); - my $dom = $parser->parse_file($restore_file); + my $dom = $parser->parse_file($restorefile); - #with existant live data will probably need to put a truncate in here + my $query = "TRUNCATE library_groups"; + my $sth = $dbh->prepare($query); + $sth->execute(); foreach my $node ($dom->findnodes('//library')) { + my $branchcode = sql_str($node->findvalue('./branchcode')); + my $branchname = sql_str($node->findvalue('./branchname')); + my $branchaddress1 = sql_str($node->findvalue('./branchaddress1')); + my $branchaddress2 = sql_str($node->findvalue('./branchaddress2')); + my $branchaddress3 = sql_str($node->findvalue('./branchaddress3')); + my $branchzip = sql_str($node->findvalue('./branchzip')); + my $branchcity = sql_str($node->findvalue('./branchcity')); + my $branchstate = sql_str($node->findvalue('./branchstate')); + my $branchcountry = sql_str($node->findvalue('./branchcountry')); + my $branchphone = sql_str($node->findvalue('./branchphone')); + my $branchfax = sql_str($node->findvalue('./branchfax')); + my $branchemail = sql_str($node->findvalue('./branchemail')); + my $branchreplyto = sql_str($node->findvalue('./branchreplyto')); + my $branchreturnpath = sql_str($node->findvalue('./branchreturnpath')); + my $branchurl = sql_str($node->findvalue('./branchurl')); + my $issuing = sql_str($node->findvalue('./issuing')); + my $branchip = sql_str($node->findvalue('./branchip')); + my $branchprinter = sql_str($node->findvalue('./branchprinter')); + my $branchnotes = sql_str($node->findvalue('./branchnotes')); + my $opac_info = sql_str($node->findvalue('./opac_info')); + my $geolocation = sql_str($node->findvalue('./geolocation')); + my $marcorgcode = sql_str($node->findvalue('./marcorgcode')); + my $pickup_location = $node->findvalue('./pickup_location'); + $query = "SELECT branchcode FROM branches WHERE branchcode = $branchcode"; + $sth = $dbh->prepare($query); + $sth->execute(); + my $answer; + while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); } + if (!defined $answer or $answer ne $branchcode) { + $query = "INSERT INTO branches (branchcode,branchname,branchaddress1,branchaddress2,branchaddress3,branchzip,branchcity,branchstate,branchcountry,branchphone,branchfax,branchemail,branchreplyto,branchreturnpath,branchurl,issuing,branchip,branchprinter,branchnotes,opac_info,geolocation,marcorgcode,pickup_location) VALUES ($branchcode,$branchname,$branchaddress1,$branchaddress2,$branchaddress3,$branchzip,$branchcity,$branchstate,$branchcountry,$branchphone,$branchfax,$branchemail,$branchreplyto,$branchreturnpath,$branchurl,$issuing,$branchip,$branchprinter,$branchnotes,$opac_info,$geolocation,$marcorgcode,$pickup_location)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } else { + $query = "UPDATE branches SET branchname = $branchname,branchaddress1 = $branchaddress1,branchaddress2 = $branchaddress2,branchaddress3 = $branchaddress3,branchzip = $branchzip,branchcity = $branchcity,branchstate = $branchstate,branchcountry = $branchcountry,branchphone = $branchphone,branchfax = $branchfax,branchemail = $branchemail,branchreplyto = $branchreplyto,branchreturnpath = $branchreturnpath,branchurl = $branchurl,issuing = $issuing,branchip = $branchip,branchprinter = $branchprinter,branchnotes = $branchnotes,opac_info = $opac_info,geolocation = $geolocation,marcorgcode = $marcorgcode,pickup_location = $pickup_location WHERE branchcode = $branchcode"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + } + + #haven't taken into account them needing to go in, in a specific order so I could check parent ids first + foreach my $node ($dom->findnodes('//library_group')) { my $id = $node->findvalue('./id'); - my $parent_id = $node->findvalue('./parent_id'); + my $parent_id = sql_num($node->findvalue('./parent_id')); my $branchcode = sql_str($node->findvalue('./branchcode')); my $title = sql_str($node->findvalue('./title')); my $descr = sql_str($node->findvalue('./description')); - my $ft1 = $node->findvalue('./ft_hide_patron_info'); - my $ft2 = $node->findvalue('./ft_search_groups_opac'); - my $ft3 = $node->findvalue('./ft_search_groups_staff'); - my $sth = $dbh->prepare($query); - my $query = "INSERT INTO library_groups (id,parent_id,branchcode,title,description,ft_hide_patron_info,ft_search_groups_opac,ft_search_groups_staff) VALUES ($id,$parent_id,$branchcode,$title,$descr,$ft1,$ft2,$ft3)"; - $sth->execute(); + my $ft1 = sql_num($node->findvalue('./ft_hide_patron_info')); + my $ft2 = sql_num($node->findvalue('./ft_search_groups_opac')); + my $ft3 = sql_num($node->findvalue('./ft_search_groups_staff')); + $query = "INSERT INTO library_groups (id,parent_id,branchcode,title,description,ft_hide_patron_info,ft_search_groups_opac,ft_search_groups_staff) VALUES ($id,$parent_id,$branchcode,$title,$descr,$ft1,$ft2,$ft3)"; + $sth = $dbh->prepare($query); + $sth->execute(); } return; } +sub restore_patrontypes { + my $dbh = shift; + my $restorefile = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restorefile); + + my $query; + my $sth; + + foreach my $node ($dom->findnodes('//category')) { + my $categorycode = sql_str($node->findvalue('./categorycode')); + my $description = sql_str($node->findvalue('./description')); + my $enrolmentperiod = sql_num($node->findvalue('./enrolmentperiod')); + my $enrolmentperioddate = sql_str($node->findvalue('./enrolmentperioddate')); + my $upperagelimit = sql_num($node->findvalue('./upperagelimit')); + my $dateofbirthrequired = sql_num($node->findvalue('./dateofbirthrequired')); + my $finetype = sql_str($node->findvalue('./finetype')); + my $bulk = sql_num($node->findvalue('./bulk')); + my $enrolmentfee = sql_num($node->findvalue('./enrolmentfee')); + my $overduenoticerequired = sql_num($node->findvalue('./overduenoticerequired')); + my $issuelimit = sql_num($node->findvalue('./issuelimit')); + my $reservefee = sql_num($node->findvalue('./reservefee')); + my $hidelostitems = sql_num($node->findvalue('./hidelostitems')); + my $category_type = sql_str($node->findvalue('./category_type')); + my $BlockExpiredPatronOpacActions = sql_num($node->findvalue('./BlockExpiredPatronOpacActions')); + my $default_privacy = sql_str($node->findvalue('./default_privacy')); + my $checkprevcheckout = sql_str($node->findvalue('./checkprevcheckout')); + my $reset_password = sql_num($node->findvalue('./reset_password')); + my $change_password = sql_num($node->findvalue('./change_password')); + $query = "SELECT categorycode FROM categories WHERE categorycode = $categorycode"; + $sth = $dbh->prepare($query); + $sth->execute(); + my $answer; + while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); } + if (!defined $answer or $answer ne $categorycode) { + $query = "INSERT INTO categories (categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,finetype,bulk,enrolmentfee,overduenoticerequired,issuelimit,reservefee,hidelostitems,category_type,BlockExpiredPatronOpacActions,default_privacy,checkprevcheckout,reset_password,change_password) VALUES ($categorycode,$description,$enrolmentperiod,$enrolmentperioddate,$upperagelimit,$dateofbirthrequired,$finetype,$bulk,$enrolmentfee,$overduenoticerequired,$issuelimit,$reservefee,$hidelostitems,$category_type,$BlockExpiredPatronOpacActions,$default_privacy,$checkprevcheckout,$reset_password,$change_password)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } else { + $query = "UPDATE categories SET description = $description,enrolmentperiod = $enrolmentperiod,enrolmentperioddate = $enrolmentperioddate,upperagelimit = $upperagelimit,dateofbirthrequired = $dateofbirthrequired,finetype = $finetype,bulk = $bulk,enrolmentfee = $enrolmentfee,overduenoticerequired = $overduenoticerequired,issuelimit = $issuelimit,reservefee = $reservefee,hidelostitems = $hidelostitems,category_type = $category_type,BlockExpiredPatronOpacActions = $BlockExpiredPatronOpacActions,default_privacy = $default_privacy,checkprevcheckout = $checkprevcheckout,reset_password = $reset_password,change_password = $change_password WHERE categorycode = $categorycode"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + } + return; +} + sub restore_preferences { my $dbh = shift; my $restore_file = shift; @@ -96,14 +447,39 @@ sub restore_preferences { return; } +sub restore_smsproviders { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM sms_providers WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + foreach my $node ($dom->findnodes('//provider')) { + my $name = sql_str($node->findvalue('./name')); + my $domain = sql_str($node->findvalue('./domain')); + $query = "INSERT INTO sms_providers (name,domain) VALUES ($name,$domain)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + return; +} + sub sql_str { my $str = shift; - if (!defined $str or $str eq '') { return ''; } + if (!defined $str or $str eq '') { return 'NULL'; } $str =~ s/'/''/g; $str = '\'' . $str . '\''; return $str; } +sub sql_num { + my $str = shift; + if (!defined $str or $str eq '') { return 'NULL'; } else { return $str; } +} + sub abort { my $msg = shift; print STDERR "$0: $msg", "\n"; -- 1.7.2.5