X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=kmig.d%2Fbin%2Fmig-import;h=a7df5acb2855fb4c35f645266bf689725a18920f;hp=6788d2852328be480fe2c5b0f745ef956b1f9aed;hb=HEAD;hpb=67fd0891594b6381eec00eee3434690404beb1a6

diff --git a/kmig.d/bin/mig-import b/kmig.d/bin/mig-import
index 6788d28..a7df5ac 100755
--- a/kmig.d/bin/mig-import
+++ b/kmig.d/bin/mig-import
@@ -25,13 +25,16 @@ 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 $mig_path = abs_path($0);
+$mig_path =~ s|[^/]+$||;
 
 my @taglist = @ARGV;
 my $arg_list_length = scalar @taglist;
-if($arg_list_length < 1) { @taglist = ("authorisedvalues","calendar","circrules","itemtypes","libraries","patrontypes","preferences","smsproviders"); } 
+if($arg_list_length < 1) { @taglist = ("libraries","patrontypes","itemtypes","authorisedvalues","booksellers","borrowerattributes","budgets","calendar","circrules","letters","messages","preferences","reports","smsproviders"); } 
 $MIGGITDIR =~ s/\/\//\//;
 
+my @errors;
+
 foreach my $restore (@taglist) {
     my $restorefile;
     if ($restore eq 'authorisedvalues') {
@@ -40,6 +43,24 @@ foreach my $restore (@taglist) {
         print "Restoring from $restorefile ... \n";
         if ($restorefile) { restore_authorisedvalues($dbh,$restorefile); }
     }
+    if ($restore eq 'booksellers') {
+        my $timestamp = most_recent_single($MIGGITDIR,'booksellers');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'booksellers' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_booksellers($dbh,$restorefile); }
+    }
+    if ($restore eq 'borrowerattributes') {
+        my $timestamp = most_recent_single($MIGGITDIR,'borrowerattributes');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'borrowerattributes' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_borrowerattributes($dbh,$restorefile); }
+    }
+    if ($restore eq 'budgets') {
+        my $timestamp = most_recent_single($MIGGITDIR,'budgets');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'budgets' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_budgets($dbh,$restorefile); }
+    }
     if ($restore eq 'calendar') {
         my $timestamp = most_recent_single($MIGGITDIR,'calendar');
         if ($timestamp) { $restorefile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; }
@@ -50,13 +71,19 @@ foreach my $restore (@taglist) {
         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 ($restorefile) { @errors = restore_circrules($dbh,$restorefile,@errors); }
     }
     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 ($restorefile) { @errors = restore_itemtypes($dbh,$restorefile,@errors); }
+    }
+    if ($restore eq 'letters') {
+        my $timestamp = most_recent_single($MIGGITDIR,'letters');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'letters' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_letters($dbh,$restorefile); }
     }
     if ($restore eq 'libraries') {
         my $timestamp = most_recent_single($MIGGITDIR,'libraries');
@@ -64,6 +91,12 @@ foreach my $restore (@taglist) {
         print "Restoring from $restorefile ... \n";
         if ($restorefile) { restore_libraries($dbh,$restorefile); }
     }
+    if ($restore eq 'messages') {
+        my $timestamp = most_recent_single($MIGGITDIR,'messages');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'messages' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_messages($dbh,$restorefile); }
+    }
     if ($restore eq 'patrontypes') {
         my $timestamp = most_recent_single($MIGGITDIR,'patrontypes');
         if ($timestamp) { $restorefile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml'; }
@@ -75,18 +108,80 @@ foreach my $restore (@taglist) {
         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 'reports') {
+        my $timestamp = most_recent_single($MIGGITDIR,'reports');
+        if ($timestamp) { $restorefile = $MIGGITDIR . 'reports' . '.' . $timestamp . '.xml'; }
+        print "Restoring from $restorefile ... \n";
+        if ($restorefile) { restore_reports($dbh,$restorefile); }
     }
     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); }
