# Output Google Books Library XML Files to CSV & SQL
# For creating labels for new library books & SQL INSERT statements
set-executionpolicy unrestricted
 
$googleXML =  Read-Host "Enter the path to the Google books xml file ( D:\AR_Middle_years.xml )"#"D:\AR_Middle_years.xml" "Enter the path to the Google books xml file ( D:\AR_Middle_years.xml )"
$configDir = Read-Host "Enter the path to the configuration file ( D:\config.xml )"
$outputDir =  "D:\" #"Enter root directory for output ( D:\ )"
 
#Load XML file exported from Google Books
[xml]$books = Get-Content $googleXML -ErrorAction Stop
#Load configuration file
[xml]$config = Get-Content $configDir -ErrorAction Stop
 
 
 #Get Unique ID set as integer
 [int]$UID = $config.Configuration.UID
 #Set input file name without extension or path
 $fileNameWE = [io.path]::GetFileNameWithoutExtension($googleXML)
 
 #Create a Powershell Object from imported XML
 #Add the auto incrementing UID value from the configuration file
 $bookObj = $books.library.books.book | ForEach-Object {
      New-Object PSCustomObject -Property @{
        "ISBN" = $_.identifier.value
        "Title" = $_.title
        "Author" = $_.contributor
        "Barcode" = 'e' + $UID++
      }
 }
     
 
#
#concatenate SQL INSERT Statement
#
 
     $biblioQuery = 'INSERT IGNORE INTO biblio ' #SQL Command, table name, ' ' blank space at the end.
     $biblio_copyQuery = 'INSERT IGNORE INTO biblio_copy '
     $biblio_fieldQuery = 'INSERT IGNORE INTO biblio_field '
     $sqlArray = @() #Initialize an empty array
 
#
#Set database column names
#
 
#biblio table
$biblioID = 'bibid';
$b_COL2 = 'create_dt'; $b_COL3 = 'last_change_dt'; $b_COL4 = 'last_change_userid'; $b_COL5 = 'material_cd'; $b_COL6 = 'collection_cd'; $b_COL7 = 'call_nmbr1'; $b_COL10 = 'title'; $b_COL13 = 'author'; $b_COL19 = 'opac_flg';    
$b_VAL2 = 'now()';     $b_VAL3 = 'now()';          $b_VAL4 = '1';                  $b_VAL5 = '2';           $b_VAL6 = '1';             $b_VAL7 = '';           $b_VAL10 = '';      $b_VAL13 = '';       $b_VAL19 = 'Y';
 
 
#biblio_copy table
$b_c_COL2 = 'copyid'; $b_c_COL3 = 'create_dt'; $b_c_COL4 = 'copy_desc'; $b_c_COL5 = 'barcode_nmbr'; $b_c_COL6 = 'status_cd'; $b_c_COL7 = 'status_begin_dt'; $b_c_COL10 = 'renewal_count';
$b_c_VAL2 = '';      $b_c_VAL3 = 'now()';     $b_c_VAL4 = '';          $b_c_VAL5 = '';             $b_c_VAL6 = 'in';        $b_c_VAL7 = 'now()';           $b_c_VAL10 = '0';
 
