From: Rogan Hamby Date: Tue, 2 Jun 2020 20:15:34 +0000 (-0400) Subject: added to kmig bookseller and budget support to import and export X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=97d32ec9b31b31619965efe339103f1c551dbd20 added to kmig bookseller and budget support to import and export --- diff --git a/kmig.d/bin/mig-export b/kmig.d/bin/mig-export index 2599240..314830c 100755 --- a/kmig.d/bin/mig-export +++ b/kmig.d/bin/mig-export @@ -27,7 +27,7 @@ $dbh->{mysql_enable_utf8mb4} = 1; my @taglist = @ARGV; my $arg_list_length = scalar @taglist; -if($arg_list_length < 1) { @taglist = ("authorisedvalues","borrowerattributes","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } +if($arg_list_length < 1) { @taglist = ("authorisedvalues","booksellers","budgets","borrowerattributes","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } $MIGGITDIR =~ s/\/\//\//; my $timestamp = create_timestamp(); @@ -39,11 +39,21 @@ foreach my $backup (@taglist) { print "Backing up $backupfile ... \n"; backup_authorisedvalues($dbh,$backupfile); } + if ($backup eq 'booksellers') { + $backupfile = $MIGGITDIR . 'booksellers' . '.' . $timestamp . '.xml'; + print "Backing up $backupfile ... \n"; + backup_booksellers($dbh,$backupfile); + } if ($backup eq 'borrowerattributes') { $backupfile = $MIGGITDIR . 'borrowerattributes' . '.' . $timestamp . '.xml'; print "Backing up $backupfile ... \n"; backup_borrower_attributes($dbh,$backupfile); } + if ($backup eq 'budgets') { + $backupfile = $MIGGITDIR . 'budgets' . '.' . $timestamp . '.xml'; + print "Backing up $backupfile ... \n"; + backup_budgets($dbh,$backupfile); + } if ($backup eq 'calendar') { $backupfile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; print "Backing up $backupfile ... \n"; @@ -112,6 +122,52 @@ sub backup_authorisedvalues { return; } +sub backup_booksellers { + 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('booksellers'); + my $query = "SELECT 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 FROM aqbooksellers"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('value'); + $xml->dataElement('name', $row[1]); + $xml->dataElement('address1', $row[2]); + $xml->dataElement('address2', $row[3]); + $xml->dataElement('address3', $row[4]); + $xml->dataElement('address4', $row[5]); + $xml->dataElement('phone', $row[6]); + $xml->dataElement('accountnumber', $row[7]); + $xml->dataElement('othersupplier', $row[8]); + $xml->dataElement('currency', $row[9]); + $xml->dataElement('booksellerfax', $row[10]); + $xml->dataElement('notes', $row[11]); + $xml->dataElement('bookselleremail', $row[12]); + $xml->dataElement('booksellerurl', $row[13]); + $xml->dataElement('postal', $row[14]); + $xml->dataElement('url', $row[15]); + $xml->dataElement('active', $row[16]); + $xml->dataElement('listprice', $row[17]); + $xml->dataElement('invoiceprice ', $row[18]); + $xml->dataElement('gstreg', $row[19]); + $xml->dataElement('listincgst', $row[20]); + $xml->dataElement('invoiceincgst', $row[21]); + $xml->dataElement('tax_rate', $row[22]); + $xml->dataElement('discount', $row[23]); + $xml->dataElement('fax ', $row[24]); + $xml->dataElement('deliverytime', $row[25]); + $xml->endTag('value'); + } + $xml->endTag('booksellers'); + $xml->end(); + close $fh; + return; +} + sub backup_borrower_attributes { my $dbh = shift; my $backupfile = shift; @@ -170,6 +226,75 @@ sub backup_borrower_attributes { return; } +sub backup_budgets { + 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('budgets'); + my $query = 'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = "aqbudgets"'; + my $sth = $dbh->prepare($query); + $sth->execute(); + my @ai = $sth->fetchrow_array; + $xml->dataElement('autoincrement', $ai[0]); + $query = "SELECT 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_owner_id,budget_permission FROM aqbudgets"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('value'); + $xml->dataElement('budget_id', $row[0]); + $xml->dataElement('budget_parent_id', $row[1]); + $xml->dataElement('budget_code', $row[2]); + $xml->dataElement('budget_name', $row[3]); + $xml->dataElement('budget_branchcode', $row[4]); + $xml->dataElement('budget_amount', $row[5]); + $xml->dataElement('budget_encumb', $row[6]); + $xml->dataElement('budget_expend', $row[7]); + $xml->dataElement('budget_notes', $row[8]); + $xml->dataElement('timestamp', $row[9]); + $xml->dataElement('budget_period_id', $row[10]); + $xml->dataElement('sort1_authcat', $row[11]); + $xml->dataElement('sort2_authcat', $row[12]); + $xml->dataElement('budget_owner_id', $row[13]); + $xml->dataElement('budget_permission', $row[14]); + $xml->endTag('value'); + } + $xml->endTag('budgets'); + + $xml->startTag('budgetperiods'); + $query = 'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = "aqbudgetperiods"'; + $sth = $dbh->prepare($query); + $sth->execute(); + @ai = $sth->fetchrow_array; + $xml->dataElement('autoincrement', $ai[0]); + $query = "SELECT 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 FROM aqbudgetperiods"; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('value'); + $xml->dataElement('budget_period_id', $row[0]); + $xml->dataElement('budget_period_startdate', $row[1]); + $xml->dataElement('budget_period_enddate', $row[2]); + $xml->dataElement('budget_period_active', $row[3]); + $xml->dataElement('budget_period_description', $row[4]); + $xml->dataElement('budget_period_total', $row[5]); + $xml->dataElement('budget_period_locked', $row[6]); + $xml->dataElement('sort1_authcat', $row[7]); + $xml->dataElement('sort2_authcat', $row[8]); + $xml->endTag('value'); + } + $xml->endTag('budgetperiods'); + + $xml->endTag('document'); + $xml->end(); + close $fh; + return; +} + sub backup_calendar { my $dbh = shift; my $backupfile = shift; diff --git a/kmig.d/bin/mig-import b/kmig.d/bin/mig-import index 095acb0..ccc30d6 100755 --- a/kmig.d/bin/mig-import +++ b/kmig.d/bin/mig-import @@ -29,7 +29,7 @@ $dbh->do('SET NAMES utf8mb4'); my @taglist = @ARGV; my $arg_list_length = scalar @taglist; -if($arg_list_length < 1) { @taglist = ("authorisedvalues","borrowerattributes","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } +if($arg_list_length < 1) { @taglist = ("authorisedvalues","booksellers","borrowerattributes","budgets","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } $MIGGITDIR =~ s/\/\//\//; foreach my $restore (@taglist) { @@ -40,12 +40,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'; } @@ -81,7 +93,7 @@ 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"; + print "IMPORTANT : if you are changing system preferences you may need to run 'sudo systemctl restart memcached'\n\n"; } if ($restore eq 'reports') { my $timestamp = most_recent_single($MIGGITDIR,'reports'); @@ -141,6 +153,49 @@ sub restore_authorisedvalues { return; } +sub restore_booksellers { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM aqbooksellers WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + 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')); + $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)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + return; +} sub restore_borrowerattributes { my $dbh = shift; @@ -196,6 +251,79 @@ sub restore_borrowerattributes { return; } +sub restore_budgets { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM aqbudgets WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "DELETE FROM aqbudgetperiods WHERE 1 = 1"; + $sth = $dbh->prepare($query); + $sth->execute(); + + 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')); + } + + $query = "ALTER TABLE aqbudgets AUTO_INCREMENT = $budget_ai"; + $sth = $dbh->prepare($query); + $sth->execute(); + + $query = "ALTER TABLE aqbudgetperiods AUTO_INCREMENT = $budgetperiod_ai"; + $sth = $dbh->prepare($query); + $sth->execute(); + + 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')); + $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)"; + print "$query\n"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + #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')); + $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)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + + return; +} sub restore_calendar { my $dbh = shift; @@ -318,6 +446,7 @@ sub restore_itemtypes { my $sth = $dbh->prepare($query); $sth->execute(); + $query = "DELETE FROM authorised_values WHERE category = 'ITEMTYPECAT'"; $sth = $dbh->prepare($query); $sth->execute(); @@ -378,7 +507,6 @@ sub restore_itemtypes { 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(); } @@ -587,6 +715,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; }