+        if ($restorefile) { restore_smsproviders($dbh,$restorefile,$mig_path); }
     }
 }
 
+reset_memcache($MIGSCHEMA);
+
 print "Done.\n";
 
+foreach my $e (@errors) { print "$e\n"; }
+
+# ---------------------------
+
+sub check_branchcode {
+    my $dbh = shift;
+    my $value = shift;
+    my $query;
+    
+    if ($value and $value ne 'NULL') {
+        $query = "SELECT EXISTS(SELECT 1 FROM branches WHERE branchcode = $value)";
+    } else {
+        return 1;
+    }
+    print "$query\n";
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+    my @sqlresult = $sth->fetchrow_array;
+    my $result = pop @sqlresult;
+    return $result;
+}
+
+sub check_category {
+    my $dbh = shift;
+    my $value = shift;
+	my $query;
+
+    if ($value and $value ne 'NULL') {
+    	$query = "SELECT EXISTS(SELECT 1 FROM categories WHERE categorycode = $value)";
+	} else {
+		return 1;
+	}
+	print "$query\n";
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+    my @sqlresult = $sth->fetchrow_array;
+    my $result = pop @sqlresult;
+    return $result;
+}
+
+sub check_itemtype {
+    my $dbh = shift;
+    my $value = shift;
+	my $query;
+    if ($value and $value ne 'NULL') {
+        $query = "SELECT EXISTS(SELECT 1 FROM itemtypes WHERE itemtype = $value)";
+    } else {
+		return 1;
+    }
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+    my @sqlresult = $sth->fetchrow_array;
+    my $result = pop @sqlresult;
+    return $result;
+}
 
 sub most_recent_single {
     my $MGIGITDIR = shift;
@@ -106,42 +201,194 @@ sub most_recent_single {
     return $max;
 }
 
+sub reset_memcache {
+	my $instance = shift;
+
+	my $aptcheck = `sudo dpkg -l | grep 'libmemcached-tools'`;
+	if (!defined $aptcheck or length($aptcheck) < 1) { 
+		print "!!! !!! !!! WARNING WARNING WARNING: libmemcached-tools is not installed, removing keys will not be attempted\n"; 
+		return;
+	}
+	
+	system("sudo memcdump --servers localhost | grep '$instance:' | grep 'syspref\|MarcStructure\|AuthorisedValues\|MarcSubfieldStructure' | xargs memcrm --servers localhost");	
+	system("sudo koha-plack --restart $instance");
+
+	return;
+}
+
 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();
+    sql_noresult($dbh,"DELETE FROM authorised_values WHERE category != 'ITEMTYPECAT'");
+
+    sql_noresult($dbh,"DELETE FROM authorised_value_categories WHERE category_name != 'ITEMTYPECAT'");
 
-    foreach my $node ($dom->findnodes('//value')) {
+    foreach my $node ($dom->findnodes('/document/categories/value')) {
+        my $name = sql_str_not_null($node->findvalue('./name'));
+        sql_noresult($dbh,"INSERT INTO authorised_value_categories (category_name) VALUES ($name)");
+    }
+
+    foreach my $node ($dom->findnodes('/document/authorisedvalues/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();
+        sql_noresult($dbh,"INSERT INTO authorised_values (category,authorised_value,lib,lib_opac,imageurl) VALUES ($category,$authorised_value,$lib,$lib_opac,$imageurl)");
     }
     return;
 }
 
+sub restore_booksellers {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM aqbooksellers WHERE 1 = 1");
+
+    foreach my $node ($dom->findnodes('/booksellers/value')) {
+        my $name = sql_str($node->findvalue('./name'));
+        my $address1 = sql_str($node->findvalue('./address1'));
+        my $address2 = sql_str($node->findvalue('./address2'));
+        my $address3 = sql_str($node->findvalue('./address3'));
+        my $address4 = sql_str($node->findvalue('./address4'));
+        my $phone = sql_str($node->findvalue('./phone'));
+        my $accountnumber = sql_str($node->findvalue('./accountnumber'));
+        my $othersupplier = sql_str($node->findvalue('./othersupplier'));
+        my $currency = sql_str_not_null($node->findvalue('./currency'));
+        my $booksellerfax = sql_str($node->findvalue('./booksellerfax'));
+        my $notes = sql_str($node->findvalue('./notes'));
+        my $bookselleremail = sql_str($node->findvalue('./bookselleremail'));
+        my $booksellerurl = sql_str($node->findvalue('./booksellerurl'));
+        my $postal = sql_str($node->findvalue('./postal'));
+        my $url = sql_str($node->findvalue('./url'));
+        my $active = sql_str($node->findvalue('./active'));
+        my $listprice = sql_str($node->findvalue('./listprice'));
+        my $invoiceprice  = sql_str($node->findvalue('./invoiceprice'));
+        my $gstreg = sql_num($node->findvalue('./gstreg'));
+        my $listincgst = sql_num($node->findvalue('./listincgst'));
+        my $invoiceincgst = sql_num($node->findvalue('./invoiceincgst'));
+        my $tax_rate = sql_num($node->findvalue('./tax_rate'));
+        my $discount = sql_num($node->findvalue('./discount'));
+        my $fax = sql_str($node->findvalue('./fax'));
+        my $deliverytime = sql_num($node->findvalue('./deliverytime'));
+        sql_noresult($dbh,"INSERT INTO aqbooksellers (name,address1,address2,address3,address4,phone,accountnumber,othersupplier,currency,booksellerfax,notes,bookselleremail,booksellerurl,postal,url,active,listprice,invoiceprice,gstreg,listincgst,invoiceincgst,tax_rate,discount,fax,deliverytime) VALUES ($name,$address1,$address2,$address3,$address4,$phone,$accountnumber,$othersupplier,$currency,$booksellerfax,$notes,$bookselleremail,$booksellerurl,$postal,$url,$active,$listprice,$invoiceprice,$gstreg,$listincgst,$invoiceincgst,$tax_rate,$discount,$fax,$deliverytime)");
+    }
+
+    return;
+}
+
+sub restore_borrowerattributes {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM authorised_values WHERE category IN (select category_code from borrower_attribute_types)");
+
+    sql_noresult($dbh,"DELETE FROM authorised_value_categories WHERE category_name IN (select category_name from borrower_attribute_types)");
+
+    sql_noresult($dbh,"DELETE FROM borrower_attribute_types WHERE 1 = 1");
+
+    foreach my $node ($dom->findnodes('/borrower_attribute_types/value')) {
+        my $code = sql_str($node->findvalue('./code'));
+        my $description = sql_num($node->findvalue('./description'));
+        my $repeatable = sql_num($node->findvalue('./repeat'));
+        my $opac_display = sql_num($node->findvalue('./opac_display'));
+        my $staff_searchable = sql_num($node->findvalue('./staff_searchable'));
+        my $authorised_value_category = sql_str($node->findvalue('./auth_value_cat'));
+        my $display_checkout = sql_num($node->findvalue('./display_checkout'));
+        my $category_code = sql_str($node->findvalue('./category_code'));
+        my $class = sql_str($node->findvalue('./class'));
+        sql_noresult($dbh,"INSERT INTO borrower_attribute_types (code,description,repeatable,opac_display,staff_searchable,authorised_value_category,display_checkout,category_code,class) VALUES ($code,$description,$repeatable,$opac_display,$staff_searchable,$authorised_value_category,$display_checkout,$category_code,$class)");
+    }
+
+    foreach my $node ($dom->findnodes('/authorised_value_categories/value')) {
+        my $category_name = sql_str($node->nodeValue);
+        sql_noresult($dbh,"INSERT INTO authorised_value_categories (category_name) VALUES ($category_name)");
+    }
+
+    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'));
+        sql_noresult($dbh,"INSERT INTO authorised_values (category,authvalue,lib,lib_opac,image_url) VALUES ($category,$authvalue,$lib,$lib_opac,$image_url)");
+    }
+
+    return;
+}
+
+sub restore_budgets {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM aqbudgets WHERE 1 = 1");
+    sql_noresult($dbh,"DELETE FROM aqbudgetperiods WHERE 1 = 1");
+
+    my $budget_ai;
+    my $budgetperiod_ai;
+    foreach my $node ($dom->findnodes('/document/budgets')) {
+        $budget_ai = sql_num($node->findvalue('./autoincrement'));
+    }
+    foreach my $node ($dom->findnodes('/document/budgetperiods')) {
+        $budgetperiod_ai = sql_num($node->findvalue('./autoincrement'));
+    }
+    sql_noresult($dbh,"ALTER TABLE aqbudgets AUTO_INCREMENT = $budget_ai");
+    sql_noresult($dbh,"ALTER TABLE aqbudgetperiods AUTO_INCREMENT = $budgetperiod_ai");
+
+    foreach my $node ($dom->findnodes('/document/budgetperiods/value')) {
+        my $budget_period_id = sql_num($node->findvalue('./budget_period_id'));
+        my $budget_period_startdate = sql_str($node->findvalue('./budget_period_startdate'));
+        my $budget_period_enddate = sql_str($node->findvalue('./budget_period_enddate'));
+        my $budget_period_active = sql_str($node->findvalue('./budget_period_active'));
+        my $budget_period_description = sql_str($node->findvalue('./budget_period_description'));
+        my $budget_period_total = sql_num($node->findvalue('./budget_period_total'));
+        my $budget_period_locked = sql_num($node->findvalue('./budget_period_locked'));
+        my $sort1_authcat = sql_str($node->findvalue('./sort1_authcat'));
+        my $sort2_authcat = sql_str($node->findvalue('./sort2_authcat'));
+        sql_noresult($dbh,"INSERT INTO aqbudgetperiods (budget_period_id,budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_total,budget_period_locked,sort1_authcat,sort2_authcat) VALUES ($budget_period_id,$budget_period_startdate,$budget_period_enddate,$budget_period_active,$budget_period_description,$budget_period_total,$budget_period_locked,$sort1_authcat,$sort2_authcat)");
+    }
+
+    #note that at this point we are not loading the budget owner, it's nullable and not 100% clear that it's the borrower
+    #and nothing to test with in initial sample
+    foreach my $node ($dom->findnodes('/document/budgets/value')) {
+        my $budget_id = sql_num($node->findvalue('./budget_id'));
+        my $budget_parent_id = sql_num($node->findvalue('./budget_parent_id'));
+        my $budget_code = sql_str($node->findvalue('./budget_code'));
+        my $budget_name = sql_str($node->findvalue('./budget_name'));
+        my $budget_branchcode = sql_str_not_null($node->findvalue('./budget_branchcode'));
+        my $budget_amount = sql_num($node->findvalue('./budget_amount'));
+        my $budget_encumb = sql_num($node->findvalue('./budget_encumb'));
+        my $budget_expend = sql_num($node->findvalue('./budget_expend'));
+        my $budget_notes = sql_str_not_null($node->findvalue('./budget_notes'));
+        my $timestamp = sql_str($node->findvalue('./timestamp'));
+        my $budget_period_id = sql_num($node->findvalue('./budget_period_id'));
+        my $sort1_authcat = sql_str_not_null($node->findvalue('./sort1_authcat'));
+        my $sort2_authcat = sql_str_not_null($node->findvalue('./sort2_authcat'));
+        my $budget_permission = sql_num($node->findvalue('./budget_permission'));
+        sql_noresult($dbh,"INSERT INTO aqbudgets (budget_id,budget_parent_id,budget_code,budget_name,budget_branchcode,budget_amount,budget_encumb,budget_expend,budget_notes,timestamp,budget_period_id,sort1_authcat,sort2_authcat,budget_permission) VALUES ($budget_id,$budget_parent_id,$budget_code,$budget_name,$budget_branchcode,$budget_amount,$budget_encumb,$budget_expend,$budget_notes,$timestamp,$budget_period_id,$sort1_authcat,$sort2_authcat,$budget_permission)");
+    }
+
+    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();
+    sql_noresult($dbh,"DELETE FROM repeatable_holidays WHERE 1 = 1");
 
-    $query = "DELETE FROM special_holidays WHERE 1 = 1";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    sql_noresult($dbh,"DELETE FROM special_holidays WHERE 1 = 1");
 
     foreach my $node ($dom->findnodes('/holidays/repeatable')) {
         my $branchcode = sql_str($node->findvalue('./branchcode'));
@@ -150,9 +397,7 @@ sub restore_calendar {
         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();
+        sql_noresult($dbh,"INSERT INTO repeatable_holidays (branchcode,weekday,day,month,title,description) VALUES ($branchcode,$weekday,$day,$month,$title,$description)");
     }
 
     foreach my $node ($dom->findnodes('/holidays/special')) {
@@ -163,9 +408,7 @@ sub restore_calendar {
         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();
+        sql_noresult($dbh,"INSERT INTO special_holidays (branchcode,day,month,year,isexception,title,description) VALUES ($branchcode,$day,$month,$year,$isexception,$title,$description)");
     }
 
     return;
@@ -174,18 +417,16 @@ sub restore_calendar {
 sub restore_circrules {
     my $dbh = shift;
     my $restore_file = shift;
+    my @errors = shift;
     my $parser = XML::LibXML->new();
     my $dom = $parser->parse_file($restore_file);
- 
+    my $error;
+	my $check; 
     #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();
+    sql_noresult($dbh,"DELETE FROM circulation_rules WHERE 1 = 1");
 
-    $query = "DELETE FROM issuingrules WHERE 1 = 1";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    sql_noresult($dbh,"DELETE FROM issuingrules WHERE 1 = 1");
 
     foreach my $node ($dom->findnodes('//issuing/rule')) {
         my $categorycode = sql_str($node->findvalue('./categorycode'));
@@ -221,9 +462,19 @@ sub restore_circrules {
         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();
+		$check = check_itemtype($dbh,$itemtype);
+        if ($check == 0 ) {
+            $error = "restore_circrules: issing rules rules itemtype $itemtype was not valid";
+            push @errors, $error;
+            next;
+        }
+		$check = check_category($dbh,$categorycode);
+        if ($check == 0 ) {
+            $error = "restore_circrules: issuing rules rules category $categorycode was not valid";
+            push @errors, $error;
+            next;
+        }
+        sql_noresult($dbh,"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)");
     }
 
     foreach my $node ($dom->findnodes('//circ/rule')) {
@@ -232,44 +483,49 @@ sub restore_circrules {
         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;
+		$check = check_itemtype($dbh,$itemtype);
+        if ($check == 0 ) { 
+            $error = "restore_circrules: circulation rules rules itemtype $itemtype was not valid"; 
+            push @errors, $error;
+            next; 
+        }
+		$check = check_category($dbh,$categorycode);
+        if ($check == 0 ) { 
+            $error = "restore_circrules: circulation rules rules category $categorycode was not valid";
+            push @errors, $error;
+            next; 
+        }
+
+        sql_noresult($dbh,"INSERT INTO circulation_rules (branchcode,categorycode,itemtype,rule_name,rule_value) VALUES ($branchcode,$categorycode,$itemtype,$rule_name,$rule_value)");
+    }
+     
+    return @errors;
 }
 
 sub restore_itemtypes {
     my $dbh = shift;
     my $restorefile = shift;
+	my @errors = shift;
     my $parser = XML::LibXML->new();
     my $dom = $parser->parse_file($restorefile);
+	my $check;
 
-    my $query = "UPDATE itemtypes SET searchcategory = NULL";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    sql_noresult($dbh,"UPDATE itemtypes SET searchcategory = NULL");
+    sql_noresult($dbh,"DELETE FROM authorised_values WHERE category = 'ITEMTYPECAT'");
+    sql_noresult($dbh,"DELETE FROM localization WHERE entity = 'itemtypes'");
+    sql_noresult($dbh,"DELETE FROM itemtypes WHERE 1 = 1");
 
-    $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')) {
+    foreach my $node ($dom->findnodes('/document/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();
+        sql_noresult($dbh,"INSERT INTO authorised_values (category,authvalue,lib,lib_opac,image_url) VALUES ($category,$authvalue,$lib,$lib_opac,$image_url)");
     }
 
-    foreach my $node ($dom->findnodes('/itemtypes/value')) {
+    foreach my $node ($dom->findnodes('/document/itemtypes/value')) {
         my $itemtype = sql_str($node->findvalue('./itemtype'));
         my $description = sql_str($node->findvalue('./description'));
         my $rentalcharge = sql_str($node->findvalue('./rentalcharge'));
@@ -285,21 +541,12 @@ sub restore_itemtypes {
         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();
+
+		$check = check_itemtype($dbh,$itemtype);
+        if ($check == 0) {
+            sql_noresult($dbh,"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)");
         } 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();
+            sql_noresult($dbh,"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");
         }
     }
 
@@ -308,10 +555,31 @@ sub restore_itemtypes {
         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();
+        sql_noresult($dbh,"INSERT INTO localization (entity,code,lang,translation) VALUES ($entity,$code,$lang,$translation)");
+    }
+
+    return;
+}
+
+sub restore_letters {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM letter WHERE 1 = 1");
+
+    foreach my $node ($dom->findnodes('/letters/letter')) {
+        my $module = sql_str($node->findvalue('./module'));
+        my $code = sql_str($node->findvalue('./code'));
+        my $branchcode = sql_str_not_null($node->findvalue('./branchcode'));
+        my $name = sql_str($node->findvalue('./name'));
+        my $is_html = sql_num($node->findvalue('./is_html'));
+        my $title = sql_str($node->findvalue('./title'));
+        my $content = sql_str($node->findvalue('./content'));
+        my $message_transport_type = sql_str($node->findvalue('./message_transport_type'));
+        my $lang = sql_str($node->findvalue('./lang'));
+        sql_noresult($dbh,"INSERT INTO letter (module,code,branchcode,name,is_html,title,content,message_transport_type,lang) VALUES ($module,$code,$branchcode,$name,$is_html,$title,$content,$message_transport_type,$lang)");
     }
 
     return;
@@ -322,6 +590,7 @@ sub restore_libraries {
     my $restorefile = shift;
     my $parser = XML::LibXML->new();
     my $dom = $parser->parse_file($restorefile);
+	my $check;
 
     my $query = "TRUNCATE library_groups";
     my $sth = $dbh->prepare($query);
@@ -351,19 +620,11 @@ sub restore_libraries {
         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();
+		$check = check_branchcode($dbh,$branchcode);
+        if ($check == 0) { 
+            sql_noresult($dbh,"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)");
         } 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();  
+            sql_noresult($dbh,"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");
         }
     }
 
@@ -377,19 +638,60 @@ sub restore_libraries {
         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();
+        sql_noresult($dbh,"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)");
     }
     return;
 }
 
+sub restore_messages {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM message_transports WHERE 1 = 1");
+    sql_noresult($dbh,"DELETE FROM message_transport_types WHERE 1 = 1");
+    sql_noresult($dbh,"DELETE FROM message_attributes WHERE 1 = 1");
+    
+    my $attr_ai;
+    foreach my $node ($dom->findnodes('/document/message_attributes')) {
+        $attr_ai = sql_num($node->findvalue('./autoincrement'));
+    }
+    sql_noresult($dbh,"ALTER TABLE message_attributes AUTO_INCREMENT = $attr_ai");
+
+    foreach my $node ($dom->findnodes('/document/message_attributes/value')) {
+        my $message_attribute_id = sql_num($node->findvalue('./message_attribute_id'));
+        my $message_name = sql_num($node->findvalue('./message_name'));
+        my $takes_days = sql_num($node->findvalue('./takes_days'));
+        sql_noresult($dbh,"INSERT INTO message_attributes (message_attribute_id,message_name,takes_days) VALUES ($message_attribute_id,$message_name,$takes_days)");
+    }
+
+    foreach my $node ($dom->findnodes('/document/message_transport_types/value')) {
+        my $message_transport_type = sql_num($node->findvalue('./message_attribute_id'));
+        sql_noresult($dbh,"INSERT INTO message_transport_types (message_transport_type) VALUES ($message_transport_type)");
+    }
+
+    foreach my $node ($dom->findnodes('/document/message_transports/value')) {
+        my $message_attribute_id = sql_num($node->findvalue('./message_attribute_id'));
+        my $message_transport_type = sql_num($node->findvalue('./message_transport_type'));
+        my $is_digest = sql_num($node->findvalue('./is_digest'));
+        my $letter_module = sql_num($node->findvalue('./letter_module'));
+        my $letter_code = sql_num($node->findvalue('./letter_code'));
+        my $branchcode = sql_num($node->findvalue('./branchcode'));
+        sql_noresult($dbh,"INSERT INTO message_transports (message_attribute_id,message_transport_type,is_digest,letter_module,letter_code,branchcode) VALUES ($message_attribute_id,$message_transport_type,$is_digest,$letter_module,$letter_code,$branchcode)");
+    }
+
+    return;
+}
+
+
+
 sub restore_patrontypes {
     my $dbh = shift;
     my $restorefile = shift;
     my $parser = XML::LibXML->new();
     my $dom = $parser->parse_file($restorefile);
-
+	my $check;
     my $query;
     my $sth;
 
@@ -413,19 +715,13 @@ sub restore_patrontypes {
         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)";
+		$check = check_category($dbh,$categorycode);    
+        if ($check == 0) {
+            sql_noresult($dbh,"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();
+            sql_noresult($dbh,"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");
         }
     }
    return;
@@ -438,11 +734,40 @@ sub restore_preferences {
     my $dom = $parser->parse_file($restore_file);
 
     foreach my $node ($dom->findnodes('//pref')) {
-	my $variable = sql_str($node->findvalue('./variable'));
-        my $value = sql_str($node->findvalue('./value'));
-        my $query = "UPDATE systempreferences SET value = $value WHERE variable = $variable";
-        my $sth = $dbh->prepare($query);
-        $sth->execute();
+		my $variable = domain_shift(sql_str_not_null($node->findvalue('./variable')));
+        my $value = domain_shift(sql_str_not_null($node->findvalue('./value')));
+        sql_noresult($dbh,"UPDATE systempreferences SET value = $value WHERE variable = $variable");
+    }
+    return;
+}
+
+sub restore_reports {
+    my $dbh = shift;
+    my $restore_file = shift;
+    my $parser = XML::LibXML->new();
+    my $dom = $parser->parse_file($restore_file);
+
+    sql_noresult($dbh,"DELETE FROM saved_sql WHERE 1 = 1");
+
+    foreach my $node ($dom->findnodes('//sqlreport')) {
+        my $date_created = sql_str($node->findvalue('./date_created'));
+        my $last_modified = sql_str($node->findvalue('./last_modified'));
+        my $savedsql = domain_shift(sql_str($node->findvalue('./savedsql')));
+        my $report_name = sql_str($node->findvalue('./report_name'));
+        my $type = sql_str($node->findvalue('./type'));
+        my $notes = sql_str($node->findvalue('./notes'));
+        my $cache_expiry = sql_num($node->findvalue('./cache_expiry'));
+        my $public = sql_str($node->findvalue('./public'));
+        my $report_area = sql_str($node->findvalue('./report_area'));
+        my $report_group = sql_str($node->findvalue('./report_group'));
+        my $report_subgroup = sql_str($node->findvalue('./report_subgroup'));
+        my $userid = sql_str($node->findvalue('./userid'));
+
+        my $sth = sql_giveback($dbh,"SELECT borrowernumber FROM borrowers WHERE userid = $userid");
+        my @row = $sth->fetchrow_array;
+        my $borrowernumber = sql_num($row[0]);
+
+        sql_noresult($dbh,"INSERT INTO saved_sql (date_created,last_modified,savedsql,report_name,type,notes,cache_expiry,public,report_area,report_group,report_subgroup,borrowernumber) VALUES ($date_created,$last_modified,$savedsql,$report_name,$type,$notes,$cache_expiry,$public,$report_area,$report_group,$report_subgroup,$borrowernumber)");
     }
     return;
 }
@@ -450,23 +775,51 @@ sub restore_preferences {
 sub restore_smsproviders {
     my $dbh = shift;
     my $restore_file = shift;
+    my $migpath = 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();
+    sql_noresult($dbh,"DELETE FROM sms_providers WHERE 1 = 1");
+
+    my $p = 0;
+    foreach my $node ($dom->findnodes('//provider')) { $p++; }
+
+    if ($p == 0) {
+        my $failover_file = find_xml('smsproviders.xml',$migpath);        
+        $dom = $parser->parse_file($failover_file);        
+    }
 
     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();
+        sql_noresult($dbh,"INSERT INTO sms_providers (name,domain) VALUES ($name,$domain)");
     }
     return;
 }
 
+sub domain_shift {
+	my $str = shift;
+	$str =~ s/-test.kohacatalog.com/.kohacatalog.com/g;
+	$str =~ s/-test-staff.kohacatalog.com/-staff.kohacatalog.com/g;
+	return $str;
+}
+
+sub sql_giveback {
+    my $dbh = shift;
+    my $query = shift;
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+    return $sth;
+}
+
+sub sql_noresult {
+    my $dbh = shift;
+    my $statement = shift;
+    my $sth = $dbh->prepare($statement);
+    $sth->execute();
+    return;
+}
+
 sub sql_str {
     my $str = shift;
     if (!defined $str or $str eq '') { return 'NULL'; }
@@ -475,6 +828,13 @@ sub sql_str {
     return $str;
 }
 
+sub sql_str_not_null {
+    my $str = shift;
+    $str = sql_str($str);
+    if (!defined $str or $str eq 'NULL') { return "''"; }
+    return $str;
+}
+
 sub sql_num {
     my $str = shift;
     if (!defined $str or $str eq '') { return 'NULL'; } else { return $str; }
@@ -487,6 +847,22 @@ sub abort {
     exit 1;
 }
 
+sub find_xml {
+    my $reports_xml = shift;
+    my $mig_path = shift;
+
+    if ($reports_xml =~ m/\//) { return $reports_xml; }
+
+    my $mig_test_file =  $mig_path . '/../xml/' . $reports_xml;
+    my $working_test_dir = getcwd();
+    my $working_test_file = $working_test_dir . '/' . $reports_xml;
+
+    if (-e $mig_test_file) { return $mig_test_file; }
+    if (-e $working_test_file) { return $working_test_file; }
+
+    return undef;
+}
+
 sub print_usage {
     print <<_USAGE_;