From 6320c3392e8cdf8b1365f7d3bd68342171fb59f9 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Thu, 21 May 2020 16:58:28 -0400 Subject: [PATCH] kmig import/export now support syncing reports --- kmig.d/bin/mig-export | 58 +++++++++++++++++++++++++++++++++++++++++------- kmig.d/bin/mig-import | 45 +++++++++++++++++++++++++++++++++++++- 2 files changed, 93 insertions(+), 10 deletions(-) diff --git a/kmig.d/bin/mig-export b/kmig.d/bin/mig-export index 0e6d27b..40decda 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","calendar","circrules","itemtypes","libraries","patrontypes","preferences","smsproviders"); } #borrowerattributes +if($arg_list_length < 1) { @taglist = ("authorisedvalues","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } #borrowerattributes $MIGGITDIR =~ s/\/\//\//; my $timestamp = create_timestamp(); @@ -41,42 +41,47 @@ foreach my $backup (@taglist) { } if ($backup eq 'borrowerattributes') { $backupfile = $MIGGITDIR . 'borrower_attributes' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_borrower_attributes($dbh,$backupfile); } if ($backup eq 'calendar') { $backupfile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_calendar($dbh,$backupfile); } if ($backup eq 'circrules') { $backupfile = $MIGGITDIR . 'circrules' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_circrules($dbh,$backupfile); } if ($backup eq 'itemtypes') { $backupfile = $MIGGITDIR . 'itemtypes' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_itemtypes($dbh,$backupfile); } if ($backup eq 'libraries') { $backupfile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_libraries($dbh,$backupfile); } if ($backup eq 'patrontypes') { $backupfile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_patrontypes($dbh,$backupfile); } if ($backup eq 'preferences') { $backupfile = $MIGGITDIR . 'systempreferences' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_preferences($dbh,$backupfile); } + if ($backup eq 'reports') { + $backupfile = $MIGGITDIR . 'reports' . '.' . $timestamp . '.xml'; + print "Backing up $backupfile ... \n"; + backup_reports($dbh,$backupfile); + } if ($backup eq 'smsproviders') { $backupfile = $MIGGITDIR . 'smsproviders' . '.' . $timestamp . '.xml'; - print "Restoring from $restorefile ... \n"; + print "Backing up $backupfile ... \n"; backup_smsproviders($dbh,$backupfile); } } @@ -477,6 +482,41 @@ sub backup_preferences { return; } +sub backup_reports { + 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('reports'); + + my $query = "SELECT s.date_created, s.last_modified, s.savedsql, s.report_name, s.type, s.notes, s.cache_expiry, s.public, s.report_area, s.report_group, s.report_subgroup, b.userid FROM saved_sql s LEFT JOIN borrowers b ON b.borrowernumber = s.borrowernumber"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + $xml->startTag('sqlreport'); + $xml->dataElement('date_created',$row[0]); + $xml->dataElement('last_modified',$row[1]); + $xml->dataElement('savedsql',$row[2]); + $xml->dataElement('report_name',$row[3]); + $xml->dataElement('type',$row[4]); + $xml->dataElement('notes',$row[5]); + $xml->dataElement('cache_expiry',$row[6]); + $xml->dataElement('public',$row[7]); + $xml->dataElement('report_area',$row[8]); + $xml->dataElement('report_group',$row[9]); + $xml->dataElement('report_subgroup',$row[10]); + $xml->dataElement('userid',$row[11]); + $xml->endTag('sqlreport'); + } + + $xml->endTag('reports'); + $xml->end(); + close $fh; + return; +} + sub backup_smsproviders { my $dbh = shift; my $backupfile = shift; diff --git a/kmig.d/bin/mig-import b/kmig.d/bin/mig-import index 33302a7..4235459 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","calendar","circrules","itemtypes","libraries","patrontypes","preferences","smsproviders"); } +if($arg_list_length < 1) { @taglist = ("authorisedvalues","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } $MIGGITDIR =~ s/\/\//\//; foreach my $restore (@taglist) { @@ -77,6 +77,12 @@ foreach my $restore (@taglist) { 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'; } @@ -447,6 +453,43 @@ sub restore_preferences { return; } +sub restore_reports { + my $dbh = shift; + my $restore_file = shift; + my $parser = XML::LibXML->new(); + my $dom = $parser->parse_file($restore_file); + + my $query = "DELETE FROM saved_sql WHERE 1 = 1"; + my $sth = $dbh->prepare($query); + $sth->execute(); + + 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')); + + $query = "SELECT borrowernumber FROM borrowers WHERE userid = $userid"; + $sth = $dbh->prepare($query); + $sth->execute(); + my @row = $sth->fetchrow_array; + my $borrowernumber = sql_num($row[0]); + + $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)"; + $sth = $dbh->prepare($query); + $sth->execute(); + } + return; +} + sub restore_smsproviders { my $dbh = shift; my $restore_file = shift; -- 1.7.2.5