#biblio_field table
$b_f_COL2 = 'fieldid'; $b_f_COL3 = 'tag'; $b_f_COL4 = 'ind1_cd'; $b_f_COL5 = 'ind2_cd'; $b_f_COL6 = 'subfield_cd'; $b_f_COL7 = 'field_data'
$b_f_VAL2 = '1';       $b_f_VAL3 = '20';  $b_f_VAL4 = 'N';       $b_f_VAL5 = 'N';       $b_f_VAL6 = 'a';            
     
     
 #Populate $sqlArray
 $bookObj | ForEach-Object{
     $VAL1 =  $_.Barcode
     $VAL3 =  $_.Title.replace("'","") 
     $VAL4 =  $_.Author.replace("'","")
     $VAL2 =  $_.ISBN
     $sqlArray += Write-Output "/* $VAL3 by $VAL4 (Barcode: $VAL1, ISBN: $VAL2) */"
     $sqlArray += Write-Output $biblioQuery"("$b_COL2","$b_COL3","$b_COL4","$b_COL5","$b_COL6","$b_COL7","$b_COL10","$b_COL13","$b_COL19") VALUES ("$b_VAL2","$b_VAL3",'"$b_VAL4"','"$b_VAL5"','"$b_VAL6"','"$VAL2"','"$VAL3"','"$VAL4"','"$b_VAL19"');"
     $sqlArray += Write-Output $biblio_copyQuery"("$biblioID","$b_c_COL2","$b_c_COL3","$b_c_COL5","$b_c_COL6","$b_c_COL7","$b_c_COL10") SELECT biblio.bibid,'"$b_c_VAL2"',biblio.create_dt,'"$VAL1"','"$b_c_VAL6"',biblio.create_dt,'"$b_c_VAL10"' FROM biblio WHERE biblio.title = '"$VAL3"' AND biblio.author = '"$VAL4"';"
     $sqlArray += Write-Output $biblio_fieldQuery"("$biblioID","$b_f_COL2","$b_f_COL3","$b_f_COL4","$b_f_COL5","$b_f_COL6","$b_f_COL7") SELECT biblio.bibid,'"$b_f_VAL2"','"$b_f_VAL3"','"$b_f_VAL4"','"$b_f_VAL5"','"$b_f_VAL6"', biblio.call_nmbr1 FROM biblio WHERE biblio.title = '"$VAL3"' AND biblio.author = '"$VAL4"';"
     $sqlArray += Write-Output ""
 }
 
 
 #
 #Create Output Files
 #
 
 
if (!(Test-Path -path $outputDir"LibraryLabels")) {
 
New-Item $outputDir"LibraryLabels\SQL" -type directory
New-Item $outputDir"LibraryLabels\CSV" -type directory
Write-Host "New Directories Created:"
Write-Host $outputDir"LibraryLabels\SQL"
Write-Host $outputDir"LibraryLabels\CSV"
 
}
 
 
 #Set the format to export the SQL Statments
 $sqlArray | out-file  -force -Encoding "UTF8" -filepath $outputDir"LibraryLabels\SQL\"$fileNameWE"_sqlOutput_"$((Get-Date).ToString("yyyy-MM-dd")).sql
 
#Use select to order columns and export to csv
 $bookObj | Select Title, Author, ISBN, Barcode | Export-Csv -NoTypeInformation -Path $outputDir"LibraryLabels\CSV\"$fileNameWE"_labelOutput_"$((Get-Date).ToString("yyyy-MM-dd")).csv
 
 
 #
 #Update Configuration File
 #
 
 $config.Configuration | ForEach-Object { $_.UID = $UID.ToString() } #convert from int to string
 $config.Save($configDir) #save xml
 
 
#
#Provide 15 seconds of feedback before script end
#
 
#If the path to the files the script creates exist show a success message, otherwise throw error
if (!(Test-Path -path $outputDir"LibraryLabels\SQL\"$fileNameWE"_sqlOutput_"$((Get-Date).ToString("yyyy-MM-dd")).sql)-and (Test-Path -path $outputDir"LibraryLabels\CSV\"$fileNameWE"_labelOutput_"$((Get-Date).ToString("yyyy-MM-dd")).csv)) {
 
Write-Host "Warning! Files could not be created."
 
}
ELSE {
 
Write-Host "Success! Files Created:"
Write-Host $outputDir"LibraryLabels\SQL\"$fileNameWE"_sqlOutput_"$((Get-Date).ToString("yyyy-MM-dd")).txt
Write-Host $outputDir"LibraryLabels\CSV\"$fileNameWE"_labelOutput_"$((Get-Date).ToString("yyyy-MM-dd")).csv
}
 
#Confirm that UID has been correctly incremented and is written to the configuration file
Write-Host UID in configuration file updated to: $config.Configuration.UID
 
#After messages are written to the console, sleep for 15 seconds then -END
Start-Sleep -s 15
 
#
#Example Configuration File
#
 
#<?xml version="1.0" encoding="utf-8"?>
#<Configuration>
#    <UID>8231</UID>
#</Configuration>