9 HOME MYSQL_HOST MYSQL_TCP_PORT MYSQL_USER MYSQL_DATABASE MYSQL_PW
10 MIGSCHEMA MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
12 use open ':encoding(utf8)';
17 use open ':std', ':encoding(UTF-8)';
18 use Encode qw( decode_utf8 );
19 use List::Util qw( min max );
20 my $mig_bin = "$FindBin::Bin/";
21 use lib "$FindBin::Bin/";
24 my $dbh = KMig::db_connect();
25 #$dbh->{mysql_enable_utf8mb4} = 1;
26 $dbh->do('SET NAMES utf8mb4');
28 #to do check for array passed and if not present then use tags
31 my $arg_list_length = scalar @taglist;
32 if($arg_list_length < 1) { @taglist = ("authorisedvalues","booksellers","borrowerattributes","budgets","calendar","circrules","itemtypes","letters","libraries","patrontypes","preferences","reports","smsproviders"); }
33 $MIGGITDIR =~ s/\/\//\//;
35 foreach my $restore (@taglist) {
37 if ($restore eq 'authorisedvalues') {
38 my $timestamp = most_recent_single($MIGGITDIR,'authorisedvalues');
39 if ($timestamp) { $restorefile = $MIGGITDIR . 'authorisedvalues' . '.' . $timestamp . '.xml'; }
40 print "Restoring from $restorefile ... \n";
41 if ($restorefile) { restore_authorisedvalues($dbh,$restorefile); }
43 if ($restore eq 'booksellers') {
44 my $timestamp = most_recent_single($MIGGITDIR,'booksellers');
45 if ($timestamp) { $restorefile = $MIGGITDIR . 'booksellers' . '.' . $timestamp . '.xml'; }
46 print "Restoring from $restorefile ... \n";
47 if ($restorefile) { restore_booksellers($dbh,$restorefile); }
49 if ($restore eq 'borrowerattributes') {
50 my $timestamp = most_recent_single($MIGGITDIR,'borrowerattributes');
51 if ($timestamp) { $restorefile = $MIGGITDIR . 'borrowerattributes' . '.' . $timestamp . '.xml'; }
52 print "Restoring from $restorefile ... \n";
53 if ($restorefile) { restore_borrowerattributes($dbh,$restorefile); }
55 if ($restore eq 'budgets') {
56 my $timestamp = most_recent_single($MIGGITDIR,'budgets');
57 if ($timestamp) { $restorefile = $MIGGITDIR . 'budgets' . '.' . $timestamp . '.xml'; }
58 print "Restoring from $restorefile ... \n";
59 if ($restorefile) { restore_budgets($dbh,$restorefile); }
61 if ($restore eq 'calendar') {
62 my $timestamp = most_recent_single($MIGGITDIR,'calendar');
63 if ($timestamp) { $restorefile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; }
64 print "Restoring from $restorefile ... \n";
65 if ($restorefile) { restore_calendar($dbh,$restorefile); }
67 if ($restore eq 'circrules') {
68 my $timestamp = most_recent_single($MIGGITDIR,'circrules');
69 if ($timestamp) { $restorefile = $MIGGITDIR . 'circrules' . '.' . $timestamp . '.xml'; }
70 print "Restoring from $restorefile ... \n";
71 if ($restorefile) { restore_circrules($dbh,$restorefile); }
73 if ($restore eq 'itemtypes') {
74 my $timestamp = most_recent_single($MIGGITDIR,'itemtypes');
75 if ($timestamp) { $restorefile = $MIGGITDIR . 'itemtypes' . '.' . $timestamp . '.xml'; }
76 print "Restoring from $restorefile ... \n";
77 if ($restorefile) { restore_itemtypes($dbh,$restorefile); }
79 if ($restore eq 'letters') {
80 my $timestamp = most_recent_single($MIGGITDIR,'letters');
81 if ($timestamp) { $restorefile = $MIGGITDIR . 'letters' . '.' . $timestamp . '.xml'; }
82 print "Restoring from $restorefile ... \n";
83 if ($restorefile) { restore_letters($dbh,$restorefile); }
85 if ($restore eq 'libraries') {
86 my $timestamp = most_recent_single($MIGGITDIR,'libraries');
87 if ($timestamp) { $restorefile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml'; }
88 print "Restoring from $restorefile ... \n";
89 if ($restorefile) { restore_libraries($dbh,$restorefile); }
91 if ($restore eq 'patrontypes') {
92 my $timestamp = most_recent_single($MIGGITDIR,'patrontypes');
93 if ($timestamp) { $restorefile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml'; }
94 print "Restoring from $restorefile ... \n";
95 if ($restorefile) { restore_patrontypes($dbh,$restorefile); }
97 if ($restore eq 'preferences') {
98 my $timestamp = most_recent_single($MIGGITDIR,'systempreferences');
99 if ($timestamp) { $restorefile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; }
100 print "Restoring from $restorefile ... \n";
101 if ($restorefile) { restore_preferences($dbh,$restorefile); }
102 print "IMPORTANT : if you are changing system preferences you may need to run 'sudo systemctl restart memcached'\n\n";
104 if ($restore eq 'reports') {
105 my $timestamp = most_recent_single($MIGGITDIR,'reports');
106 if ($timestamp) { $restorefile = $MIGGITDIR . 'reports' . '.' . $timestamp . '.xml'; }
107 print "Restoring from $restorefile ... \n";
108 if ($restorefile) { restore_reports($dbh,$restorefile); }
110 if ($restore eq 'smsproviders') {
111 my $timestamp = most_recent_single($MIGGITDIR,'smsproviders');
112 if ($timestamp) { $restorefile = $MIGGITDIR . 'smsproviders' . '.' . $timestamp . '.xml'; }
113 print "Restoring from $restorefile ... \n";
114 if ($restorefile) { restore_smsproviders($dbh,$restorefile); }
121 sub most_recent_single {
122 my $MGIGITDIR = shift;
126 opendir (DIR, $MIGGITDIR) or abort("could not open $MIGGITDIR");;
127 while (my $file = readdir(DIR)) {
128 if ($file =~ m/$str/) { push @files, $file; }
130 foreach my $file (@files) {
131 my @f = split /\./, $file;
135 my $max = max @times;
139 sub restore_authorisedvalues {
141 my $restore_file = shift;
142 my $parser = XML::LibXML->new();
143 my $dom = $parser->parse_file($restore_file);
145 my $query = "DELETE FROM authorised_values WHERE category != 'ITEMTYPECAT'";
146 my $sth = $dbh->prepare($query);
149 $query = "DELETE FROM authorised_value_categories WHERE category_name != 'ITEMTYPECAT'";
150 $sth = $dbh->prepare($query);
153 foreach my $node ($dom->findnodes('/document/categories/value')) {
154 my $name = sql_str_not_null($node->findvalue('./name'));
155 $query = "INSERT INTO authorised_value_categories (category_name) VALUES ($name)";
156 $sth = $dbh->prepare($query);
160 foreach my $node ($dom->findnodes('/document/authorisedvalues/value')) {
161 my $category = sql_str($node->findvalue('./category'));
162 my $authorised_value = sql_str($node->findvalue('./authorised_value'));
163 my $lib = sql_str($node->findvalue('./lib'));
164 my $lib_opac = sql_str($node->findvalue('./lib_opac'));
165 my $imageurl = sql_str($node->findvalue('./imageurl'));
166 $query = "INSERT INTO authorised_values (category,authorised_value,lib,lib_opac,imageurl) VALUES ($category,$authorised_value,$lib,$lib_opac,$imageurl)";
167 $sth = $dbh->prepare($query);
173 sub restore_booksellers {
175 my $restore_file = shift;
176 my $parser = XML::LibXML->new();
177 my $dom = $parser->parse_file($restore_file);
179 my $query = "DELETE FROM aqbooksellers WHERE 1 = 1";
180 my $sth = $dbh->prepare($query);
183 foreach my $node ($dom->findnodes('/booksellers/value')) {
184 my $name = sql_str($node->findvalue('./name'));
185 my $address1 = sql_str($node->findvalue('./address1'));
186 my $address2 = sql_str($node->findvalue('./address2'));
187 my $address3 = sql_str($node->findvalue('./address3'));
188 my $address4 = sql_str($node->findvalue('./address4'));
189 my $phone = sql_str($node->findvalue('./phone'));
190 my $accountnumber = sql_str($node->findvalue('./accountnumber'));
191 my $othersupplier = sql_str($node->findvalue('./othersupplier'));
192 my $currency = sql_str_not_null($node->findvalue('./currency'));
193 my $booksellerfax = sql_str($node->findvalue('./booksellerfax'));
194 my $notes = sql_str($node->findvalue('./notes'));
195 my $bookselleremail = sql_str($node->findvalue('./bookselleremail'));
196 my $booksellerurl = sql_str($node->findvalue('./booksellerurl'));
197 my $postal = sql_str($node->findvalue('./postal'));
198 my $url = sql_str($node->findvalue('./url'));
199 my $active = sql_str($node->findvalue('./active'));
200 my $listprice = sql_str($node->findvalue('./listprice'));
201 my $invoiceprice = sql_str($node->findvalue('./invoiceprice'));
202 my $gstreg = sql_num($node->findvalue('./gstreg'));
203 my $listincgst = sql_num($node->findvalue('./listincgst'));
204 my $invoiceincgst = sql_num($node->findvalue('./invoiceincgst'));
205 my $tax_rate = sql_num($node->findvalue('./tax_rate'));
206 my $discount = sql_num($node->findvalue('./discount'));
207 my $fax = sql_str($node->findvalue('./fax'));
208 my $deliverytime = sql_num($node->findvalue('./deliverytime'));
209 $query = "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)";
210 $sth = $dbh->prepare($query);
217 sub restore_borrowerattributes {
219 my $restore_file = shift;
220 my $parser = XML::LibXML->new();
221 my $dom = $parser->parse_file($restore_file);
223 my $query = "DELETE FROM authorised_values WHERE category IN (select category_code from borrower_attribute_types)";
224 my $sth = $dbh->prepare($query);
227 $query = "DELETE FROM authorised_value_categories WHERE category_name IN (select category_name from borrower_attribute_types)";
228 $sth = $dbh->prepare($query);
231 $query = "DELETE FROM borrower_attribute_types WHERE 1 = 1";
232 $sth = $dbh->prepare($query);
235 foreach my $node ($dom->findnodes('/borrower_attribute_types/value')) {
236 my $code = sql_str($node->findvalue('./code'));
237 my $description = sql_num($node->findvalue('./description'));
238 my $repeatable = sql_num($node->findvalue('./repeat'));
239 my $opac_display = sql_num($node->findvalue('./opac_display'));
240 my $staff_searchable = sql_num($node->findvalue('./staff_searchable'));
241 my $authorised_value_category = sql_str($node->findvalue('./auth_value_cat'));
242 my $display_checkout = sql_num($node->findvalue('./display_checkout'));
243 my $category_code = sql_str($node->findvalue('./category_code'));
244 my $class = sql_str($node->findvalue('./class'));
245 $query = "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)";
246 $sth = $dbh->prepare($query);
250 foreach my $node ($dom->findnodes('/authorised_value_categories/value')) {
251 my $category_name = sql_str($node->nodeValue);
252 $query = "INSERT INTO authorised_value_categories (category_name) VALUES ($category_name)";
253 $sth = $dbh->prepare($query);
257 foreach my $node ($dom->findnodes('/authorised_values/value')) {
258 my $category = sql_str($node->findvalue('./category'));
259 my $authvalue = sql_str($node->findvalue('./authvalue'));
260 my $lib = sql_str($node->findvalue('./lib'));
261 my $lib_opac = sql_str($node->findvalue('./lib_opac'));
262 my $image_url = sql_str($node->findvalue('./image_url'));
263 $query = "INSERT INTO authorised_values (category,authvalue,lib,lib_opac,image_url) VALUES ($category,$authvalue,$lib,$lib_opac,$image_url)";
264 $sth = $dbh->prepare($query);
271 sub restore_budgets {
273 my $restore_file = shift;
274 my $parser = XML::LibXML->new();
275 my $dom = $parser->parse_file($restore_file);
277 my $query = "DELETE FROM aqbudgets WHERE 1 = 1";
278 my $sth = $dbh->prepare($query);
281 $query = "DELETE FROM aqbudgetperiods WHERE 1 = 1";
282 $sth = $dbh->prepare($query);
288 foreach my $node ($dom->findnodes('/document/budgets')) {
289 $budget_ai = sql_num($node->findvalue('./autoincrement'));
292 foreach my $node ($dom->findnodes('/document/budgetperiods')) {
293 $budgetperiod_ai = sql_num($node->findvalue('./autoincrement'));
296 $query = "ALTER TABLE aqbudgets AUTO_INCREMENT = $budget_ai";
297 $sth = $dbh->prepare($query);
300 $query = "ALTER TABLE aqbudgetperiods AUTO_INCREMENT = $budgetperiod_ai";
301 $sth = $dbh->prepare($query);
304 foreach my $node ($dom->findnodes('/document/budgetperiods/value')) {
305 my $budget_period_id = sql_num($node->findvalue('./budget_period_id'));
306 my $budget_period_startdate = sql_str($node->findvalue('./budget_period_startdate'));
307 my $budget_period_enddate = sql_str($node->findvalue('./budget_period_enddate'));
308 my $budget_period_active = sql_str($node->findvalue('./budget_period_active'));
309 my $budget_period_description = sql_str($node->findvalue('./budget_period_description'));
310 my $budget_period_total = sql_num($node->findvalue('./budget_period_total'));
311 my $budget_period_locked = sql_num($node->findvalue('./budget_period_locked'));
312 my $sort1_authcat = sql_str($node->findvalue('./sort1_authcat'));
313 my $sort2_authcat = sql_str($node->findvalue('./sort2_authcat'));
314 $query = "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)";
316 $sth = $dbh->prepare($query);
320 #note that at this point we are not loading the budget owner, it's nullable and not 100% clear that it's the borrower
321 #and nothing to test with in initial sample
322 foreach my $node ($dom->findnodes('/document/budgets/value')) {
323 my $budget_id = sql_num($node->findvalue('./budget_id'));
324 my $budget_parent_id = sql_num($node->findvalue('./budget_parent_id'));
325 my $budget_code = sql_str($node->findvalue('./budget_code'));
326 my $budget_name = sql_str($node->findvalue('./budget_name'));
327 my $budget_branchcode = sql_str_not_null($node->findvalue('./budget_branchcode'));
328 my $budget_amount = sql_num($node->findvalue('./budget_amount'));
329 my $budget_encumb = sql_num($node->findvalue('./budget_encumb'));
330 my $budget_expend = sql_num($node->findvalue('./budget_expend'));
331 my $budget_notes = sql_str_not_null($node->findvalue('./budget_notes'));
332 my $timestamp = sql_str($node->findvalue('./timestamp'));
333 my $budget_period_id = sql_num($node->findvalue('./budget_period_id'));
334 my $sort1_authcat = sql_str_not_null($node->findvalue('./sort1_authcat'));
335 my $sort2_authcat = sql_str_not_null($node->findvalue('./sort2_authcat'));
336 my $budget_permission = sql_num($node->findvalue('./budget_permission'));
337 $query = "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)";
338 $sth = $dbh->prepare($query);
345 sub restore_calendar {
347 my $restore_file = shift;
348 my $parser = XML::LibXML->new();
349 my $dom = $parser->parse_file($restore_file);
351 my $query = "DELETE FROM repeatable_holidays WHERE 1 = 1";
352 my $sth = $dbh->prepare($query);
355 $query = "DELETE FROM special_holidays WHERE 1 = 1";
356 $sth = $dbh->prepare($query);
359 foreach my $node ($dom->findnodes('/holidays/repeatable')) {
360 my $branchcode = sql_str($node->findvalue('./branchcode'));
361 my $weekday = sql_num($node->findvalue('./weekday'));
362 my $day = sql_num($node->findvalue('./day'));
363 my $month = sql_num($node->findvalue('./month'));
364 my $title = sql_str($node->findvalue('./title'));
365 my $description = sql_str($node->findvalue('./description'));
366 $query = "INSERT INTO repeatable_holidays (branchcode,weekday,day,month,title,description) VALUES ($branchcode,$weekday,$day,$month,$title,$description)";
367 $sth = $dbh->prepare($query);
371 foreach my $node ($dom->findnodes('/holidays/special')) {
372 my $branchcode = sql_str($node->findvalue('./branchcode'));
373 my $day = sql_num($node->findvalue('./day'));
374 my $month = sql_num($node->findvalue('./month'));
375 my $year = sql_num($node->findvalue('./year'));
376 my $isexception = sql_num($node->findvalue('./isexception'));
377 my $title = sql_str($node->findvalue('./title'));
378 my $description = sql_str($node->findvalue('./description'));
379 $query = "INSERT INTO special_holidays (branchcode,day,month,year,isexception,title,description) VALUES ($branchcode,$day,$month,$year,$isexception,$title,$description)";
380 $sth = $dbh->prepare($query);
387 sub restore_circrules {
389 my $restore_file = shift;
390 my $parser = XML::LibXML->new();
391 my $dom = $parser->parse_file($restore_file);
393 #order of operations - clean out circ_rules, then issuingrules; restore issuingrules then circ_rules
395 my $query = "DELETE FROM circulation_rules WHERE 1 = 1";
396 my $sth = $dbh->prepare($query);
399 $query = "DELETE FROM issuingrules WHERE 1 = 1";
400 $sth = $dbh->prepare($query);
403 foreach my $node ($dom->findnodes('//issuing/rule')) {
404 my $categorycode = sql_str($node->findvalue('./categorycode'));
405 my $itemtype = sql_str($node->findvalue('./itemtype'));
406 my $restrictedtype = sql_num($node->findvalue('./restrictedtype'));
407 my $rentaldiscount = sql_num($node->findvalue('./rentaldiscount'));
408 my $reservecharge = sql_num($node->findvalue('./reservecharge'));
409 my $fine = sql_num($node->findvalue('./fine'));
410 my $finedays = sql_num($node->findvalue('./finedays'));
411 my $maxsuspensiondays = sql_num($node->findvalue('./maxsuspensiondays'));
412 my $suspension_chargeperiod = sql_num($node->findvalue('./suspension_chargeperiod'));
413 my $firstremind = sql_num($node->findvalue('./firstremind'));
414 my $chargeperiod = sql_num($node->findvalue('./chargeperiod'));
415 my $chargeperiod_charge_at = sql_num($node->findvalue('./chargeperiod_charge_at'));
416 my $accountsent = sql_num($node->findvalue('./accountsent'));
417 my $issuelength = sql_num($node->findvalue('./issuelength'));
418 my $lengthunit = sql_str($node->findvalue('./lengthunit'));
419 my $hardduedate = sql_str($node->findvalue('./hardduedate'));
420 my $hardduedatecompare = sql_num($node->findvalue('./hardduedatecompare'));
421 my $renewalsallowed = sql_num($node->findvalue('./renewalsallowed'));
422 my $renewalperiod = sql_num($node->findvalue('./renewalperiod'));
423 my $norenewalbefore = sql_num($node->findvalue('./norenewalbefore'));
424 my $auto_renew = sql_num($node->findvalue('./auto_renew'));
425 my $no_auto_renewal_after = sql_num($node->findvalue('./no_auto_renewal_after'));
426 my $no_auto_renewal_after_hard_limit = sql_str($node->findvalue('./no_auto_renewal_after_hard_limit'));
427 my $reservesallowed = sql_num($node->findvalue('./reservesallowed'));
428 my $holds_per_record = sql_num($node->findvalue('./holds_per_record'));
429 my $holds_per_day = sql_num($node->findvalue('./holds_per_day'));
430 my $branchcode = sql_str($node->findvalue('./branchcode'));
431 my $overduefinescap = sql_num($node->findvalue('./overduefinescap'));
432 my $cap_fine_to_replacement_price = sql_num($node->findvalue('./cap_fine_to_replacement_price'));
433 my $onshelfholds = sql_num($node->findvalue('./onshelfholds'));
434 my $opacitemholds = sql_str($node->findvalue('./opacitemholds'));
435 my $article_requests = sql_str($node->findvalue('./article_requests'));
436 my $note = sql_str($node->findvalue('./note'));
437 $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)";
438 $sth = $dbh->prepare($query);
442 foreach my $node ($dom->findnodes('//circ/rule')) {
443 my $branchcode = sql_str($node->findvalue('./branchcode'));
444 my $categorycode = sql_str($node->findvalue('./categorycode'));
445 my $itemtype = sql_str($node->findvalue('./itemtype'));
446 my $rule_name = sql_str($node->findvalue('./rule_name'));
447 my $rule_value = sql_str($node->findvalue('./rule_value'));
448 $query = "INSERT INTO circulation_rules (branchcode,categorycode,itemtype,rule_name,rule_value) VALUES ($branchcode,$categorycode,$itemtype,$rule_name,$rule_value)";
449 $sth = $dbh->prepare($query);
456 sub restore_itemtypes {
458 my $restorefile = shift;
459 my $parser = XML::LibXML->new();
460 my $dom = $parser->parse_file($restorefile);
462 my $query = "UPDATE itemtypes SET searchcategory = NULL";
463 my $sth = $dbh->prepare($query);
466 $query = "DELETE FROM authorised_values WHERE category = 'ITEMTYPECAT'";
467 $sth = $dbh->prepare($query);
470 $query = "DELETE FROM localization WHERE entity = 'itemtypes'";
471 $sth = $dbh->prepare($query);
474 $query = "DELETE FROM itemtypes WHERE 1 = 1";
475 $sth = $dbh->prepare($query);
478 foreach my $node ($dom->findnodes('/document/authorised_values/value')) {
479 my $category = sql_str($node->findvalue('./category'));
480 my $authvalue = sql_str($node->findvalue('./authvalue'));
481 my $lib = sql_str($node->findvalue('./lib'));
482 my $lib_opac = sql_str($node->findvalue('./lib_opac'));
483 my $image_url = sql_str($node->findvalue('./image_url'));
484 my $query = "INSERT INTO authorised_values (category,authvalue,lib,lib_opac,image_url) VALUES ($category,$authvalue,$lib,$lib_opac,$image_url)";
485 $sth = $dbh->prepare($query);
489 foreach my $node ($dom->findnodes('/document/itemtypes/value')) {
490 my $itemtype = sql_str($node->findvalue('./itemtype'));
491 my $description = sql_str($node->findvalue('./description'));
492 my $rentalcharge = sql_str($node->findvalue('./rentalcharge'));
493 my $rentalcharge_daily = sql_str($node->findvalue('./rentalcharge_daily'));
494 my $rentalcharge_hourly = sql_str($node->findvalue('./rentalcharge_hourly'));
495 my $defaultreplacecost = sql_str($node->findvalue('./defaultreplacecost'));
496 my $processfee = sql_str($node->findvalue('./processfee'));
497 my $notforloan = sql_str($node->findvalue('./notforloan'));
498 my $imageurl = sql_str($node->findvalue('./imageurl'));
499 my $summary = sql_str($node->findvalue('./summary'));
500 my $checkinmsg = sql_str($node->findvalue('./checkinmsg'));
501 my $checkinmsgtype = sql_str($node->findvalue('./checkinmsgtype'));
502 my $sip_media_type = sql_str($node->findvalue('./sip_media_type'));
503 my $hideinopac = sql_str($node->findvalue('./hideinopac'));
504 my $searchcategory = sql_str($node->findvalue('./searchcategory'));
505 $query = "SELECT itemtype FROM itemtypes WHERE itemtype = $itemtype";
506 $sth = $dbh->prepare($query);
509 while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); }
510 if (!defined $answer or $answer ne $itemtype) {
511 $query = "INSERT INTO itemtypes (itemtype,description,rentalcharge,rentalcharge_daily,rentalcharge_hourly,defaultreplacecost,processfee,notforloan,imageurl,summary,checkinmsg,sip_media_type,hideinopac,searchcategory)
512 VALUES ($itemtype,$description,$rentalcharge,$rentalcharge_daily,$rentalcharge_hourly,$defaultreplacecost,$processfee,$notforloan,$imageurl,$summary,$checkinmsg,$sip_media_type,$hideinopac,$searchcategory)";
513 $sth = $dbh->prepare($query);
516 $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
517 WHERE itemtype = $itemtype";
518 $sth = $dbh->prepare($query);
523 foreach my $node ($dom->findnodes('//localizations/value')) {
524 my $entity = sql_str($node->findvalue('./entity'));
525 my $code = sql_str($node->findvalue('./code'));
526 my $lang = sql_str($node->findvalue('./lang'));
527 my $translation = sql_str($node->findvalue('./translation'));
528 my $query = "INSERT INTO localization (entity,code,lang,translation) VALUES ($entity,$code,$lang,$translation)";
529 $sth = $dbh->prepare($query);
536 sub restore_letters {
538 my $restore_file = shift;
539 my $parser = XML::LibXML->new();
540 my $dom = $parser->parse_file($restore_file);
542 my $query = "DELETE FROM letter WHERE 1 = 1";
543 my $sth = $dbh->prepare($query);
546 foreach my $node ($dom->findnodes('/letters/letter')) {
547 my $module = sql_str($node->findvalue('./module'));
548 my $code = sql_str($node->findvalue('./code'));
549 my $branchcode = sql_str_not_null($node->findvalue('./branchcode'));
550 my $name = sql_str($node->findvalue('./name'));
551 my $is_html = sql_num($node->findvalue('./is_html'));
552 my $title = sql_str($node->findvalue('./title'));
553 my $content = sql_str($node->findvalue('./content'));
554 my $message_transport_type = sql_str($node->findvalue('./message_transport_type'));
555 my $lang = sql_str($node->findvalue('./lang'));
556 $query = "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)";
557 $sth = $dbh->prepare($query);
564 sub restore_libraries {
566 my $restorefile = shift;
567 my $parser = XML::LibXML->new();
568 my $dom = $parser->parse_file($restorefile);
570 my $query = "TRUNCATE library_groups";
571 my $sth = $dbh->prepare($query);
574 foreach my $node ($dom->findnodes('//library')) {
575 my $branchcode = sql_str($node->findvalue('./branchcode'));
576 my $branchname = sql_str($node->findvalue('./branchname'));
577 my $branchaddress1 = sql_str($node->findvalue('./branchaddress1'));
578 my $branchaddress2 = sql_str($node->findvalue('./branchaddress2'));
579 my $branchaddress3 = sql_str($node->findvalue('./branchaddress3'));
580 my $branchzip = sql_str($node->findvalue('./branchzip'));
581 my $branchcity = sql_str($node->findvalue('./branchcity'));
582 my $branchstate = sql_str($node->findvalue('./branchstate'));
583 my $branchcountry = sql_str($node->findvalue('./branchcountry'));
584 my $branchphone = sql_str($node->findvalue('./branchphone'));
585 my $branchfax = sql_str($node->findvalue('./branchfax'));
586 my $branchemail = sql_str($node->findvalue('./branchemail'));
587 my $branchreplyto = sql_str($node->findvalue('./branchreplyto'));
588 my $branchreturnpath = sql_str($node->findvalue('./branchreturnpath'));
589 my $branchurl = sql_str($node->findvalue('./branchurl'));
590 my $issuing = sql_str($node->findvalue('./issuing'));
591 my $branchip = sql_str($node->findvalue('./branchip'));
592 my $branchprinter = sql_str($node->findvalue('./branchprinter'));
593 my $branchnotes = sql_str($node->findvalue('./branchnotes'));
594 my $opac_info = sql_str($node->findvalue('./opac_info'));
595 my $geolocation = sql_str($node->findvalue('./geolocation'));
596 my $marcorgcode = sql_str($node->findvalue('./marcorgcode'));
597 my $pickup_location = $node->findvalue('./pickup_location');
598 $query = "SELECT branchcode FROM branches WHERE branchcode = $branchcode";
599 $sth = $dbh->prepare($query);
602 while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); }
603 if (!defined $answer or $answer ne $branchcode) {
604 $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)";
605 $sth = $dbh->prepare($query);
608 $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";
609 $sth = $dbh->prepare($query);
614 #haven't taken into account them needing to go in, in a specific order so I could check parent ids first
615 foreach my $node ($dom->findnodes('//library_group')) {
616 my $id = $node->findvalue('./id');
617 my $parent_id = sql_num($node->findvalue('./parent_id'));
618 my $branchcode = sql_str($node->findvalue('./branchcode'));
619 my $title = sql_str($node->findvalue('./title'));
620 my $descr = sql_str($node->findvalue('./description'));
621 my $ft1 = sql_num($node->findvalue('./ft_hide_patron_info'));
622 my $ft2 = sql_num($node->findvalue('./ft_search_groups_opac'));
623 my $ft3 = sql_num($node->findvalue('./ft_search_groups_staff'));
624 $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)";
625 $sth = $dbh->prepare($query);
631 sub restore_patrontypes {
633 my $restorefile = shift;
634 my $parser = XML::LibXML->new();
635 my $dom = $parser->parse_file($restorefile);
640 foreach my $node ($dom->findnodes('//category')) {
641 my $categorycode = sql_str($node->findvalue('./categorycode'));
642 my $description = sql_str($node->findvalue('./description'));
643 my $enrolmentperiod = sql_num($node->findvalue('./enrolmentperiod'));
644 my $enrolmentperioddate = sql_str($node->findvalue('./enrolmentperioddate'));
645 my $upperagelimit = sql_num($node->findvalue('./upperagelimit'));
646 my $dateofbirthrequired = sql_num($node->findvalue('./dateofbirthrequired'));
647 my $finetype = sql_str($node->findvalue('./finetype'));
648 my $bulk = sql_num($node->findvalue('./bulk'));
649 my $enrolmentfee = sql_num($node->findvalue('./enrolmentfee'));
650 my $overduenoticerequired = sql_num($node->findvalue('./overduenoticerequired'));
651 my $issuelimit = sql_num($node->findvalue('./issuelimit'));
652 my $reservefee = sql_num($node->findvalue('./reservefee'));
653 my $hidelostitems = sql_num($node->findvalue('./hidelostitems'));
654 my $category_type = sql_str($node->findvalue('./category_type'));
655 my $BlockExpiredPatronOpacActions = sql_num($node->findvalue('./BlockExpiredPatronOpacActions'));
656 my $default_privacy = sql_str($node->findvalue('./default_privacy'));
657 my $checkprevcheckout = sql_str($node->findvalue('./checkprevcheckout'));
658 my $reset_password = sql_num($node->findvalue('./reset_password'));
659 my $change_password = sql_num($node->findvalue('./change_password'));
660 $query = "SELECT categorycode FROM categories WHERE categorycode = $categorycode";
661 $sth = $dbh->prepare($query);
664 while (my @row = $sth->fetchrow_array) { $answer = sql_str($row[0]); }
665 if (!defined $answer or $answer ne $categorycode) {
666 $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)";
667 $sth = $dbh->prepare($query);
670 $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";
671 $sth = $dbh->prepare($query);
678 sub restore_preferences {
680 my $restore_file = shift;
681 my $parser = XML::LibXML->new();
682 my $dom = $parser->parse_file($restore_file);
684 foreach my $node ($dom->findnodes('//pref')) {
685 my $variable = domain_shift(sql_str($node->findvalue('./variable')));
686 my $value = domain_shift(sql_str($node->findvalue('./value')));
687 my $query = "UPDATE systempreferences SET value = $value WHERE variable = $variable";
688 my $sth = $dbh->prepare($query);
694 sub restore_reports {
696 my $restore_file = shift;
697 my $parser = XML::LibXML->new();
698 my $dom = $parser->parse_file($restore_file);
700 my $query = "DELETE FROM saved_sql WHERE 1 = 1";
701 my $sth = $dbh->prepare($query);
704 foreach my $node ($dom->findnodes('//sqlreport')) {
705 my $date_created = sql_str($node->findvalue('./date_created'));
706 my $last_modified = sql_str($node->findvalue('./last_modified'));
707 my $savedsql = domain_shift(sql_str($node->findvalue('./savedsql')));
708 my $report_name = sql_str($node->findvalue('./report_name'));
709 my $type = sql_str($node->findvalue('./type'));
710 my $notes = sql_str($node->findvalue('./notes'));
711 my $cache_expiry = sql_num($node->findvalue('./cache_expiry'));
712 my $public = sql_str($node->findvalue('./public'));
713 my $report_area = sql_str($node->findvalue('./report_area'));
714 my $report_group = sql_str($node->findvalue('./report_group'));
715 my $report_subgroup = sql_str($node->findvalue('./report_subgroup'));
716 my $userid = sql_str($node->findvalue('./userid'));
718 $query = "SELECT borrowernumber FROM borrowers WHERE userid = $userid";
719 $sth = $dbh->prepare($query);
721 my @row = $sth->fetchrow_array;
722 my $borrowernumber = sql_num($row[0]);
724 $query = "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)";
725 $sth = $dbh->prepare($query);
731 sub restore_smsproviders {
733 my $restore_file = shift;
734 my $parser = XML::LibXML->new();
735 my $dom = $parser->parse_file($restore_file);
737 my $query = "DELETE FROM sms_providers WHERE 1 = 1";
738 my $sth = $dbh->prepare($query);
741 foreach my $node ($dom->findnodes('//provider')) {
742 my $name = sql_str($node->findvalue('./name'));
743 my $domain = sql_str($node->findvalue('./domain'));
744 $query = "INSERT INTO sms_providers (name,domain) VALUES ($name,$domain)";
745 $sth = $dbh->prepare($query);
753 $str =~ s/-test.kohacatalog.com/.kohacatalog.com/g;
754 $str =~ s/-test-staff.kohacatalog.com/-staff.kohacatalog.com/g;
760 if (!defined $str or $str eq '') { return 'NULL'; }
762 $str = '\'' . $str . '\'';
766 sub sql_str_not_null {
768 $str = sql_str($str);
769 if (!defined $str or $str eq 'NULL') { return "''"; }
775 if (!defined $str or $str eq '') { return 'NULL'; } else { return $str; }
780 print STDERR "$0: $msg", "\n";
788 mig import foo_a foo_b foo_c